This blog is going to have surfing, snowboarding, tech stuff and maybe some 'meaning of life' stuff if I'm feeling particularly stoked.

Monday, April 25, 2005

SQLDependency in the April CTP

****** UPDATED 04/26/2005 *********

The problem here was that the SqlQueryNotificationService did not appear to get created when
I upgraded an installation from SQL2000. I fixed the problem by creating the service and then all the advice in the posts mentioned works fine. Here's how to create the service if you have the same problem:

USE [msdb]
CREATE QUEUE [dbo].[SqlQueryNotificationService_DefaultQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [sys].[sp_DispatcherProc] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' ) ON [PRIMARY]

USE [msdb]
CREATE SERVICE [SqlQueryNotificationService] AUTHORIZATION [dbo] ON QUEUE [dbo].[SqlQueryNotificationService_DefaultQueue] ([])

USE [msdb]
GRANT SEND on service::SqlQueryNotificationService to guest

****** END OF UPDATE ***************

As an update to some very useful posts on getting SQL Notification to work in Beta 1 from Nils, Julie and Bob Beauchemin, as well as a great original article from Bob, here's what you need to do in SQL2005 April CTP with Beta 2 VS.

Use service name of
rather than SqlQueryNotificationService

So to grant the send rights in SQLServer use


To create an instance of the SQLDependency object you will also have to use the overloaded method which explicitly takes a service name (I presume the default is SqlQueryNotificationService), so you will have

SqlDependency depend = new SqlDependency(cmd, "",SqlNotificationAuthType.None,SqlNotificationTransports.Any,10000);

By the way, this will make no sense unless you have read the above article/posts first.