Query Notification problem

• SQLDev1

  SSChasing Mays

  Points: 643

  More actions

  July 21, 2009 at 1:54 pm

  #97846

  I've been trying to get query notification working for a little while now and have become frustrated with the lack of documentation or I guess the fact that all the stars have to be aligned in order for this thing to work.

  Steps.

  EXEC sp_addrole 'sql_dependency_subscriber'

  GRANT CREATE PROCEDURE to [sql_dependency_subscriber];

  GRANT CREATE QUEUE to [sql_dependency_subscriber];

  GRANT CREATE SERVICE to [sql_dependency_subscriber];

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber];

  GRANT VIEW DEFINITION TO [sql_dependency_subscriber];

  GRANT SELECT TO [sql_dependency_subscriber];

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

  GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber];

  GRANT CONTROL ON SCHEMA::[dbo] TO sql_dependency_subscriber;

  GRANT SEND ON SERVICE::[CentersService] TO sql_dependency_subscriber;

  EXEC sp_addrolemember 'sql_dependency_subscriber', 'Login';

  I believe that this is all the permissions that I need as far as I have read. Although I am not a fan of giving control to dbo but for dev purposes it's OK for now.

  I have set up a trace to see what's going on and below are the abbreviated steps that it took.

  - select is_broker_enabled from sys.databases where database_id=db_id()

  - CREATE PROCEDURE [SqlQueryNotificationStoredProcedure-2729e156-5b03-4ee4-a4d0-1cd50a41b26a] AS BEGIN BEGIN TRANSACTION; RECEIVE TOP(0) conversation_handle FROM [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]; IF (SELECT COUNT(*) FROM [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a] WHERE message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer') > 0 BEGIN DROP SERVICE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]; DROP QUEUE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]; DROP PROCEDURE [SqlQueryNotificationStoredProcedure-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]; END COMMIT TRANSACTION; END

  - declare @p3 uniqueidentifier

  set @p3=NULL

  exec sp_executesql N'IF OBJECT_ID(''SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a'') IS NULL BEGIN CREATE QUEUE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a] WITH ACTIVATION (PROCEDURE_NAME=[SqlQueryNotificationStoredProcedure-2729e156-5b03-4ee4-a4d0-1cd50a41b26a], MAX_QUEUE_READERS=1, EXECUTE AS OWNER); END; IF (SELECT COUNT(*) FROM sys.services WHERE NAME=''SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a'') = 0 BEGIN CREATE SERVICE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a] ON QUEUE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); IF (SELECT COUNT(*) FROM sys.database_principals WHERE name=''sql_dependency_subscriber'' AND type=''R'') <> 0 BEGIN GRANT SEND ON SERVICE::[SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a] TO sql_dependency_subscriber; END; END; BEGIN DIALOG @dialog_handle FROM SERVICE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a] TO SERVICE ''SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a''',N'@dialog_handle uniqueidentifier output',@dialog_handle=@p3 output

  select @p3

  - exec sp_executesql N'BEGIN CONVERSATION TIMER (''1bf1dcdb-2a76-de11-8bdf-000e7fefe4ce'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]), TIMEOUT @p2;',N'@p2 int',@p2=0

  - exec sp_executesql N'BEGIN CONVERSATION TIMER (''1bf1dcdb-2a76-de11-8bdf-000e7fefe4ce'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]), TIMEOUT @p2;',N'@p2 int',@p2=60000

  At this point the app calls the SP

  Then this...

  exec sp_executesql N'BEGIN TRANSACTION; DROP SERVICE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]; DROP QUEUE [SqlQueryNotificationService-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]; DROP PROCEDURE [SqlQueryNotificationStoredProcedure-2729e156-5b03-4ee4-a4d0-1cd50a41b26a]; COMMIT TRANSACTION;',N'@p2 int',@p2=60000

  I do not see any Query Notification events in the trace for creating a subscrioption or anything else besides cleanup stuff.

  All it seems to do is create the queue, service, SP and then drop them...

  Can someone help me understand what I am doing wrong?

  Much appreciated