Tuesday, November 20, 2012

ASP.NET ~ SQL Cache Notification issue

After enabling the database and table in sql server to support sql caching, I noticed that the cache is not still reflected with the updates. I verified to make sure if the database and tables are enabled and found that those are enabled. I opened the AspNet_SqlCacheTablesForChangeNotification table in sql server and noticed my table listed. The web.config file was also updated properly but still the cache was not getting invalidated. This was driving me crazy as why is the notification not working.

 

I enabled the database to support sql caching as follows:

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727>aspnet_regsql -S <<server>> -E -d <<myDB>> –ed

 

I enabled the table to support sql caching as follows:

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727>aspnet_regsql -S <<server>> -E -t <<myTable>> -d <<myDB>> –et

 

I have also set the web.config to support sql caching as follows:

 

    <caching>

      <sqlCacheDependency enabled="true" pollTime="2000">

        <databases>

          <add name="<<myDB>>" connectionStringName="myDBConnString" />

        </databases>

      </sqlCacheDependency>

    </caching>

 

After a while I checked the AspNet_SqlCacheTablesForChangeNotification table again just to make sure if everything is alright and that the table name is listed properly. I noticed that the case of the table name was different in AspNet_SqlCacheTablesForChangeNotification table. I was not sure but I just wanted to change case exactly as how it is displayed in the list of tables and give a try. I changed the table name manually in AspNet_SqlCacheTablesForChangeNotification table and then I tried again and it worked.

 

Whew….That was a great relief.

Hope this helps someone facing similar problem