Why is my SqlDependency not working

Question

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

The database contains a product catalog that I use to feed the “pages” to the CMS running in the web application. Once the pages have been created, the application caches them, so I need to notify the application of this change so that it can recreate the page objects.

CMS uses its own caching, so SqlCacheDependency cannot be used to perform this task.

When I launch the application, I get a subscriber that appears as a result

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 starter code, I have

// 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

sys.transmission_queue. , , , . transmission_status , . .

, EXECUTE AS dbo :

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

, , .