SqlCacheDependency not invalidating

Question

yisman 0 Newbie Poster

17 Years Ago

Ok fellows. I've tried EVERYTHING!!

System
OS: Windows Vista SP2
IIS: version 7
MSSQL: 2008 express
ASPX: .NET 3.5 framework

my global.asax has sqlcachedependency.start and stop at application start and end
i have enabled sqlnotifications on the database.

The database has a table for tracking the changes and the track counter increments correctly with each add/edit/delete to the table.

On the asp.net side i have this simple page

<%@ Page Language="VB" CodeFile="SizeTest.aspx.vb" Inherits="SizeTest" %>

<%@ OutputCache Duration="50000" VaryByParam="None" SqlDependency="MyDay:SitePages" %>
<html>
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" DataKeyNames="SitePageID" DataSourceID="SqlDataSource1">
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDay%>"
            ProviderName="<%$ ConnectionStrings:MyDay.providername %>" SelectCommand="SELECT SitePageID, PageName FROM SitePages" />
    </div>
    </form>
</body>
</html>

Where "MyDay" is the name of my db as well as the name of the connectionstring in the web.config

here is my web.config parts

<caching>
            <sqlCacheDependency enabled="true">
                <databases>
                    <add name="MyDay" connectionStringName="MyDay" />
                </databases>
            </sqlCacheDependency>            
        </caching>

Now, all this does not help get my aspx page invalidate on data change

the procedures and broker stuff all get created on app start. but the data remains stale
i granted notification permissions to any user i could think of (at least i believe i have). i would note that i did not find in my sytem any type of user/login called aspnet or the like, if that means anything.
i tried changing my connectionstring from a windows integrate login to a sql server login with u&p. still stale data.
i read tens of articles, i also added a masterkey to the db. really idid every step i could find. nothing helps!

im feeling desperate here.

can anyone help out?
thanks to all!

.net caching microsoft:sql-server

2 Contributors
6 Replies
215 Views
4 Days Discussion Span
Latest Post 17 Years Ago Latest Post by yisman

Dani AI

Generated 7 Months Ago

Quick diagnosis and what to try next (short checklist for — and , this is the “source you can run” that helps debug):

There are two very different ASP.NET SQL-cache mechanisms in play: the polling/table-based SqlCacheDependency (aspnet_regsql-created table+triggers) and the push/query-notification model that uses Service Broker + SqlDependency. Mixing them or having only part of the plumbing for the push model is the usual cause of “never invalidates.” Confirm which model you want and test that one end‑to‑end. (learn.microsoft.com)

Quick server checks to run (run in SSMS as an admin):

-- Is Service Broker enabled?
SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDay';

-- See active query-notification subscriptions
SELECT id, database_id, status FROM sys.dm_qn_subscriptions
  WHERE database_id = DB_ID('MyDay');

If broker is disabled, enable it (requires exclusive access):

ALTER DATABASE [MyDay] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

