Why is my SqlDependency not firing.

Question

I have a database running on MS SQL Server 2005 and an ASP.NET 3.5 web application.

The database contains a product catalog which I'm using to feed "pages" into a CMS running in the web app. Once the pages have been created the application caches them so I need to notify the application of this change so it can recreate the page objects.

The CMS uses it's own caching so an SqlCacheDependency cannot be used to perform this task.

When I fire the app up I do get a subscriber appearing in the result of

select * from sys.dm_qn_subscriptions

But as soon as I add some data to the table, the subscriber disappears and the OnChanged event never fires.

In my startup code I have the following

// Ensure the database is setup for notifications
SqlCacheDependencyAdmin.EnableNotifications(DataHelper.ConnectionString);
SqlCacheDependencyAdmin.EnableTableForNotifications(DataHelper.ConnectionString, "Category");
SqlCacheDependencyAdmin.EnableTableForNotifications(DataHelper.ConnectionString, "Product");

if (!SqlDependency.Start(DataHelper.ConnectionString, SqlDependencyQueueName))
     throw new Exception("Something went wrong");

string queueOptions = string.Format("service = {0}", SqlDependencyQueueName);
using (var connection = new SqlConnection(DataHelper.ConnectionString))
{
     connection.Open();
     using (SqlCommand command = new SqlCommand(@"SELECT [CategoryID],[Name]
                   FROM [dbo].[Category]", connection))
     {
           // Create a dependency and associate it with the SqlCommand.
           dependency = new SqlDependency(command, queueOptions, 0);

            // Subscribe to the SqlDependency event.
            dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

            command.ExecuteReader().Close();
        }
    }

// ...

void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
    Debugger.Break(); // This never hits
    this.ReloadData();
}

Answer 1

Check your database sys.transmission_queue. Most likely your notification(s) will be there, retained because they cannot be delivered. The transmission_status will have an explanation why is this happening. For a more detailed troubleshooting guide, see Troubleshooting Dialogs.

The most often issue is due to EXECUTE AS infrastructure requirements not being satisfied by an orphaned database dbo ownership and can be resolved via:

ALTER AUTHORIZATION ON DATABASE::<dbname> TO [sa];

however the solution depends from case to case, depending on the actual problem, as investigated above.