SqlCacheDependency and CommandNotification Rule
Continuing with my study using AppDev.com course, I feel compelled to mention two crucial steps in setting up “Query Notification” when using database caching techniques. I spent almost two days trying to get this techique to work! Thanks to AppDev it works now.
After adding
<%@ OutputCache SqlDependency="CommandNotification" Duration="9999999" VaryByParam="None" %>
there are two things that we must get right for this setup to work:
1. Establishing the target table and writing a correct query, as outlined in this MSDN article.
2. Configuring Service Broker as listed below
USE YourDatabaseName;
– Ensure compatibility with SQL Server 2005
EXECUTE sp_dbcmptlevel ‘Northwind’,90;– Enable Service Broker
IF NOT EXISTS (SELECT * FROM sys.databases
WHERE name = ‘YourDatabaseName‘ AND is_broker_enabled = 1)
BEGIN
ALTER DATABASE Northwind SET ENABLE_BROKER
WITH ROLLBACK AFTER 5 SECONDS;
– Set Trustworthy
ALTER DATABASE Northwind SET TRUSTWORTHY ON;
END;
GO– Create an encryption key for Service Broker
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys
WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N‘t0psecret’;
GO– Create a Service Broker endpoint
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS );
GOselect * from sys.dm_qn_subscriptions
It’s easy when you know 
