Registering SQL Cache Dependency in web.config. Why elsewhere, too?

I have a sqlCacheDependency element registered in web.config that I figured would set the database up to deliver all notifications for all its tables:

<

sqlCacheDependencyenabled="true">

<

databases>

<

addname="MyDb"connectionStringName="MyDbConnnectionString"/>

</

databases>

</

sqlCacheDependency>

I've seen docs saying that's all I need to do with SQL Server 2005, but that doesn't appear to be the case. I get the error:

The database 'MyDB' is not enabled for SQL cache notification.

To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.

Are the docs I read wrong or am I missing something?

I beleive the sytax would be:

<

caching>

<

sqlCacheDependencyenabled ="true" >

<

databases>

<

addname="MyDb"connectionStringName="MyDbConnnectionString" />

</

databases>

</

sqlCacheDependency>

</

caching>

And then on your aspx page:

<asp:sqldatasourceid="Whatever"runat="server"connectionstring="<%$ ConnectionStrings:MyDbConnnectionString %>"datasourcemode="DataSet"enablecaching="true"CacheKeyDependency="MyDb"selectcommand="Whatever_SP"selectcommandtype="StoredProcedure">

|||

The web.config file is set up correctly, my problem is that I can't get notification services working. I have to resort to polling although I have SQL Server 2005.

To add more detail, I'm using Windows XP Pro and I programatically add the SqlCacheDependency:

SqlCacheDependency myDependency = new SqlCacheDependency( "MyDb", "MyTable" );

Cache.Insert( blah,
blah,
...
myDependency
... );

Everything executes correctly, but unless I use the SqlCacheDependencyAdmin or aspnet_regsql to set up polling, it throws the error displayed in my first post. If I have polling set up, the error goes away, but I'm not taking full advantage of the performance benefits of SQL Server 2005 notification services.

|||Anyone have any ideas on this? I'm perplexed!|||

I think most of what you need is covered in the links below because it maybe permissions related. Hope this helps.

http://msdn2.microsoft.com/en-us/library/system.web.caching.sqlcachedependencyadmin.aspx

http://msdn2.microsoft.com/en-us/library/xh507fc5.aspx

|||

I wondered if there might be some permission issues, though I haven't discovered what permissions may be required.

What's curious is that I'd expect the SqlCacheDependency to throw some permissions exception in such a case, however, and I wasn't seeing that.

I'll have to keep digging...

|||

Bump! I was hoping the issue would go away when I moved our SQL Server to a development server, but the problem persists.

My progress:

1. I discovered SQL Server can't run under the local machine account for Notification Services to work properly so I switched to Network Service... didn't help.

2. Apparently you need to manually create a Service Broker endpoint and enable the database to use it. Also didn't help...

3. I read a lot of people said they have to call SqlDependency.Start() to get it working... didn't help.

|||When pemissions related problem cannot be traced it usually means the service in this case the Notification service may be SQL Server Agent dependent which means the account that runs the Agent must have Admin permissions. The reason is even to run Replications the Agent needs network access and DTS automation, the list goes on. Hope this helps.|||Okay, the latest in the saga is that a Microsoft support rep is claiming the SQL Server 2005 lost the ability to integrate with SqlCacheDependency through anything other than polling in the release version. This seems fishy, since it contradicts their documentation and a lot of other resources, so I'll see if I can get to the bottom of it.|||As I suspected, query notification-based cache invalidation is alive and well in SQL Server 2005. I did a little digging and ended up finding a couple of issues:

1. You can't use the constructor I was using to set up notification-based dependencies (you need to pass in the SqlCommand).
2. You need to exclude the command SET NOCOUNT ON from your stored procedure.

See here:http://forums.asp.net/1353521/ShowThread.aspx#1353521 for more.