I was browsing through MSDN magazine (the paper version) today and noticed an ad for the upcoming SQLConnections conference in Orlando. I'm doing three sessions there as well as a day-long pre-conference talk "All about SQL Server 2008 Spatial Data and writing Location-aware Applications". I'm REALLY looking forward to this one and have some interesting and novel demos and information. Almost everyone is really using spatial apps, but store the info in its "alternate" form; that is address-city-state-postal code-country form.

However, the ad mentioned my pre-conference workshop was "A Day of SQL Server Security". This turned out to be a misprint (it is correct on the conference website), I am doing a 1.5 hour talk on "SQL Server 2008 Security for Developers and Architects", but the full-day pre-con is on Spatial and Spatial apps. Really...

Just didn't want anyone showing up expecting a different topic. Although I could do a day (or more) on SQL Server security, it will be the condensed version that "plays at" SQLConnections. You've been warned...

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.