SqlDependency notification - immediate failure notification after executing query

Question

I am having a problem where i am trying to setup SqlDependency notifications to receive notifications when data in a table on sql sever changes. However, as soon as i execute the query that i use to set up the sql depenency for, a notification is immediately received indicating that the attempt to subscribe failed due to an issue with the sql statement (SqlNotificationEventArgs shows Info: Invalid, Source: Statement, Type: Subscribe)

This indicates that there i a problem with the sql query, but having tried a very basic example to make sure it is a not an issue with the select statement, i am still recieving these 'invalid' notifications immediately. I've also made sure that i've started SQL Server's service broker, created a queue and notification service, and granted all the necessary permissions to the principal (in this case the user i'm connecting to the sql server with) 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 '_sqlCommand.ExecuteReader()' is called, immediately the OnDataChangeNotification handler is invoked with the SqlNotificationEventArgs parameter showing Info:Invalid, Source:Statement, Type:Subscribe.

Anyone know what the problem might be or how to determine/debug what it is (without using SQL profiler which i do not have atm).

Answer 1

You have to use the two part names (dbo.TableName) for your tables in the SQL select statement in order to use 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 requirements for query notifications: MSDN Query Notifications.

Hope, this helps.