Technical: Microsoft – SQL Server – Change Data Capture – Error – sys.sp_cdc_disable_table fails when variable passed as @source_schema

Technical: Microsoft – SQL Server – Change Data Capture – Error – sys.sp_cdc_disable_table fails when variable passed as @source_schema

Introduction

An interesting error returned when we pass in a variable, as opposed, to a hard-coded value to the sys.sp_cdc_disable_table statement.

Error Message

Source table ‘objectSchema.<objectName>’ does not exist in the current database. Ensure that the correct database context is set. Specify a valid schema and table name for the database.

Create Database

If Database DBLAB, does not exist please create it

        id db_id('DBLab') is null
        begin

            exec ('create database [DBLab]');
            exec ( 'alter database [DBLAB] set recovery simple');
        end
        go

Enable Database for CDC

Enable Database for CDC


use [<database_name>]
go
if not exists
(

      SELECT *
      FROM   sys.databases
      WHERE  is_cdc_enabled = 1
      and name= db_name()

)
begin

     print 'Enable CDC ' + db_name() + ' ...'

         EXEC sys.sp_cdc_enable_db

     print 'Enable CDC ' + db_name() + ' ...'

end

Create Object (Table)

Create Object



set nocount on
go

use [DBLAB]
go

USE [DBLAB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

/*
	DROP TABLE [dbo].[customer];
*/

set noexec off
go

if object_id('[dbo].[customer]') is not null
begin

	set noexec on

end
go

CREATE TABLE [dbo].[customer]
(

	  [CustomerID]   [bigint] NOT NULL

	, [CustomerName] [nvarchar](255) NOT NULL

	, [inceptionDate] [datetime] NOT NULL

	, [expiryDate] [datetime] NULL

	, [active] bit not null 
		constraint defaultActive default 1

	, [dateAdded]    [datetime] NOT NULL
		 constraint defaultDateAdded default getutcdate()

	, [addedBy]      [sysname]  NOT NULL 
		constraint defaultAddedBy default SYSTEM_USER

	 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
		(
			[CustomerID] ASC
		)WITH 
		(
			  PAD_INDEX  = OFF
			, STATISTICS_NORECOMPUTE  = OFF
			, IGNORE_DUP_KEY = OFF
			, ALLOW_ROW_LOCKS  = ON
			, ALLOW_PAGE_LOCKS  = ON
		) 
		ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

set noexec off
go

Enable Object for CDC

Enable Object for CDC



set nocount on
go

use [DBLAB]
go

declare @objectSchema   sysname
declare @objectName     sysname
declare @objectNameFull sysname
declare @objectID       int
declare @addCDC         bit

set @objectSchema = 'dbo'
set @objectName = 'customer'
set @objectNameFull = quoteName(@objectSchema) 
                                 + '.'
                                 + quoteName(@objectName)
set @objectID = object_id(@ObjectNameFull)
set @addCDC = 1

if (@addCDC = 1)
begin

	if not exists
	(

		SELECT *
		FROM   sys.tables 
		WHERE  is_tracked_by_cdc = 1
		and    schema_id = schema_id(@objectSchema)
		and    name= @objectName

	)
	begin

	   print 'CDC - Enable....' 

	  EXEC sys.sp_cdc_enable_table
		  @source_schema = @objectSchema
		, @source_name   = @objectName
		, @role_name     = null 

	     select 
		  object_name(object_id) as objectName
		, capture_instance
		, supports_net_changes
		, index_name
		, filegroup_name
		, create_date
	     from   [cdc].[change_tables]
	     where  source_object_id =  @objectID

	     print 'CDC - Enabled' 

	end

end --addCDC =1

exec sys.sp_cdc_help_change_data_capture

Disable CDC on Object

Disable CDC on Object



   set nocount on
   go

   use [DBLAB]
   go

    declare @objectSchema sysname
    declare @objectName sysname
    declare @objectNameFull sysname
    declare @objectID int
    declare @capture_instance sysname
    declare @hardcoded bit

    set @hardcoded = 0
    set @objectSchema = 'dbo' 
    set @objectName = 'customer'
    set @objectNameFull = quotename(@objectSchema)
                             + '.' 
                             + quotename(@objectName)
    set @objectID = object_id(@objectNameFull)

    print 'objectSchema ' + isNull(@objectSchema, '')
    print 'objectName ' + isNull(@objectName, '')
    print 'objectNameFull ' + isNull(@objectNameFull, '')
    print 'objectID ' + cast(isNull(@objectID, '') as sysname)

	if exists
	(

		SELECT *
		FROM   sys.tables 
		WHERE  is_tracked_by_cdc = 1
		and    schema_id = schema_id(@objectSchema)
		and    name= @objectName

	)
	begin

		select *
		from [cdc].[change_tables]
		where source_object_id = @objectID

		select 
			@capture_instance = capture_instance
		from [cdc].[change_tables]
		where source_object_id = @objectID

		if (@hardcoded = 0)
		begin

			--fails
			EXEC sys.sp_cdc_disable_table
				 @source_schema = objectSchema
				 --@source_schema = 'dbo'
			         , @source_name = @objectName
				 , @capture_instance = @capture_instance

		end
		else
		begin

			--good
			EXEC sys.sp_cdc_disable_table

				 --@source_schema = objectSchema
				 @source_schema = 'dbo'

				 , @source_name = @objectName

				 , @capture_instance = @capture_instance

		end

	end

go

Explanation:

  • When the local variable @hardcoded is set to 1, CDC is successfully dropped on the Object
  • On the other hand, when @hardcoded is set to 0, we get an error message
  • The exact error message being
             
    Msg 22931, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 59
    Source table 'objectSchema.customer' does not exist in the current 
    database. Ensure that the correct database context is set. Specify a 
    valid schema and table name for the database.
             

It remains unclear why the error message affirmatively refers to objectSchema.customer.

Where did objectSchema arise or come from.

Conclusion

The error seems to impact the following versions of MS SQL Server:

  • v2012 (tested against Enterprise Edition)
  • v2008/R2 (tested against Developer Edition)

Addendum

Addendum – 2013.12.21

I am so sorry. I found the problem:

Original Code:



if (@hardcoded = 0)
begin

    --fails
    EXEC sys.sp_cdc_disable_table

        @source_schema = objectSchema
       --@source_schema = 'dbo'

         , @source_name = @objectName

         , @capture_instance = @capture_instance

end
else
begin

      --good
      EXEC sys.sp_cdc_disable_table

         --@source_schema = objectSchema
         @source_schema = 'dbo'

         , @source_name = @objectName

         , @capture_instance = @capture_instance

end

Revised Code:



if (@hardcoded = 0)
begin

       --fails
       EXEC sys.sp_cdc_disable_table

                @source_schema = @objectSchema
              --@source_schema = 'dbo'

              , @source_name = @objectName

              , @capture_instance = @capture_instance

end
else
begin

         --good
         EXEC sys.sp_cdc_disable_table

                 --@source_schema = objectSchema
                 @source_schema = 'dbo'

                , @source_name = @objectName

                , @capture_instance = @capture_instance

end

The problem being the missing @ that should be in front of objectSchema, for it to be recognized as a variable.

The fact that we do not have single quotes around it, is not enough to make it a variable.

Totally, my BAD.

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s