Query Notifications/ Service Broker problem

Hey , I encountered a problem using SqlDependency and notification services.

I’ve created a database with tools from c# Express 2005 and attached it to the sql server express 2005. And it turned out that ‘dependency_OnChange’ in my application is triggered each time I execute the command.ExecuteReader(); and it shouldn’t, because the command is just the select statement of columns and rows for which I wait to be modified.

SqlDependency dependency = new SqlDependency(command);

dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

command.ExecuteReader();//when the program gets here dependency_OnChange is called

So I Used the example from here :

http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx

and with AdventureWorks it behaves as it should , but when I change the connection string and select statement in the example to work with MyDatabase, the problem appears again.

So there is something wrong with MyDatabase ?

The select statement I used was “select Name, Pass from Users where Name like ‘L%’”;

So theoretically this should work but it does not and I end up in an infinite loop with

dependency_OnChange called cyclically …

I’d be grateful for any advice !

What are the dependency_OnChange paramater values passed in when the callback is notified?

I suspect the SqlNotificationEventArgs argument has a value of 'Subscribe' for the notification type, indicating an error in setting up the notification. If the statement you included is the exact statement you'd tried, then the problem is that your query is not respecting the Query Notifications restrictions. The query select Name, Pass from Users where Name like ‘L%’ is not properly schema bound, the Users table name should be bound with a schema name (e.g. dbo.Users).

HTH,
~ Remus