SQL Cache Dependency with custom schema RSS

• • Reply
  

  travis.wade

  Member

  2 Points

  7 Posts

  SQL Cache Dependency with custom schema

  Apr 17, 2008 03:26 PM|LINK

  Hi
  I have created a datatbase called "testDB" and schema called "MySchema". I have
  created a table "MySchema.table1" in the database. I am trying to create sql
  cache dependancy on table using aspnet_regsql utitlity.
  
  aspnet_regsql -S localhost -U someUser -P somePW -ed  -d testDB -et -t
  MySchema.table1
  
  But it is throwing an error.
  
  Enabling the table for SQL cache dependency.
  
  An error has happened.  Details of the exception:
  Cannot create trigger
  'dbo.MySchema.Table1_AspNet_SqlCacheNotification_Trigger'
  as its schema is different from the schema of the target table or view.
  
  Failed during cache dependency registration.
  
  Please make sure the database name and the table name are valid. Table names
  must conform to the format of regular identifiers in SQL.
  
  The failing SQL command is:
  dbo.AspNet_SqlCacheRegisterTableStoredProcedure
  
  Without the schema name is still fails.

   Is there a way to create a SQL cache dependecy using a custom (non-dbo) schema?

  Thanks

  asp.net 2.0 Cache dependency SQL Server 2005

• • Reply
  

  vivekjdesai

  Member

  506 Points

  68 Posts

  Re: SQL Cache Dependency with custom schema

  Apr 18, 2008 12:03 PM|LINK

  Yes it is possible to work with custom schemas with some tweaking in ASP.NET notification stored procedures.

   If you look up the Stored Procedures section of your database, you will find a stored procedure - [AspNet_SqlCacheRegisterTableStoredProcedure]

  This is the procedure that 'enables' change notification on the table you specify (in simpler terms, it just creates a trigger on the table you specify)

  This procedure has the dbo schema hardcoded in it (dont know why, may be some MVPs over here can tell).

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

  I just tweaked this portion of the procedure to extract the Schema Name specified by the user and use that to create the trigger rather than the hardcoded 'dbo'. The modified stored proc is below. You can alter the procedure on your database instance and you are all set. Ofcourse, I find similar hardcoding of 'dbo' in the procedure AspNet_SqlCacheUnRegisterTableStoredProcedure, which is responsible for disabling notifications on your table, so you will need to tweak it a bit to make unregistration work.

  -- ===========================================================================================

  USE [TestDB]
  GO
  /****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure]    Script Date: 04/18/2008 16:05:31 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER 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)
  		
  
  --Vivek Start --Added A
   DECLARE @schemaName NVARCHAR(3000)
  		IF(CHARINDEX('.',@tableName) <> 0)
  		BEGIN
  			SET @schemaName = SUBSTRING(@tableName,0,CHARINDEX('.',@tableName))
  			SET @tableName =  SUBSTRING(@tableName,CHARINDEX('.',@tableName) + 1,LEN(@tableName) - CHARINDEX('.',@tableName))
  		END
  --Vivek END A
  		
  
  
  
           /* Create the trigger name */
           SET @triggerName = REPLACE(@tableName, '[', '__o__')
           SET @triggerName = REPLACE(@triggerName, ']', '__c__')
           SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger'
           --Vivek Commented --SET @fullTriggerName = 'dbo[' + @triggerName + ']'
   IF(@schemaName IS NOT NULL)
  			SET @fullTriggerName ='[' + @schemaName + '].[' + @triggerName + ']'
  		 ELSE
  			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 = '[' + @schemaName + '].[' + @tableName + ']'
  
  
  		 /* First make sure the table exists */
           --Vivek Commented --IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL
  			               --BEGIN
                             --  RAISERROR ('00000001', 16, 1)
                             --  RETURN
                             --END
  --Vivek Start --Added B
   IF(@schemaName IS NULL)
  			BEGIN
  				IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL
  				BEGIN
                     RAISERROR ('00000001', 16, 1)
                     RETURN
                  END
  			END
  		 ELSE
  			BEGIN
  				IF (SELECT OBJECT_ID(@schemaName + '.' + @tableName, 'U')) IS NULL
  				BEGIN
  					RAISERROR ('00000001', 16, 1)
  					RETURN
  				END
  			END
  		 
  
  --Vivek End B
  
  
  
           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

   --==========================================================================================

   

  Vivek Desai
  • Marked as answer by travis.wade on Apr 18, 2008 04:14 PM
• • Reply
  

  travis.wade

  Member

  2 Points

  7 Posts

  Re: SQL Cache Dependency with custom schema

  Apr 18, 2008 04:14 PM|LINK

  Thank You vivekjdesai!

  I made the modification and now regeistration completed.

  
  Thanks for the help!