ASP.NET cachin with SQL Server doesnt work. Help needed.

Hello! I'm trying to get caching work with ASP.NET 2.0 and SQL Server 2005, but I have a problem and I can't figure out what I'm doing wrong.

I have tried to do everything according to the instructions. I have enabled the database for cache notification by running the aspnet_regsql tool on the
command line like this:
aspnet_regsql -S server -E -d database -et

Then I have run the aspnet_regsql tool to enable each table in the database for cache notification:
aspnet_regsql -S server -E -d database -et -t tablename

I have added the necessary parts into web.config file, so that it is like this:

<connectionStrings>
<add name="ConnectionString1" connectionString="Data
Source=servername;Database=databasename;Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="2000">
<databases>
<add connectionStringName="ConnectionString1"
name="KJ"/>
</databases>
</sqlCacheDependency>
</caching
I have added this into global.asax file for the application start event:
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())

To test caching I wrote this piece of code where I get data from the table MAAKUNNAT in the database.
----
SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString(),
"MAAKUNNAT")
Dim maakunnatDS As New DataSet

If Cache.Get("maakunnat") Is Nothing Then
Dim myConnection As New
SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())
myConnection.Open()

Dim cmd As New SqlCommand("haemaakunnat", myConnection)
cmd.CommandType = CommandType.StoredProcedure
Dim riippuvuus As New SqlCacheDependency("KJ", "MAAKUNNAT")
Dim aggDep As New AggregateCacheDependency
aggDep.Add(riippuvuus)

Dim maakunnatDA As SqlDataAdapter = New SqlDataAdapter
maakunnatDA.SelectCommand = cmd

maakunnatDA.Fill(maakunnatDS, "MAAKUNTA")
myConnection.Close()
Cache.Insert("maakunnat", maakunnatDS, aggDep)

Else
Response.Write("The data is in the cache")
maakunnatDS = Cache.Get("maakunnat")
End If
------
But when I run the page I get this error.
-------------

The 'MAAKUNNAT' table in the database 'KJ' is not enabled for SQL cache
notification.

Please make sure the table exists, and the table name used for cache
dependency matches exactly the table name used in cache notification
registration.

To enable a table for SQL cache notification, please use
SqlCacheDependencyAdmin.EnableTableForNotifications method, or the command
line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?'
for more information.

To get a list of enabled tables in the database, please use
SqlCacheDependencyManager.GetTablesEnabledForNotifications method, or the
command line tool aspnet_regsql.exe.
--------------
When I run the aspnet_regsql tool with the -lt parameter to get a list of enabled tables in the database they are all enabled. I have searched many pages in the internet and I cannot find out what I'm still doing wrong. I'd appreciate it very much if someone could help me with this problem.

Toni S.

Have you set the Session state mode to Sql Server in web.config and in IIS?

|||

I don't think that I have. How do you do that?

|||

Well to do that in IIS

right click website/webapp-properties->Asp.net Tab->Press Edit Configuration->Select State Management->Select Session state mode dropdown to Sql Server, specify the connectionstring in connectionstring setting.

|||

I am terribly sorry I have been guiding you for Session in Sql server rather then caching.

Try this as a sample first ...

http://msdn2.microsoft.com/en-us/library/e3w8402y(VS.80).aspx

|||

OK, I got it working now. Thank you for your help! I tried the example on this page that you suggested using my original database, but it didn't work. I created a simple test database for the example and that worked just fine. I needed to disable the cache dependencies for the tables in my original database and then re-enable them. I think the problem was that since the table names in my database are written all in capital letters, I needed to write the table names in capital letters also when using the aspnet_regsql tool to enable them for cache notification, like this:

aspnet_regsql ... -et -t TABLENAME