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]
GO
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]
GO
CREATE SERVICE [SqlQueryNotificationService] AUTHORIZATION [dbo] ON QUEUE [dbo].[SqlQueryNotificationService_DefaultQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])

USE [msdb]
GO
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 http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService
rather than SqlQueryNotificationService

So to grant the send rights in SQLServer use

GRANT SEND ON SERVICE::[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService] to GUEST

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, "http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService",SqlNotificationAuthType.None,SqlNotificationTransports.Any,10000);


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

b.