In my What’s new in ADO.NET 2.0 talk, I have one slide and three quick demos on Query Notifications. For DevConnections, I am doing a whole talk on the topic. I was a little worried about filling up an entire session on the topic, but I have learned so much about Query Notifications, caching and SQL Server Service Broker, that I could go on and on about it.

For anyone who has started working with this fantastic new result of collaboration between ADO.NET and SQL Server developers at Microsoft, there are some post-Beta2 changes you should be aware of.

Most importantly, it will, indeed, be possible to use query notifications (easily) when running as a non-admin. Phew!

Also, the plumbing has changed. Through Beta2, notifications come back through either TCPIP or HTTP. This was creating the permission issues above. Now It will be necessary when using SqlDependency or SQLCacheDependency, to do an application wide SqlDependency.Start, which will open up a SQL Connection for the notifications to come through.

Currently there are some settings that need to be tweaked which we haven’t figured out yet so I wouldn’t bother trying to use this post Beta2 anyway. After many many (many) hours of experimenting, I am determined that there is some setting in SQL Server that we are missing. (And I don’t mean enabling service broker on the database). The experimenting pays off with a deeper understanding of how things work.

As soon as I find out the magic combo of settings, I will be sure as ___ 😉 to post them here!

Update: My old friend CompatibilityLevel had come back to bite me in the rear end – and hard. I was using the pubs database. Steve Smith was having the same problem and using the Northwind database. I was using a new install and therefore not inheriting my pubs db where I had changed the setting for pubs to 90. I thought the issue was gone, but it wasn’t.

So, though this deserves it’s own blog post – remember you can check and set the compatibility level. You need 90 for working with Notification Query. I wonder if Service Broker has the same requirement.

will tell you what the level is.

sp_dbcmptlevel yourdatabasename,90

will set it to 90.

Thanks to Sushil Chordia and Leonid Tsybert at Microsoft for their help with this!!

Don’t Forget: www.acehaid.org

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!