Help needed, ASP.NET caching with SQL Server doesn't work

[Toni]

Hello! I'm trying to use ASP.NET caching with my web site and SQL Server,
but I have a problem.

I try to do everything according to the instructions like this page here:
http://www.eggheadcafe.com/articles/20060407.asp

First I run the aspnet_regsql tool on the command line succesfully to enable
the database for cache notification:
aspnet_regsql -S [SERVER] -E -d [database] -ed

Then I run the following command on the command line succesfully for each
table in the database:
aspnet_regsql -S [SERVER] -E -d [database] -et -t

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

<connectionStrings>
<add name="ConnectionString1"
connectionString="Server=servername;Database=databasename;Trusted_Connection=yes"
/>
</connectionStrings>

<caching>
<sqlCacheDependency enabled="true" pollTime="2000">
<databases>
<add connectionStringName="ConnectionString1"
name="KJ"/>

</databases>
</sqlCacheDependency>
</caching>

Then I added the following line into the beginning of the page default.aspx
<%@ OutputCache Duration="86400" VaryByParam="*"
SqlDependency="KJ:ILMOITUKSET" %>

But when I run the page default.aspx it gives the following error:
-----------------------------
The 'ILMOITUKSET' 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 aspnet_regsql.exe with the -lt parameter to see the list of
tables that are enabled for cache notification they are all enabled. Could
someone help me with this problem and tell me what I'm doing wrong? Thank
you so much in advance.

Toni

 

[Cowboy \(Gregory A. Beamer\)]

First, are you using SQL Server 7 or 2000? They use the version of caching
you talk about. If you are using SQL Server 2005, it is automatic when you
use the SQLCacheDependency instead.

 

[Toni]

I am using SQL Server 2005 Express Edition. I tried to create the cache
programmatically. I removed the line

<%@ OutputCache Duration="86400" VaryByParam="*"
and wrote the following code:

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

I added into global.asax line:
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())

I still get the same error.

Toni S:

First, are you using SQL Server 7 or 2000? They use the version of caching
you talk about. If you are using SQL Server 2005, it is automatic when you
use the SQLCacheDependency instead.

Hello! I'm trying to use ASP.NET caching with my web site and SQL Server,
but I have a problem.

I try to do everything according to the instructions like this page here:
http://www.eggheadcafe.com/articles/20060407.asp

First I run the aspnet_regsql tool on the command line succesfully to
enable the database for cache notification:
aspnet_regsql -S [SERVER] -E -d [database] -ed

Then I run the following command on the command line succesfully for each
table in the database:
aspnet_regsql -S [SERVER] -E -d [database] -et -t

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

<connectionStrings>
<add name="ConnectionString1"

connectionString="Server=servername;Database=databasename;Trusted_Connection=yes"
/>
</connectionStrings>

<caching>
<sqlCacheDependency enabled="true" pollTime="2000">
<databases>
<add connectionStringName="ConnectionString1"
name="KJ"/>

</databases>
</sqlCacheDependency>
</caching>

Then I added the following line into the beginning of the page
default.aspx
<%@ OutputCache Duration="86400" VaryByParam="*"
SqlDependency="KJ:ILMOITUKSET" %>

But when I run the page default.aspx it gives the following error:
-----------------------------
The 'ILMOITUKSET' 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 aspnet_regsql.exe with the -lt parameter to see the list of
tables that are enabled for cache notification they are all enabled.
Could someone help me with this problem and tell me what I'm doing wrong?
Thank you so much in advance.

Toni

 

[poojamca]

hi guys,
i m able to store data in dataset but dataset not refreshed on database change.in dataset i m storing three tables as a result of store procedure.
can anyone help me please.