SqlDependency notification - notification of an immediate failure after query execution

Question

I had a problem when I try to configure SqlDependency notifications to receive notifications when data in a table changes to SQL. However, as soon as I execute the query that I use to configure sql dependencies, I immediately received a notification that the attempt to subscribe failed due to a problem with the sql operator ( SqlNotificationEventArgs shows Info: Invalid, Source: Statement, Type: Subscribe )

This indicates that I have a problem with the sql query, but having tried a very simple example to make sure that this is not a problem with the select statement, I still get these "invalid" notifications immediately. I also made sure that I started the SQL Server service broker, created the queue and notification service and provided all the necessary permissions for the principal (in this case, I connect to the sql server) Here is my table:

 CREATE TABLE [dbo].[TableTest]( [id] [int] NOT NULL, [val1] [int] NULL, [val2] [int] NULL, CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED ( [id] ASC ) ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO 

here is the code:

  SqlDependency.Start(connectStr); _connection = new SqlConnection(connectStr); _connection.Open(); _sqlCommand = new SqlCommand("Select [id] from TableTest", _connection); _sqlCommand.Notification = null; SqlDependency dependency = new SqlDependency(_sqlCommand); dependency.OnChange += this.OnDataChangeNotification; DataTable dt = new DataTable(); dt.Load(_sqlCommand.ExecuteReader()); 

After calling `_sqlCommand.ExecuteReader () ', the OnDataChangeNotification handler is immediately called with the SqlNotificationEventArgs parameter displaying Info: Invalid, Source: Statement, Type: Subscribe.

Does anyone know what might be the problem or how to determine / debug what it is (without using an SQL profiler that does not have atm).

Answer 1

You must use the two part names (dbo.TableName) for your tables in the SQL select statement in order to use the SqlDependency notification:

 SqlDependency.Start(connectStr); _connection = new SqlConnection(connectStr); _connection.Open(); _sqlCommand = new SqlCommand("Select [id] from dbo.TableTest", _connection); _sqlCommand.Notification = null; SqlDependency dependency = new SqlDependency(_sqlCommand); dependency.OnChange += this.OnDataChangeNotification; 

Here is a link to request notification requirements: MSDN request notifications.

Hope this helps.