Watch the SqlNotificationInfo for query notifications

I've been back working with SQL Server Query Notifications again lately. This blog post stems from a problem where the query notification "appears to register correctly" but does not fire. Or registers but "fires immediately". In this case "register correctly" meant fires a QN:Subscription event to SQL Profiler.

The firing of a QN:Subscription event to SQL Profiler means that you did attempt to register a query notification. The query notification can either register correctly or return an additional field in the QN:Subscription event, an XML attribute named "info". This can be the reason why the Query Notification was NOT registered.

If your OnChange handler gets hit, you may either be getting a valid notification (some row has changed) or a notification that something has gone wrong, either with the registration or after the registration. Be sure and look at the SqlNotificationInfo for each request to see what happened.

The most common reason for a query notification to fail registration is "Query", that is the query is not supported by Query Notifications. But the one that I ran into recently is "Isolation". Query Notifications aren't supported if the statement that performs the SELECT is running under isolation level Read Uncommitted or Snapshot. Another interesting one that can happen at registration time is "Options", the SET options aren't set properly (yes, Query Notifications require certain SET options).

After the notification is registered, you can get not only Insert/Update/Delete notifications but also "Alter, a table/index/view definition was altered. And, if the SQL Server is recycled while you have a pending Query Notification, you'll get a "restart" notification reason when it starts back up. Bear in mind that you won't get that restart notification if Mirroring-Based failover happens (see http://support.microsoft.com/kb/930048).

Most times you just want to invalidate your cache and re-register the query notification, but note the reason (SqlNotificationInfo class) carefully; reregistering a query that fails for "Options" or "Isolation" just causes a register-fire-reregister spin behavior. Or, if you only act on Insert/Update/Delete, the perception that the notification registers correctly but never fires.