Getting Notifications to Work.
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.
- My Application doesn’t get Notification messages at all:
There could be many reasons why we could end up with this. Here are some reasons.
-
- Is CLR enabled on SQL Server?
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'
go
RECONFIGURE
go
EXEC sp_configure 'clr enabled', 1
go
RECONFIGURE
-
- Does the SQL user have permissions on the Queue and/or the Service
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
-
- Is it an XPSP2 machine and/or you using a firewall to block ports?
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.
- I get an instantaneous notification telling that the Subscription failed.
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 https://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
Comments
Anonymous
January 31, 2005
Bookmarked !! :)Anonymous
March 27, 2008
PingBack from http://employmentwagesblog.info/sushils-weblog-getting-notifications-to-work/Anonymous
June 15, 2009
PingBack from http://einternetmarketingtools.info/story.php?id=23657