Getting Notifications to Work.

  • Article

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.

  1. 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.

    1. 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

    1. 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

    1. 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.

  1. 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