(Do this only after confirming it's safe for your environment.) (learn.microsoft.com)

Common failures and how to spot them:
- Query notifications require specific session/set options and strict query syntax (schema-qualified table name, no SELECT *, no unsupported types). If those are wrong SQL will register a subscription and immediately fire it with Info = “statement” or “set options” — you won’t get a visible exception unless you log the notification args. Check the “magic” SET options required. (learn.microsoft.com)
Minimal programmatic test to see exactly what SQL reports (attach a SqlDependency and print the OnChange args). This will tell you whether the subscription failed (Info) or fired because of data change:

' VB minimal debug example (run in a console app)
SqlDependency.Start(connString)
Using cn = New SqlConnection(connString)
  cn.Open()
  Using cmd = New SqlCommand("SELECT SitePageID, PageName FROM dbo.SitePages", cn)
    Dim dep = New SqlDependency(cmd)
    AddHandler dep.OnChange, Sub(s,e) Console.WriteLine("Type={0} Source={1} Info={2}", e.Type, e.Source, e.Info)
    Using r = cmd.ExecuteReader() : End Using
  End Using
  Console.ReadLine()
End Using
SqlDependency.Stop(connString)

Log the printed Info/Source/Type — they point to the exact problem. (learn.microsoft.com)

If you want immediate invalidation choose query notifications (push) and fix broker/SET/options/permissions (GRANT SUBSCRIBE QUERY NOTIFICATIONS to the DB principal). If you prefer the simpler route, stick to table‑based polling and confirm the AspNet_SqlCacheTablesForChangeNotification row + trigger + pollTime are correct. (learn.microsoft.com)

If you paste the OnChange output (Info/Source/Type) someone can point to the precise fix quickly.

All 6 Replies

IdanS 12 Junior Poster in Training

17 Years Ago

Do you get en exception message in run time like permission is not allowed or somthing like this?

IdanS 12 Junior Poster in Training

17 Years Ago

Upload you entire source code here please.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

yisman 0 Newbie Poster · Answer 1 · 2009-06-30T19:00:25+00:00

hi IdanS
thanks for replying
no. i have not recieved any error message whatsoever
what can i do?
thanks for bearing with me

yisman 0 Newbie Poster · Answer 2 · 2009-07-03T13:43:36+00:00

Hi Idans.
I do not have any source code aside of the snippets i sent you. its entirely declaritive.
i hope to eventually have. but first im trying the simple.
are you referring to the global.asax? ill post here those lines as well

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs on application startup
        AddHandler SiteMap.SiteMapResolve, AddressOf AddDynaNode
        SqlDependency.Start(ConfigurationManager.ConnectionStrings("MyDay").ConnectionString)
    End Sub
    
    Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs on application shutdown
        SqlDependency.Stop(ConfigurationManager.ConnectionStrings("MyDay").ConnectionString)
    End Sub

Anything else?
Heres the code behind (totally empty)

Partial Class SizeTest
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Response.Write("Page Created: " & Now.ToLongTimeString)
    End Sub
End Class

Thanks again for keeping along. i deeply appreciate it.
i will meanwhile try to make a brand new test site with only this page, to see t it works out.
waiting to hear from you.

yisman 0 Newbie Poster · Answer 3 · 2009-07-03T14:10:39+00:00

Hi
I am attaching a complete website solution. it includes precisely
1 aspx page
1 web.config
1 global.asax

all torn down to the barest minimum necessary. it still does not work.
for redundancy ill post all code here as well:
DEFAULT.ASPX

<%@ Page Language="VB" %>

<%@ OutputCache Duration="50000" VaryByParam="None" SqlDependency="MyDay:SitePages" %>
<html>
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" DataKeyNames="SitePageID" DataSourceID="SqlDataSource1" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDay %>"
            SelectCommand="SELECT [SitePageID], [PageName] FROM [SitePages]" />
    </div>
    </form>
</body>
</html>

GLOBAL.ASAX

<%@ Application Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

    Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        SqlDependency.Start(ConfigurationManager.ConnectionStrings("MyDay").ConnectionString)
    End Sub
    
    Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
        SqlDependency.Stop(ConfigurationManager.ConnectionStrings("MyDay").ConnectionString)
    End Sub
        
</script>

WEB.CONFIG

<?xml version="1.0"?>
<configuration>
    <configSections>
        <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
            <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                    <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere" />
                    <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
                    <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
                    <section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
                </sectionGroup>
            </sectionGroup>
        </sectionGroup>
    </configSections>
    <connectionStrings>
        <add name="MyDay" connectionString="Data Source=INSPIRON\SQLEXPRESS;Initial Catalog=MyDay;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <system.web>
        <caching>
            <sqlCacheDependency enabled="true">
                <databases>
                    <add name="MyDay" connectionStringName="MyDay"/>
                </databases>
            </sqlCacheDependency>
        </caching>
        <compilation debug="true" strict="false" explicit="true">  
        </compilation>
        <httpHandlers>
            <remove verb="*" path="*.asmx"/>
            <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
        </httpHandlers>
        <httpModules>
            <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </httpModules>
        <pages>           
            <controls>
                <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            </controls>
        </pages>
    </system.web>

    <system.codedom>
        <compilers>
            <compiler language="c#;cs;csharp" extension=".cs" warningLevel="4"
                      type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
            <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4"
                      type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="OptionInfer" value="true"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
        </compilers>
    </system.codedom>
    <system.webServer>
        <validation validateIntegratedModeConfiguration="false"/>
        <modules>
            <remove name="ScriptModule" />
            <add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </modules>
        <handlers>
            <remove name="WebServiceHandlerFactory-Integrated"/>
            <remove name="ScriptHandlerFactory" />
            <remove name="ScriptHandlerFactoryAppServices" />
            <remove name="ScriptResource" />
            <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode"
                 type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode"
                 type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        </handlers>
    </system.webServer>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
            </dependentAssembly>
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
</configuration>

Thank you again

yisman 0 Newbie Poster · Answer 4 · 2009-07-05T02:15:01+00:00

Hello Everybody
I cant figure this thing out. I now made a complete brand-new one-table database just for this test. still no go. one thing that i have not been able to do is, that i cannot seem to grant query notification rights to the default/trusted/windows login.
i am now pasting my complete db code, so maybe you can find some privilege problem. or any other problem that might be wrong
i am using ssms 2008 express.

USE [CacheBase]
GO
/****** Object:  Role [aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess]    Script Date: 07/04/2009 23:10:05 ******/
CREATE ROLE [aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess] AUTHORIZATION [dbo]
GO
/****** Object:  User [Yisman]    Script Date: 07/04/2009 23:10:05 ******/
CREATE USER [Yisman] FOR LOGIN [Yisman] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  Schema [aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess]    Script Date: 07/04/2009 23:10:05 ******/
CREATE SCHEMA [aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess] AUTHORIZATION [dbo]
GO
/****** Object:  Table [dbo].[AspNet_SqlCacheTablesForChangeNotification]    Script Date: 07/04/2009 23:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNet_SqlCacheTablesForChangeNotification](
	[tableName] [nvarchar](450) NOT NULL,
	[notificationCreated] [datetime] NOT NULL,
	[changeId] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[tableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[AspNet_SqlCacheTablesForChangeNotification] ([tableName], [notificationCreated], [changeId]) VALUES (N'SitePages', CAST(0x00009C3C0177280E AS DateTime), 34)
/****** Object:  StoredProcedure [dbo].[SqlQueryNotificationStoredProcedure-3fbe6514-7c73-4167-9d17-3280dfa1541d]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SqlQueryNotificationStoredProcedure-3fbe6514-7c73-4167-9d17-3280dfa1541d] AS BEGIN BEGIN TRANSACTION; RECEIVE TOP(0) conversation_handle FROM [SqlQueryNotificationService-3fbe6514-7c73-4167-9d17-3280dfa1541d]; IF (SELECT COUNT(*) FROM [SqlQueryNotificationService-3fbe6514-7c73-4167-9d17-3280dfa1541d] WHERE message_type_name = '') > 0 BEGIN DROP SERVICE [SqlQueryNotificationService-3fbe6514-7c73-4167-9d17-3280dfa1541d]; DROP QUEUE [SqlQueryNotificationService-3fbe6514-7c73-4167-9d17-3280dfa1541d]; DROP PROCEDURE [SqlQueryNotificationStoredProcedure-3fbe6514-7c73-4167-9d17-3280dfa1541d]; END COMMIT TRANSACTION; END
GO
/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] 
             @tableName NVARCHAR(450) 
         AS
         BEGIN

         DECLARE @triggerName AS NVARCHAR(3000) 
         DECLARE @fullTriggerName AS NVARCHAR(3000)
         DECLARE @canonTableName NVARCHAR(3000) 
         DECLARE @quotedTableName NVARCHAR(3000) 

         /* Create the trigger name */ 
         SET @triggerName = REPLACE(@tableName, '[', '__o__') 
         SET @triggerName = REPLACE(@triggerName, ']', '__c__') 
         SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' 
         SET @fullTriggerName = 'dbo.[' + @triggerName + ']' 

         /* Create the cannonicalized table name for trigger creation */ 
         /* Do not touch it if the name contains other delimiters */ 
         IF (CHARINDEX('.', @tableName) <> 0 OR 
             CHARINDEX('[', @tableName) <> 0 OR 
             CHARINDEX(']', @tableName) <> 0) 
             SET @canonTableName = @tableName 
         ELSE 
             SET @canonTableName = '[' + @tableName + ']' 

         /* First make sure the table exists */ 
         IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL 
         BEGIN 
             RAISERROR ('00000001', 16, 1) 
             RETURN 
         END 

         BEGIN TRAN
         /* Insert the value into the notification table */ 
         IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHERE tableName = @tableName) 
             IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX) WHERE tableName = @tableName) 
                 INSERT  dbo.AspNet_SqlCacheTablesForChangeNotification 
                 VALUES (@tableName, GETDATE(), 0)

         /* Create the trigger */ 
         SET @quotedTableName = QUOTENAME(@tableName, '''') 
         IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = 'TR') 
             IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = 'TR') 
                 EXEC('CREATE TRIGGER ' + @fullTriggerName + ' ON ' + @canonTableName +'
                       FOR INSERT, UPDATE, DELETE AS BEGIN
                       SET NOCOUNT ON
                       EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N' + @quotedTableName + '
                       END
                       ')
         COMMIT TRAN
         END
GO
/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure] 
         AS
         SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification
GO
/****** Object:  StoredProcedure [dbo].[AspNet_SqlCachePollingStoredProcedure]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AspNet_SqlCachePollingStoredProcedure] AS
         SELECT tableName, changeId FROM dbo.AspNet_SqlCacheTablesForChangeNotification
         RETURN 0
GO
/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] 
             @tableName NVARCHAR(450) 
         AS

         BEGIN 
             UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH (ROWLOCK) SET changeId = changeId + 1 
             WHERE tableName = @tableName
         END
GO
/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure] 
             @tableName NVARCHAR(450) 
         AS
         BEGIN

         BEGIN TRAN
         DECLARE @triggerName AS NVARCHAR(3000) 
         DECLARE @fullTriggerName AS NVARCHAR(3000)
         SET @triggerName = REPLACE(@tableName, '[', '__o__') 
         SET @triggerName = REPLACE(@triggerName, ']', '__c__') 
         SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' 
         SET @fullTriggerName = 'dbo.[' + @triggerName + ']' 

         /* Remove the table-row from the notification table */ 
         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U') 
             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U') 
             DELETE FROM dbo.AspNet_SqlCacheTablesForChangeNotification WHERE tableName = @tableName 

         /* Remove the trigger */ 
         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = 'TR') 
             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = 'TR') 
             EXEC('DROP TRIGGER ' + @fullTriggerName) 

         COMMIT TRAN
         END
GO
/****** Object:  Table [dbo].[SitePages]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SitePages](
	[SitePageID] [int] IDENTITY(1,1) NOT NULL,
	[AddedOn] [datetime] NOT NULL,
	[AddedBy] [int] NOT NULL,
	[ParentID] [int] NULL,
	[PageName] [nvarchar](50) NOT NULL,
	[URL] [nvarchar](100) NULL,
	[Title] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](100) NULL,
	[PageHTML] [nvarchar](max) NULL,
	[MenuShow] [bit] NULL,
	[Priority] [int] NOT NULL,
 CONSTRAINT [PK_SitePages] PRIMARY KEY CLUSTERED 
(
	[SitePageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SitePages] ON
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (1, CAST(0x00009B8400000000 AS DateTime), 35, NULL, N'Homeu', NULL, N'Home', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (3, CAST(0x00009C2C00E08176 AS DateTime), 35, 1, N'Shop', N'~\Default.aspx?shop', N'Shop', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (4, CAST(0x00009C2C00E179F5 AS DateTime), 35, 3, N'Blog', N'~\Blog.aspx', N'Blog', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (5, CAST(0x00009C2C00E23684 AS DateTime), 35, 3, N'AboutUs', NULL, N'About Us', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (6, CAST(0x00009C2C00E277CD AS DateTime), 35, 3, N'Cart', N'~\Cart.aspx', N'Cart', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (7, CAST(0x00009C2C00EE85E5 AS DateTime), 35, 3, N'ContactUs', N'~\ContactUs.aspx', N'Contact Us', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (8, CAST(0x00009C2C00F6A026 AS DateTime), 35, 3, N'Login', N'~\Login.aspx', N'Login', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (10, CAST(0x00009C2C00F7FDC1 AS DateTime), 35, 3, N'MyAccount', N'~\MyAccount.aspx', N'My Account', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (11, CAST(0x00009C2C00F85C53 AS DateTime), 35, 3, N'NewUser', N'~\NewUser.aspx', N'New User', NULL, NULL, NULL, 100)
INSERT [dbo].[SitePages] ([SitePageID], [AddedOn], [AddedBy], [ParentID], [PageName], [URL], [Title], [Description], [PageHTML], [MenuShow], [Priority]) VALUES (12, CAST(0x00009C2C00F8CDF9 AS DateTime), 35, 1, N'Orders', NULL, N'Orders', NULL, NULL, NULL, 100)
SET IDENTITY_INSERT [dbo].[SitePages] OFF
/****** Object:  Trigger [SitePages_AspNet_SqlCacheNotification_Trigger]    Script Date: 07/04/2009 23:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SitePages_AspNet_SqlCacheNotification_Trigger] ON [dbo].[SitePages]
                       FOR INSERT, UPDATE, DELETE AS BEGIN
                       SET NOCOUNT ON
                       EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'SitePages'
                       END
GO
/****** Object:  Default [DF__AspNet_Sq__notif__0AD2A005]    Script Date: 07/04/2009 23:10:08 ******/
ALTER TABLE [dbo].[AspNet_SqlCacheTablesForChangeNotification] ADD  DEFAULT (getdate()) FOR [notificationCreated]
GO
/****** Object:  Default [DF__AspNet_Sq__chang__0BC6C43E]    Script Date: 07/04/2009 23:10:08 ******/
ALTER TABLE [dbo].[AspNet_SqlCacheTablesForChangeNotification] ADD  DEFAULT ((0)) FOR [changeId]
GO
/****** Object:  Default [DF_SitePages_AddedOn]    Script Date: 07/04/2009 23:10:09 ******/
ALTER TABLE [dbo].[SitePages] ADD  CONSTRAINT [DF_SitePages_AddedOn]  DEFAULT (getdate()) FOR [AddedOn]
GO
/****** Object:  Default [DF_SitePages_MenuShow]    Script Date: 07/04/2009 23:10:09 ******/
ALTER TABLE [dbo].[SitePages] ADD  CONSTRAINT [DF_SitePages_MenuShow]  DEFAULT ((0)) FOR [MenuShow]
GO
/****** Object:  Default [DF_SitePages_Priority]    Script Date: 07/04/2009 23:10:09 ******/
ALTER TABLE [dbo].[SitePages] ADD  CONSTRAINT [DF_SitePages_Priority]  DEFAULT ((100)) FOR [Priority]
GO
/****** Object:  Check [CK_SitePages]    Script Date: 07/04/2009 23:10:09 ******/
ALTER TABLE [dbo].[SitePages]  WITH CHECK ADD  CONSTRAINT [CK_SitePages] CHECK  ((len([url])>(0)))
GO
ALTER TABLE [dbo].[SitePages] CHECK CONSTRAINT [CK_SitePages]
GO
/****** Object:  ForeignKey [frkSitePageParent]    Script Date: 07/04/2009 23:10:09 ******/
ALTER TABLE [dbo].[SitePages]  WITH CHECK ADD  CONSTRAINT [frkSitePageParent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[SitePages] ([SitePageID])
GO
ALTER TABLE [dbo].[SitePages] CHECK CONSTRAINT [frkSitePageParent]
GO

SqlCacheDependency not invalidating

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers