I've spent the last few days trying to get caching working with my C# ASP.NET web application and SQL Sever 2005. I'd like to take advantage of row-level caching by using a SqlCacheDependency on the SQL command used to fetch the row of data. However, I can't seem to get it to work.From a high level I can retreive my data fine, get it into the cache, and then retrieve it just fine. However, when the data is modified in the database, the cached object does not expire. The best I can tell is that there are no SQL Notification subscriptions that are set up. It appears that the broker service is set up on the database and I can see a SqlQueryNotificationService in the Queue and Services. There don't seem to be any errors in the SQL log that I can find either.To set everything up on my database I did the following:Made sure that ENABLE_BROKER was set on the database (select is_broker_enabled from sys.databases where name = '<dbname>' returns 1)Set privaleges for my database user properly with:GRANT CREATE PROCEDURE TO <dbuser>GRANT CREATE QUEUE TO <dbuser>GRANT CREATE SERVICE TO <dbuser>GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <dbuser>We typically use a data access layer for accessing our data, however, I wanted to test a stupid simple example of caching when I found I couldn't get it to work.
View Complete Post