Scott on Writing

The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies

In order to use SQL cache dependencies in ASP.NET 2.0 on the Express Edition of SQL Server 2005 or on SQL Server 7.0 or 2000, ASP.NET needs to continuously poll the database to determine if there's been any change to the monitored tables. For this to work, the database needs some infrastructure installed on it - namely a “maintenance” table named AspNet_SqlCacheTablesForChangeNotification that keeps track of each monitored table and its latest changeId, stored procedures for reading and writing to this table, and triggers on the monitored tables.

This infrastructure is created using the aspnet_regsql.exe command line program, pointing it to the appropriate database and specifying that the polling infrastructure should be created on the database (which adds the maintenance table and sprocs) as well as what tables need to be monitored (which adds the triggers). aspnet_regsql.exe contains a switch called -sqlexportonly that will output the SQL syntax to a specified file, but sadly this option does not work with the polling infrastructure option. I was curious as to the output so I used SQL Profiler to examine the incoming T-SQL from aspnet_regsql.exe. I figured others might be interested as well, so I post it here to save others the three minutes it took me to get it from SQL Profiler:

Running the database-related command, aspnet_regsql.exe -S server -d database -E -ed, results in the following T-SQL sent to the database:

/* Create notification table */ 
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U') 
   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U') 
      CREATE TABLE dbo.AspNet_SqlCacheTablesForChangeNotification (
      tableName             NVARCHAR(450) NOT NULL PRIMARY KEY,
      notificationCreated   DATETIME NOT NULL DEFAULT(GETDATE()),
      changeId              INT NOT NULL DEFAULT(0)
      )
/* Create polling SP */
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCachePollingStoredProcedure' AND type = 'P') 
   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCachePollingStoredProcedure' AND type = 'P') 
   EXEC('CREATE PROCEDURE dbo.AspNet_SqlCachePollingStoredProcedure AS
         SELECT tableName, changeId FROM dbo.AspNet_SqlCacheTablesForChangeNotification
         RETURN 0')
/* Create SP for registering a table. */ 
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheRegisterTableStoredProcedure' AND type = 'P') 
   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheRegisterTableStoredProcedure' AND type = 'P') 
   EXEC('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
   ')
/* Create SP for updating the change Id of a table. */ 
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheUpdateChangeIdStoredProcedure' AND type = 'P') 
   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheUpdateChangeIdStoredProcedure' AND type = 'P') 
   EXEC('CREATE PROCEDURE dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure 
             @tableName NVARCHAR(450) 
         AS
         BEGIN 
             UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH (ROWLOCK) SET changeId = changeId + 1 
             WHERE tableName = @tableName
         END
   ')
/* Create SP for unregistering a table. */ 
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheUnRegisterTableStoredProcedure' AND type = 'P') 
   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheUnRegisterTableStoredProcedure' AND type = 'P') 
   EXEC('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
   ')
/* Create SP for querying all registered table */ 
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheQueryRegisteredTablesStoredProcedure' AND type = 'P') 
   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheQueryRegisteredTablesStoredProcedure' AND type = 'P') 
   EXEC('CREATE PROCEDURE dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure 
         AS
         SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification   ')
/* Create roles and grant them access to SP  */ 
IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') 
    EXEC sp_addrole N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' 
GRANT EXECUTE ON dbo.AspNet_SqlCachePollingStoredProcedure to aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess

Running the table-related command, aspnet_regsql.exe -S server -d database -E -t tableName -et, simply calls the AspNet_SqlCacheRegisterTableStoredProcedure sproc passing in the name of the specified table:

exec dbo.AspNet_SqlCacheRegisterTableStoredProcedure @tableName=N'tableName'

This stored procedure - added when registering the infrastructure eith the -ed switch - creates an insert/update/delete trigger on the specified table that invokes the AspNet_SqlCacheUpdateChangeIdStoredProcedure stored procedure, passing in the table's name. AspNet_SqlCacheUpdateChangeIdStoredProcedure simply updates the changeId value in the appropriate row in AspNet_SqlCacheTablesForChangeNotification.

For more on using SQL cache dependencies in an ASP.NET 2.0 web application, see Caching in ASP.NET with the SqlCacheDependency Class, Overview of SqlCacheDependency, or wait for my upcoming Working with Data in ASP.NET 2.0 tutorial on the topic.

posted on Thursday, January 04, 2007 4:42 PM

Feedback

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/5/2007 7:41 AM David Dodson

Thanks so much Scott!

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/5/2007 10:46 AM Michael K. Campbell

Scott, the code for aspnet_regsql can also be found on your local file system. Just nav to your v2.0.50727 folder and sort on file types - you'll find a bunch of .sql files used by aspnet_regsql.exe.

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/5/2007 10:50 AM Scott Mitchell

Thanks for the heads up, Michael. That would have been a lot quicker than using SQL Profiler! :-)

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/16/2007 7:43 PM Michael K. Campbell

Scott,

After re-reading your post (the devil's in the details as they say *grin*), I'm wrong. You can get access to SOME of the scripts that aspnet_regsql.exe uses, but NOT this one - so your Profiler approach was the way to go.

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/28/2008 8:55 AM Scott Mitchell

Here is some email feedback I received from an individual named Carsten:

############################################

Thank you for your tip with the SQL Profiler. I have downloaded the trial of MS SQL Server 2005 and i want to inform you that the "ASP.NET SQL Server Registration Tool" automatically starts the following 6 SQL-Scripts:
1. InstallCommon.sql
2. InstallMembership.sql
3. InstallPersonalization.sql
4. InstallProfile.sql
5. InstallRoles.sql
6. InstallWebEventSQLProvider.sql

That means that the following 3 SQL-Scripts will not be executed:
1. InstallPersistSQLState
2. InstallSQLState
3. InstallSQLStateTemplate

Greetings,
Carsten

Title:

Name:

Url:

Enter the code you see:

Comments

Remember Me?

Day	Total	% of Total
Sunday	204	6.8%
Monday	425	14.1%
Tuesday	516	17.1%
Wednesday	553	18.4%
Thursday	580	19.2%
Friday	547	18.2%
Saturday	188	6.2%
Total	3013	100.0%

Hour¹	Total	% of Total
12:00 AM	77	2.6%
1:00 AM	81	2.7%
2:00 AM	68	2.3%
3:00 AM	82	2.7%
4:00 AM	69	2.3%
5:00 AM	126	4.2%
6:00 AM	118	3.9%
7:00 AM	180	6.0%
8:00 AM	192	6.4%
9:00 AM	158	5.2%
10:00 AM	187	6.2%
11:00 AM	193	6.4%
12:00 PM	201	6.7%
1:00 PM	184	6.1%
2:00 PM	169	5.6%
3:00 PM	135	4.5%
4:00 PM	115	3.8%
5:00 PM	106	3.5%
6:00 PM	101	3.4%
7:00 PM	107	3.6%
8:00 PM	92	3.1%
9:00 PM	88	2.9%
10:00 PM	89	3.0%
11:00 PM	95	3.2%
Total	3013	100.0%

Day Entry Made	Avg.	Total
Sunday	4.94	158
Monday	4.80	384
Tuesday	4.08	477
Wednesday	7.47	680
Thursday	6.25	675
Friday	5.02	462
Saturday	4.78	177
Total	5.41	3013

Hour¹ Entry Made	Avg.	Total
12:00 AM	5.29	37
1:00 AM	1.00	2
5:00 AM	0.00	0
7:00 AM	3.85	50
8:00 AM	3.72	134
9:00 AM	6.02	295
10:00 AM	5.63	276
11:00 AM	4.20	193
12:00 PM	6.14	350
1:00 PM	3.17	133
2:00 PM	5.00	230
3:00 PM	7.62	320
4:00 PM	4.00	108
5:00 PM	6.04	169
6:00 PM	4.64	116
7:00 PM	8.95	188
8:00 PM	8.63	164
9:00 PM	5.00	115
10:00 PM	6.31	101
11:00 PM	4.57	32
Total	5.41	3013

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/5/2007 7:41 AM David Dodson

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/5/2007 10:46 AM Michael K. Campbell

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/5/2007 10:50 AM Scott Mitchell

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/16/2007 7:43 PM Michael K. Campbell

# re: The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies 1/28/2008 8:55 AM Scott Mitchell

Scott on Writing

The T-SQL Emitted by the aspnet_regsql.exe Command Line Program when Adding the Infrastructure for SQL Cache Dependencies

Feedback

My Links

Archives

Post Categories

Comment Stats

Comments by Blog Entry Date/Time

Blog Stats

Favorite Web Sites

My Books

My MSDN Articles