A fix for error Cannot find the remote service SqlQueryNotificationService-GUID

Sometimes your ERRORLOG is peppered with messages complaining about the service SqlQueryNotificationService-<guid> not existing or query notification dialogs being closed because they received an error message with the text Remote service has been dropped. I have blogged about this problem before: http://rusanu.com/2007/11/10/when-it-rains-it-pours/. Unfortunately this problem was not under your control as an administrator nor as a developer. It is caused by the way the SqlDependency component of ADO.Net deploys the temporary service, queue and procedure needed for its functioning. The problem could be caused by your application calling SqlDependency.Stop inadvertently but also by simple timing problems: http://rusanu.com/2008/01/04/sqldependencyonchange-callback-timing/.

Good news: Microsoft has shipped a fix for this issue: http://support.microsoft.com/kb/958006. According to the knowledge base article you need to install the following Cumulative Update depending on your current version of SQL Server deployed:

• For SQL Server 2005 SP2 you need CU 10.
• For SQL Server 2005 SP3 you need CU 1.
• For SQL Server 2008 you need CU 2.

If you have SQL Server 2008 SP1 deployed you do not need to install any fix because the issue is fixed in SP1 for 2008.