problems with SQL Server cache invalidation

Hi I am using SQL Server cache invalidation with caching in my application.

I have a master page and several other pages that are referring the master page.

I have specified :

<%@.OutputCacheDuration="60"VaryByParam="*"SqlDependency="CommandNotification" %> on one of my content page.

and I have included the

System.Data.SqlClient.SqlDependency.Start(connectionstring) in my Application_Start.

My web.config contains this section as well -

<caching>

<sqlCacheDependencyenabled="true">

<databases>

<addname="BizPartnerV4"connectionStringName="BizPartnerConnectionString"/>

<addname="DirectBuyBeaverton"connectionStringName="DirectBuyBeavertonConnectionString"/>

</databases>

</sqlCacheDependency>

I</caching>

have also run the aspnet_regsql utility to enable sqlcache dependency for the database.

But my page is not taking the values from cache when the page is refreshed.

Please help.

Hi,

you have two options to work with SQLServer cache:

1.push from the SQL Server

2.polling from Web server

Check your configuration to see if it si configured the right way.

|||

What are you using for the database? SQL Server 2005, SQL Express, or SQL Server 2000?

|||

I am using SQL Server 2005.

I guess it is using the push model

|||

Change VaryByParam from * to None, and see if it is now caching.

|||

no it still doesn't cache

|||

If you use implement cache with SQLServer 2005 , try the following steps:

1) Enable notifications for the database using the aspnet_regsql.exe tool. >aspnet_regsql.exe -S ".\SQLExpress" -E -d "pubs" -ed
This only needs to be done once for each database.

2) Enable notifications for the table(s) you want to have dependencies on using the aspnet_regsql.exe tool. >aspnet_regsql.exe -S ".\SQLExpress" -E -d "pubs" -et -t "authors"
3) Register the notification in the configuration for the application. <system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="1000" >
<databases>
<add name="PubsDB" connectionStringName="Pubs" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>The poll time specifies how often the application checks to see whether the data has changed.

4) A SQL dependency can then be used on the OutputCache directive: <%@. OutputCache Duration="999999" SqlDependency="Pubs:Authors" VaryByParam="none" %>Or it can be specified directly on a datasource control: <asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite" SqlCacheDependency="PubsDB:Authors" ... />

Hope this helps.

Thanks.