Getting SQLDependency to fire the OnChange

After many problems with permissions I have got got SQL to accept a notification request but the public static void OnChange(object sender, SqlNotificationEventArgs e) is never triggered. The notification registers and de-registers ok. The same connect string successfuly connects to the same database to process queries.

I can see the GUID suffixed stored procedure, queue and service being created. Where does SQL2005 store the address/name of the routine it is to trigger (When the notification is cancelled, the guid-siffixed items disappear) I have looked at the generated stored procedure, queue and service, but there is no indication of what is to be called back.

I have followed the instructions at http://msdn2.microsoft.com/en-us/library/ms181122.aspx, but so far without avail. I have checked the Application and system event logs, but there is indication therein. Also the SQL log.

So my questions are:
1) Where is the callback stored (is it a pointer or an actual name)
2) What steps should I take to resolve this

Answer this question

Getting SQLDependency to fire the OnChange

an5w3r

The problem turned out to be that after setting up the notfication as in MSDN example at
http://msdn2.microsoft.com/en-us/library/62xk7953.aspx, I had omitted the equivalent line to

 command.ExecuteReader(); // Execute the command.

Once I added this line, the notifications started working! 
It is bizzare that the command needs to be executed to 
get the notify to fire.

Many thanks for the various contributions - they are much appreciated.

Vjy
Upon further examination, within the SQL log there is a
"The Service Broker protocol transport is disabled or not configured."

This is in spite of enabling CLR integration and

USE dbname
EXEC sp_configure 'show advanced options', '1'
go
RECONFIGURE
go
EXEC sp_configure 'clr enabled', 1
go
RECONFIGURE
GO
USE MASTER
ALTER DATABASE dbname SET ENABLE_BROKER
GO

Does the Service Broker protocol transport need to be enabled and if so, how
Chris Richner

The message reffers to the broker endpoint (CREATE/ALTER/DROP ENDPOINT ... FOR SERVICE_BROKER) that is required only for cases involving sending messages between SQL instances. If you just use SqlDependency you do not need this.

Enabling CLR has nothing to do with this, this is for running CLR procedures in the server.

HTH,
~ Remus
Eric_Martin

SQL does not store the routine being triggered. This is a client feature. On the server side all that is done is a Query Notification is registered and a message is delivered to the subscribed service when the query is notified. Once the message is delivered, everything else happens on the client (SqlDependency). The callback is stored like most callbacks in CLR, as a delegate.

To investigate this, you have to confirm that:
1) the notification is fired: see http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx and http://msdn2.microsoft.com/en-us/library/ms177469.aspx
2) the message carying the notification is delivered: see http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx
3) the message is being RECEIVED by your app (the SqlDependency). Monitor with Profiler for batch completion.

HTH,
~ Remus
Getting SQLDependency to fire the OnChange

Answer this question