Query Notification - Setup Problems

Hi Guys,

I'm having some problems setting up a SQL Server 2005 Query Notification application. I've only recently moved from SQL Server 2000 so 2005 is still a little alien to me!

I've followed example found on the internet in the following steps:

1.

ALTER DATABASE <dbname> SET ENABLE_BROKER

2.

GRANT CREATE PROCEDURE TO <user>

GRANT CREATE QUEUE TO <user>

GRANT CREATE SERVICE TO <user>

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <user>

GRANT RECEIVE ON QueryNotificationErrorsQueue TO <user>

3.

ALTER DATABASE <dbname> SET TRUSTWORTHY ON

The application developers have also followed the same example setting up SQL cache and the connection code. When they start the application I can see the connection in the activity monitor but it has a status of SUSPENDED. I've also checked the view:

SELECT * FROM sys.dm_qn_subscriptions

There are no subscriptions setup I can however see that there is a new SP created. I tried to creating master keys as well but I'm not sure what the reasoning behind this is.

Is there anything else I need to take into consideration or check? When I change the data which they use for their select statement SQL profiler doesn't register anything (except the update). I have however seen this:

exec sp_executesql N'END CONVERSATION @.p1; BEGIN CONVERSATION TIMER (''69e2c786-9d33-dc11-a751-0050568146e8'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-5fb25d04-6ef6-47e6-9406-854f15e16eb7]), TIMEOUT @.p2;',N'@.p2 int,@.p1
uniqueidentifier',@.p2=60000,@.p1='BD98F6B4-A133-DC11-A751-0050568146E8'

This occurs when they refresh the page.

Any ideas?

Cheers!

Ian

You should place the SqlDependency.Start in the appdomain load event. From your description it seems to be placed on the page load.