SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Monthly Archives: September 2012

Sending SQL Notification to C# Application using Service Broker

On one of my earlier post on CDC, I was asked if we can send out CDC notifications to a C# application. The ensuing post is an attempt to answer that question. This post does not specifically talk about sending CDC notification, but a general guidance on how Service Broker can be leveraged to send out notifications to an External Application.

In order to send out the notification using Service Broker to an external application, we would need to make use of the SQL Server Service Broker External activator, which is distributed as part of the SQL Server Feature Pack (available for 2005/2008/2008R2/2012). The links for the respective SQL Versions (latest versions).

From a work flow perspective, the following events take place.

image

The Application event or service communicates with the Notification Service (Event Notification). The External Activator services is continuously listening on the notification queue. When it encounters a message in the Queue, it invokes the External Application, which can process the required operation.

Once the External Activator is installed, it works as a NT Service.

image

Before the External Activator can be configured, the Event Notification mechanism needs to be created in SQL. This event notification would be fired on the Queue Activation Event on the Application Queue. In this example, I have implement an Async Trigger, which uses Service Broker to activate an external application.

Setting up the Async Trigger Code

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

Next we need to setup the objects for Event Notification

--- 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

Once we have setup the Event notification, we need an create the External Application as per our requirement. In my case the External Application, takes the message from the Service and inserts it into a secondary table.

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());

            }    

        }

    }

}

Once the application has been created, the last step would be to configure the EA.

In order to configure the EA to listen to listen for notification it needs to be configured accordingly using the configuration file which is created as part of the installation.

SNAGHTML5ebfc47

The above snapshot of the Configuration File shows the important parameters which needs to be specified for the EA to function properly.

  • Notification Service Name: Name of the Service which would be used for Notification.
  • Connection String: Information about which server/database on which the Service and the queues are created.
  • Application Name: Name of the application which would be invoked as part of External activation.
  • On Notification: The four-part name of the queue on which to listen for notification.
  • Launch Info: The executable path for the application which would be launched as part of the notification. The Concurrency section indication how many instance of the applications to be invoked in case multiple messages arrive in the queue.

In my case, there were some issues with launching the application from the External Activator which were resolved after setting some policies and UAC controls.

Hopefully this helps!!