I was trying out the SQL cache dependency feature that Asp.net 2.0 provided. I got this error after I configure the application properly with this config section:
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add connectionStringName="p_dbivt001ConnectionString" name ="p_dbivt001ConnectionString" pollTime="1000"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
After a further reading, I realized that I haven't run the aspnet_regsql command line tool to enable the database for dependency check:
aspnet_regsql -S servername -U username -d DatabaseName -ed -et -t TableName
Microsoft document says you can use either aspnet_regsql command line tool or SqlCacheDependencyAdmin.EnableNotifications to programmatically turn on the SQL dependency. I didn't success with the later though.
This tool will create the infrastructure to record any changes on the table you specified. There will be a trigger added that table you wish to depend on. The trigger will call a stored procedure AspNet_SqlCacheUpdateChangeIdStoredProcedure(created by the tool) to record the changes you made on a table. Another worker procedure AspNet_SqlCachePollingStoredProcedure will be called from asp.net application to poll those changes. The pollTime attribute specifies the interval in milliseconds with minimum 500.
One thing bothers me is the triggers added by the aspnet_regsql command line tool don't have any exception handling structure by default. That means your changes to the host tables will be roll back if the trigger or any processes underneath for SQL dependency failed. Sometimes, this is not what we want, isn't it? Fortunately with SQL server 2K5, we can add a try-catch block and force it to commit in catch block, here is the modified trigger:
ALTER TRIGGER [dbo].[dbo.BM_UDFLookupDataSetValue_AspNet_SqlCacheNotification_Trigger] ON [dbo].[BM_UDFLookupDataSetValue]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
BEGIN TRY
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'dbo.BM_UDFLookupDataSetValue'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END CATCH;
END