With good things said about Notification in my previous blog, I will like to take a step further to walk you through some common issues in setting up this feature. We would need to undestand how notifications internally work to track some of these issues. Lets start with the SqlDependency object. When we create a SqlDependency object we are creating a listener on the client to track notifications sent by the server. There are two types of protocol options that SqlDependency supports – HTTP and TCP when creating a listener. The HTTP listener uses the HTTP.SYS functionality to create a listener. By default when no protocol option is specified, we try to create an HttpListener; if that for some reason fails, we create a TCPListener. When a change occurs, the server then dispatches notification messages via a .Net Procedure to the client listener. Now, let’s go over some common issues.
There could be many reasons why we could end up with this. Here are some reasons.
Cause: Since the code on the SQL server that dispatches notification messaged to the client is a .Net Procedure, you will have to enable CLR on Server. We are working on not requiring this restriction.:)
Solution: Here is way to do this for now,
EXEC sp_configure 'show advanced options', '1'goRECONFIGUREgoEXEC sp_configure 'clr enabled', 1goRECONFIGURE
Cause: Since this feature uses the SQL Server Service Broker (aka SSB) infrastructure to get notification, the user has to have the permissions on the Notification Service.
Solution: To grant the permission to user ‘Willy’ on the Service and queue use:
GRANT SEND on service::SqlQueryNotificationService to Willy
GRAND RECEIVE on SqlQueryNotificationService_DefaultQueue to Willy
Also the user needs permission to subscribe to notification. To do this use:
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO Willy
Cause: As said above, Notifications are dispatched from the server to the client via listener that sits on the client. With XPSP2 we have firewall enabled by default. Since the firewall will block any message sent to your TCP/HTTP ports, this may not generate Notification event.
Solution: Make sure these are open for the application.
Cause: There are set of requirements that are placed on the queries for which are notifiable . Basically, the restrictions are quite similar to restrictions for Indexed Views in SQL Server 2000. More information on these requirements can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_9jnb.asp
Solution: Queries that pass the above requirements can only be used.
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights