Change Data Capture (cdc) System Tables aren't being created

bobkainebobkaine Posts: 6
edited October 31, 2013 9:01AM in Deployment Manager
We're using CDC for audit purposes.

When I create a new deployment package by right clicking the database in SSMS and select 'Publish [dbname] for Deployment', the package created doesn't include the CDC system tables.
2013-10-28 13:30:37 -04:00 DEBUG  Creating [cdc].[fn_cdc_get_all_changes_dbo_TchApplicationRole]
2013-10-28 13:30:37 -04:00 ERROR  Error while executing job: Create failed
Invalid object name 'cdc.dbo_TchApplicationRole_CT'.

As you can see above, it's attempting to create the CDC function, but since the function references the CDC tables that weren't created it fails.

How do I "fix" this?

Comments

  • Hi,

    The problem here is that our SQL Compare engine (which underpins DM and several other tools we provide) doesn't currently support handling System level objects.

    Because of this, the relevant objects will need to be created by some other method before you deploy.

    If you don't have many target DB's to deploy to, and they are likely to stay in place once initially configured, then it's perhaps easiest to manually create the system objects CDC requires.

    If you're likely to deploy to more systems, then it would be more useful to include a "PreDeploy.ps1" powershell script in your package that would check for, and create if not present, the required objects by executing a query via SQLCMD.
    Systems Software Engineer

    Redgate Software

  • Thanks for the quick response.

    I was kind of suspecting something similar.

    I'll determine which approach you suggested will work the best and proceed from there.

    Thank you.
Sign In or Register to comment.