SYSK 236: Using SQL Server Notification Services API Remotely

  • Article

Did you notice that all samples (at least all I’ve come across) show how to use Notification Services interface running locally, i.e. on the SQL Server computer? The code snippet below shows getting a list of subscribers:

 

Microsoft.SqlServer.NotificationServices.NSInstance ns =

    new Microsoft.SqlServer.NotificationServices.NSInstance(Properties.Settings.Default.InstanceName);

Microsoft.SqlServer.NotificationServices.SubscriberEnumeration subscriberEnum =

   new Microsoft.SqlServer.NotificationServices.SubscriberEnumeration(ns);

List<Microsoft.SqlServer.NotificationServices.Subscriber> subscribers =

    new List<Microsoft.SqlServer.NotificationServices.Subscriber>();

foreach (Microsoft.SqlServer.NotificationServices.Subscriber s in subscriberEnum)

{

    subscribers.Add(s);

}

// display subscribers

this.dataGridView1.DataSource = subscribers;

 

So, is it possible to run this type of code on a different computer, i.e. remotely from Notification Services? In the case of my current project, we needed to provide end users with ability to manage their own notification subscriptions from the intranet…

 

The answer is ‘yes’. As I see it, you have three choices:

 

1. Create a .reg file with the following data:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Services\NotificationServices\Instances\NameOfYourNSInstance]
"DBServer"="your_sql_server_name"
"Version"="9.0.242"

 

** replace the bold text above with your values.

 

Then, double click on this file to add the data into the local registry (e.g. on the web box, or your laptop for testing), and that’s all there is to it…

 

The drawback of this approach is that the notification instance name in your registry must match exactly on in SQL Server Notification Services; so you'd have to alter the instance name on the server for each environment (e.g. XYZInstance-DEV, XYZInstance-QA, XYZInstance-PROD) for the side-by-side support.

 

2. Create a CLR stored proc that encapsulates all the NS logic; i.e. the CLR procs would use the Microsoft.SqlServer.NotificationServices classes and expose the required data/methods to the thin UI layer.

 

3. (my least favorite) Invoke stored procedures used by the Notification Services using ADO.NET. For example, to get subscribers you’d call NSGetSubscribers and pass in null as a parameter.