SQLUNINTERRUPTED

Use master

go

create database SSBEA

go

-- Enable Trustworthy on the database

Alter database SSBEA SET TRUSTWORTHY ON

go

Use SSBEA

go

/***************************** SET UP Application Service and Queue **********************************************/

--Create Message type for Communication

CREATE MESSAGE TYPE [MyMessage]

    VALIDATION = NONE 

-- Create a Contract for SSB communication

CREATE Contract  [MyContract]    

        ([MyMessage] Sent by ANY) 

-- Create a Queue for the Notification

Create Queue MySSBEAQueue

With Status = ON,Retention = off, Poison_Message_Handling (Status = off) 

go

-- Create a Service for the Notification

Create Service MySSBEAService 

    On Queue MySSBEAQueue

go

ALTER SERVICE MySSBEAService

    (ADD CONTRACT [MyContract]) 

/****************** Create a Sample Table using which we would send message to be queue *****************/

-- 

Create table test_SSB(a int, b int)

GO

-- Create a Trigger on this table which would invoke the SSB Service and post messages to the Queue.

alter trigger SSB_trigger 

on test_SSB

AFTER INSERT

as

declare @h as uniqueIdentifier

declare @message Varchar(1000)

declare @test1 int, @test2 int

select @test1=a, @test2=b from INSERTED with(nolock)

Set @message = ''

set @message = @message + 'The value Inserted in the table were '+ Cast(@test1 as varchar(10)) + ' and '+ Cast(@test2 as varchar(10));

BEGIN DIALOG CONVERSATION @h

FROM SERVICE MySSBEAService TO SERVICE 'MySSBEAService', 'CURRENT DATABASE'

ON CONTRACT [MyContract]

with encryption = off;

SEND ON CONVERSATION @h MESSAGE TYPE [MyMessage] (@message)

print @h

IF EXISTS(select * from sys.conversation_endpoints 

                        where conversation_handle=@h and state='ER')

    begin

        RAISERROR ('Service Broker in error state',18,127)

        rollback transaction

    end

else

    begin

        END CONVERSATION @h WITH CLEANUP;

        print 'Clean Up Completed'

    end 

GO

--- Create the Queues for Notification

CREATE QUEUE MyNotificationQueue 

GO

-- create event notification service

CREATE SERVICE MyNotificationService

      ON QUEUE MyNotificationQueue

      (

            [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] 

      )

GO

-- Create a Event Notification to allow the EA to listen for Notification

CREATE EVENT NOTIFICATION MyNotificationEvent

ON QUEUE MySSBEAQueue

FOR QUEUE_ACTIVATION

TO SERVICE 'MyNotificationService' , 'current database'

GO

using System;

using System.Collections.Generic;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Diagnostics;

using System.IO;

namespace MyMessageApplication

{

    class Program

    {

        /// <summary>

        /// Application name used when connecting to Sql Server and writing Windows Event Log entries.

        /// </summary>

        private const string ApplicationName = "MyMessageApplication";

        /// <summary>

        /// Defines how many milliseconds the <c>RECEIVE</c> statement should wait for messages.

        /// </summary>

        private const int WaitforTimeout = 5000;

        /// <summary>

        /// Predefined Sql Server message type name for end dialog messages.

        /// </summary>

        private const string EndDialogMessageType = "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog";

        /// <summary>

        /// Predefined Sql Server message type name for conversation error messages.

        /// </summary>

        private const string ErrorMessageType = "http://schemas.microsoft.com/SQL/ServiceBroker/Error";

        /// <summary>

        /// Services request messages by doing all the necessary computation and preparing the payload of a reply message.

        /// </summary>

        /// 

        ///Create a message logger function

        ///

        public static void LogMessageToFile(string message)

        {

                StreamWriter sw = File.AppendText(@"D:\SQLDataFiles\MyMessageApplicationLog.txt");

                string entry = String.Format("{0:G}: {1}.", System.DateTime.Now, message);

                sw.WriteLine(entry);

                sw.Close();

        }

        static void Main()

        {   

            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();

            csb.ApplicationName = ApplicationName;

            csb.DataSource = "<ServerName>";

            csb.InitialCatalog = "SSBEA";

            csb.UserID = "UserAccount";

            csb.Password = "Password for the User Account above";

            csb.MultipleActiveResultSets = true;

            try

            {

                SqlConnection conn = new SqlConnection(csb.ToString());

                conn.Open();

                bool MsgRcv;

                do

                {

                    MsgRcv = false;

                    SqlTransaction tran = conn.BeginTransaction();

                    using (tran)

                    {

                        SqlCommand comm = conn.CreateCommand();

                        comm.Transaction = tran;

                        //LogMessageToFile("Querying from the Queue");

                        comm.CommandText = string.Format(

                            "WAITFOR (RECEIVE TOP (1) conversation_handle, message_type_name, convert(xml,message_body) FROM {0}), TIMEOUT {1}", 

                            "MySSBEAQueue", WaitforTimeout);

                        SqlDataReader reader = comm.ExecuteReader();

                        while(reader.Read())

                        {

                            MsgRcv = true;

                            System.Data.SqlTypes.SqlGuid handle = reader.GetSqlGuid(0);

                            System.Data.SqlTypes.SqlString TypeName = reader.GetSqlString(1);

                            System.Data.SqlTypes.SqlXml message = reader.GetSqlXml(2);

                            if(TypeName == EndDialogMessageType || TypeName == ErrorMessageType)

                            {

                                if (TypeName == ErrorMessageType)

                                {

                                    LogMessageToFile(message.ToString());

                                }

                                SqlCommand endconv = conn.CreateCommand();

                                endconv.Transaction = tran;

                                endconv.CommandText = "End Conversation @handle";

                                endconv.Parameters.Add("@handle", System.Data.SqlDbType.UniqueIdentifier);

                                endconv.Parameters["@handle"].Value = handle;

                                endconv.ExecuteNonQuery();  

                            }

                            else

                            {

                                SqlCommand com = conn.CreateCommand();

                                //LogMessageToFile("Inserting into the table");

                                com.CommandText = "Insert into SSBEA.dbo.ReceievedMassage(RecievedMessage) values (convert(varchar(1000),@payload))";

                                com.Parameters.Add("@payload", System.Data.SqlDbType.Xml);

                                com.Parameters["@payload"].Value = message;

                                com.Transaction = tran;

                                com.ExecuteNonQuery();

                                //LogMessageToFile("Inserted this message to the table:- " + message.Value);

                            } 

                        }

                        tran.Commit();

                    }

                } while (MsgRcv);

            }

            catch (Exception e)

            {

                LogMessageToFile(e.Message.ToString());

            }    

        }

    }

}