Home Dashboard Directory Help
Search

sys.sp_cdc_disable_table fails when variable passed to @source_schema by Daniel Adeniji


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 811748
Opened: 12/16/2013 8:27:42 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description


When sys.sp_cdc_disable_table is invoked and a variable value is passed to @source_schema, it fails with the error message indicated below:

______________________________________________________________________________________________________________________________________

Msg 22931, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 58

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.
______________________________________________________________________________________________________________________________________

Details
Sign in to post a comment.
Posted by Daniel Adeniji on 12/21/2013 at 10:01 AM
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

Please mark as closed!
Posted by Daniel Adeniji on 12/21/2013 at 9:04 AM
To ease communication, I posted some of my findings in a publicly available web posting.

The link is http://danieladeniji.wordpress.com/2013/12/21/technical-microsoft-sql-server-change-data-capture-error-sys-sp_cdc_disable_table-fails-when-variable-passed-to-source_schema/

Thanks,
Daniel
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
ChangeDataExchange__Customer.sql 12/16/2013 1 KB
dbo.customer.sql 12/16/2013 726 bytes
CDC_Enable__DBLAB.sql 12/16/2013 753 bytes