August 23, 2011 at 5:55 pm
Hi
So I was having a problem trying to rebuild indexes on CDC enabled tables and I decided to implement a couple of steps in my overnight optimisation job to disable the cdc on the relevant tables, rebuild the indexes then enable them again.
I maintain all of my optimisation sprocs etc in a separate 'Maintenance' DB, so I created sprocs in 'Maintenance', which made calls to the sys.sp_cdc_enable_table and sys.sp_cdc_disable_table system sprocs except using a full 3-part name, to specify the database where the CDC is enabled, lets call it 'MyCDCDB'. So nested in my sproc I have:
EXEC MyCDCDB.sys.sp_cdc_enable_table
@source_schema = @SourceSchema ,
@source_name = @TableName,
@role_name = @SourceSchema.
However, despite this, I get an error:
The database 'Maintenance' is not enabled for Change Data Capture.
It seems the scope of the sproc, sp_cdc_enable_table, is still within the database where the outer stored procedure is called, despite the nested sproc being called within a specific database. I'm guessing this is a scope issue that has always existed and, for some reason, I've never come across it until now???!
Anyway, apart from creating my maintenance sprocs within the CDC enabled DB itself - does anyone have any thoughts on how to get the cdc sprocs executing in the correct context?
Thanks
August 24, 2011 at 12:44 am
Try using Exec ('DBName.schemaname.spname') At Servername
Regards
Deepak.A
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply