There doesn’t seem to be a whole lot about it online, but out of the box, ASP.NET’s included tool for enabling Polling-Based SQL Cache Dependencies doesn’t work with SQL 2005’s schema names.  However, a few manually steps can get them to interoperate.

The problem is that a stored procedure that aspnet_regsql.exe calls, AspNet_SqlCacheRegisterTableStoredProcedure always precedes its trigger names with a dbo., and SQL 2005 believes that to be a schema name.  To enable SQL Cache Dependencies on a table, you can either add an INSERT, UPDATE, DELETE trigger that calls AspNet_SqlCacheUpdateChangeIdStoredProcedure  N’TableName’ yourself:

CREATE TRIGGER [dbo.SchemaName.TableName_AspNet_SqlCacheNotification_Trigger] ON [SchemaName].[TableName]

FOR INSERT, UPDATE, DELETE AS

BEGIN

SET NOCOUNT ON

EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'SchemaName.TableName'

END

…or hack AspNet_SqlCacheRegisterTableStoredProcedure, changing:

SET @fullTriggerName = 'dbo.[' + @triggerName + ']'

…to:

SET @fullTriggerName = '[dbo.' + @triggerName + ']'

…and then running aspnet_regsql.exe the normal way:

aspnet_regsql -U User -S Server -d Database -et -t SchemaName.TableName

In either case, be sure to confirm that ASP.NET recognizes your table as enabled for SQL cache dependencies:

aspnet_regsql -U User -S Server -d Database –lt

Note that even if you modify AspNet_SqlCacheUnRegisterTableStoredProcedure in a similar way, running:

aspnet_regsql -U User -S Server -d Database -dt -t SchemaName.TableName

…won’t delete the table’s trigger—however, it will get rid of the associated AspNet_SqlCacheTablesForChangeNotification row.  Keeping in mind that the trigger inherits its table’s schema name, dropping it manually only requires:

DROP TRIGGER [SchemaName]. [dbo.SchemaName.TableName_AspNet_SqlCacheNotification_Trigger]