Problem with SQL Dependency

Hi -

I am trying to set up a SQL dependency using VS Studio 2005 and SQL Server 2005, Dev Ed. My problem is that the handler for the onchange event never gets called, and the SQL Service Broker statistics report shows it as disabled.

This is really vexing because this is what I have done:

The service broker was enabled using this:

use masteralterdatabase mydatabaseSET ENABLE_BROKER

which I can verify by using:select is_broker_enabled from sys.databases where name = ''mydatabase', which results 1.

I've also given the user the right permissions, I believe, like this:

use mydatabase

GRANT CREATE PROCEDURE TO myuser
GRANT CREATE QUEUE TO myuser
GRANT CREATE SERVICE TO myuser

This isn't doing me any good, it seems, although I took care to log in as myuser. The connection works, and I can read/write data from the db. However the following code does not seem to do anything:

Public Sub StartListening()If IsNothing(Dependency)Then Dim cAs New SqlClient.SqlCommand("SELECT Messages.ID, Messages.TimeInitiated, Messages.TimeToSend, Messages.TimeSent FROM(Messages)") Dependency =New SqlClient.SqlDependency(c)AddHandler Dependency.OnChange,AddressOf UpdateMessages SqlClient.SqlDependency.Start(LoadConnectionString)End If Messages = MessageTA.GetDataEnd Sub Public Sub UpdateMessages(ByVal senderAs Object,ByVal eAs System.Data.SqlClient.SqlNotificationEventArgs)Dim i = 1End Sub

Or at least my breakpoint in UpdateMessages is never reached.

ANY HELP OR IDEAS AS TO WHAT I AM MISSING IS HIGHLY APPRECIATED!!!

Thanks,

Oliver

Have you enabled both your database and tables for change notifiations?

Have a look at this article. It should help you.

http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html