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.

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.

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.

Next we need to setup the objects for Event Notification

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.

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.

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.

please source sample for me ?? i need it and tks you

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.

Could you elaborate on the issues you had please, i tried the sample and it does not execute the program, its probably my 5th attempt at runinng samples and the Main not being called. I dont have any errors in event log.

In my case, its probably because nothing is being writtent to MyNotificationQueue.

I noticed that c has a typo there, maybe there is a typo in sql as well?

Sourabh, have you managed to get this to work when using more than one database? The example is relating to multiple queues. So SSBEAS is installed on a server and there are two identical queues on two different databases which will invoke two different applications. I cant find any examples of how this in implemented in the config. Only the first NotificationService entry set to true in the nfig can be used. The second is ignored.

Fill in your details below or click an icon to log in:

You are commenting using your account.(LogOutChange)

You are commenting using your Google account.(LogOutChange)

You are commenting using your Twitter account.(LogOutChange)

You are commenting using your Facebook account.(LogOutChange)

Enter your email address to follow this blog and receive notifications of new posts by email.

SQL Community SessionIndexing Basics and Writing OptimalQueries

Introduction to Stretch Databases PartThree

Post was not sent – check your email addresses!

Sorry, your blog cannot share posts by email.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

To find out more, including how to control cookies, see here:Cookie Policy%dbloggers like this:>