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 [table]

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

<connectionStrings>
<add name="ConnectionString1"
connectionString="Server=servername;Database=datab asename;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.EnableTableForNotification s 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.GetTablesEnabledForNotif ications 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.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************
"Toni" <no****@hotmail555.comwrote in message
news:OC**************@TK2MSFTNGP02.phx.gbl...

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 [table]

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

<connectionStrings>
<add name="ConnectionString1"

connectionString="Server=servername;Database=datab asename;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.EnableTableForNotification s 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.GetTablesEnabledForNotif ications 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

[Toni]

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

<%@ OutputCache Duration="86400" VaryByParam="*"

>SqlDependency="KJ:ILMOITUKSET" %>

and wrote the following code:

SqlCacheDependencyAdmin.EnableTableForNotification s(ConfigurationManager.ConnectionStrings("Connecti onString1").ToString(),
"MAAKUNNAT")
Dim maakunnatDS As New DataSet

If Cache.Get("maakunnat") Is Nothing Then
Dim myConnection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("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(Configur ationManager.ConnectionStrings("ConnectionString1" ).ToString())

I still get the same error.

Toni S:

"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamMkirjoitti
viestissä:O%****************@TK2MSFTNGP06.phx.gbl. ..

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.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************
"Toni" <no****@hotmail555.comwrote in message
news:OC**************@TK2MSFTNGP02.phx.gbl...

>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 [table]

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

<connectionStrings>
<add name="ConnectionString1"

connectionString="Server=servername;Database=data basename;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.EnableTableForNotificatio ns 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.GetTablesEnabledForNoti fications 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