Hello all.
We are a small taskforce currently working on a system of which utilizes the SQL notification service. For this we are using dependencies provided by SqlDependency. The dependency is set up as follows:
//Where dbo.MTable is the table and guid is the unique key of the records. We //want to listen to individual records and not the entire table
SqlCommand listenCommand = new SqlCommand( "SELECT ID FROM dbo.MTable WHERE ID = " + "@.guid", connection );
listenCommand.Parameters.AddWithValue( "@.guid", guid );
depend = new SqlDependency( listenCommand, null, 0 );
depend.OnChange += onChangeEventHandler;
If you’re not so bold as to look in the database server log, and you do not care about log growth of cataclysmic proportions which will inevitably crash the server, it will work fine. The problem is, as hinted above, that the server log is filled with error messages.
Error Code: 8490
Description: “Cannot find the remote service : because it does not exist.”
Here is an example of before mentioned messages:
The query notification dialog on conversation handle '{F53C37DE-E66A-DA11-90A1-00105AC00126}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-cc3933a7-f97f-45b4-822f-f326f9f6e373' because it does not exist.</Description></Error>'.
We have realized that listening on an entire table does not provoke this error. Furthermore, experiments have shown that the error only appears after we have shut down our system.
On shutdown, we call the following method:
SqlDependency.Stop( connection.ConnectionString );
This seems to stop the database from sending out notifications when we listen to an entire table, but not when we listen to individual records. Are we using the service in an incorrect manner or is there an error in the Stop method?
We have discovered a hotfix, but it is not exactly a beautiful solution:
SqlDependency.Start( connection.ConnectionString );
SqlCommand listenCommand = new SqlCommand( "SELECT * FROM MTable", connection );
depend = new SqlDependency( listenCommand );
SqlDependency.Stop( m_ConnectionString );
We have found that by ‘restarting’ the SqlDependency and giving it a new dependency where it listens to the entire table and THEN stopping it, the error does not appear. Our theory is that the new start and dependency overwrites the old ones.
