Skip to content
01/04/2009 / dimarzionist

How to make SQL Server Notifications work

 

1. Enabling CLR

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘clr enabled’, 1;

GO

RECONFIGURE;

GO

2. Grant necessary permissions to SQL Server user

In order to use the SqlDependency infrastructure, the sql user must be able to create a procedure, a service and a queue, must be granted REFERENCES permission on the QN contract and must have ‘subscribe query notifications’ permission. In adition, for reasons I’m not sure I comprehend, it must have have suficient permissions over the [dbo] schema to be able to create a queue and procedure in it and be able impersonate the queue owner (that is, [dbo]).

GRANT CREATE PROCEDURE TO [SqlUser];

GRANT CREATE SERVICE TO [SqlUser];

GRANT CREATE QUEUE TO [SqlUser];

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SqlUser];

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlUser];

GRANT CONTROL

ON SCHEMA::[dbo] TO [SqlUser];

GRANT IMPERSONATE ON USER::DBO TO [SqlUser];

3. SQL Server account

Service Account for SQL Server

An application will not receive notifications from an instance of SQL Server that uses the Local System account as the service account. For more information, see "Setting Up Windows Service Accounts" in SQL Server Books Online.

Also check that your login account has all necessary permissions under specific database.

4. SQL Queries

Query notifications are supported for SELECT statements that meet the following requirements:

  • The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
  • The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
  • The statement may not use unnamed columns or duplicate column names.
  • The statement must reference a base table.
  • The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
  • A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
  • The statement must not include PIVOT or UNPIVOT operators.
  • The statement must not include the INTERSECT or EXCEPT operators.
  • The statement must not reference a view.
  • The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
  • The statement must not reference server global variables (@@variable_name).
  • The statement must not reference derived tables, temporary tables, or table variables.
  • The statement must not reference tables or views from other databases or servers.
  • The statement must not contain subqueries, outer joins, or self-joins.
  • The statement must not reference the large object types: text, ntext, and image.
  • The statement must not use the CONTAINS or FREETEXT full-text predicates.
  • The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
  • The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
  • The statement must not use any nondeterministic functions, including ranking and windowing functions.
  • The statement must not contain user-defined aggregates.
  • The statement must not reference system tables or views, including catalog views and dynamic management views.
  • The statement must not include FOR BROWSE information.
  • The statement must not reference a queue.
  • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

5. Alter authorization on database

Try to change db owner first to sa.

ALTER AUTHORIZATION ON DATABASE::[CDR] TO [SA];

6. Setup new instance of Service Broker (renew, enable)

DON’T FORGET TO SWITCH DATABASE TO SINGLE_USER MODE BEFORE RUNNING THIS COMMAND!

1) ALTER DATABASE xDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

2) ALTER DATABASE xDB SET NEW_BROKER

3) ALTER DATABASE xDB SET ENABLE_BROKER;

Then I checked that it had worked:

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘MyDB’;

7. When using SqlDependency

· Ensure you call SqlDependency.Start(connectionString) before subscribing to any notifications

· Ensure you are not calling SqlDependency.Stop(connectionString) too early, i.e. when some notifications still could be raised

LINKS:

http://msdn.microsoft.com/en-us/library/t9x04ed2(VS.80).aspx

http://msdn.microsoft.com/en-us/library/ms131048.aspx

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/505778e4-be04-4673-892c-917b91872aef/

http://social.msdn.microsoft.com/forums/en-US/sqlservicebroker/thread/bd195da8-93b2-43c6-8f59-674f5fb9d618/

http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx?msg=2487937#xx2487937xx

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/f5419d1c-e63e-4012-ac7d-857b13fed6b3/

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: