Scott on Writing

Musings on technical 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:

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:
Protected by Clearscreen.SharpHIPEnter the code you see:
Comments   

Add To Your Reader

My Links

Archives

Post Categories

 

I am a Microsoft MVP for ASP.NET.
I am an ASPInsider.
<May 2008>
SMTWTFS
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

Comment Stats

DayTotal% of Total
Sunday 1866.8%
Monday 37913.9%
Tuesday 45316.7%
Wednesday 50418.5%
Thursday 53519.7%
Friday 49418.2%
Saturday 1666.1%
Total 2717100.0%

Hour1Total% of Total
12:00 AM 652.4%
1:00 AM 682.5%
2:00 AM 622.3%
3:00 AM 742.7%
4:00 AM 572.1%
5:00 AM 1033.8%
6:00 AM 1084.0%
7:00 AM 1585.8%
8:00 AM 1716.3%
9:00 AM 1475.4%
10:00 AM 1716.3%
11:00 AM 1816.7%
12:00 PM 1886.9%
1:00 PM 1696.2%
2:00 PM 1605.9%
3:00 PM 1324.9%
4:00 PM 1073.9%
5:00 PM 923.4%
6:00 PM 913.3%
7:00 PM 963.5%
8:00 PM 833.1%
9:00 PM 782.9%
10:00 PM 792.9%
11:00 PM 772.8%
Total 2717100.0%

Comments by Blog Entry Date/Time

Day Entry MadeAvg.Total
Sunday 5.54144
Monday 5.22339
Tuesday 4.28419
Wednesday 7.67637
Thursday 6.90607
Friday 5.48411
Saturday 5.33160
Total 5.842717

Hour1 Entry MadeAvg.Total
12:00 AM 5.0035
1:00 AM 1.002
5:00 AM 0.000
7:00 AM 7.0035
8:00 AM 5.35107
9:00 AM 6.32278
10:00 AM 6.47246
11:00 AM 4.41181
12:00 PM 6.88330
1:00 PM 3.00111
2:00 PM 5.41222
3:00 PM 8.64285
4:00 PM 4.0589
5:00 PM 5.92154
6:00 PM 4.52113
7:00 PM 9.67174
8:00 PM 9.80147
9:00 PM 5.05111
10:00 PM 5.4265
11:00 PM 4.5732
Total 5.842717

Learn More About Comment Stats
1 - All times GMT -8...


Blog Stats

Favorite Web Sites

My Books

My MSDN Articles