SSDT With CDC - [under change data capture control and cannot be modified]

1
votes

Thanks in advance for reading this; this is with VS 2015 and SQL Server 2012.

Have a database that's cdc enabled and did any extract to get the dacpac. Created a database project from the dacpac and was able to Build the database project. Didn't make any changes other than deleting the login IDs under the 'Security' folder. When trying to publish to a target database we have these error msg:

[dbo].[SomeTable] is under change data capture control and cannot be modified

Looked around and tried a few things.

Based on this: https://social.msdn.microsoft.com/Forums/en-US/cc746b7b-6d7d-4701-aa8d-3ae9dde00e06/sql-2008-vstsdb-deployment-with-cdc-enabled?forum=vstsdb

  • a) unchecked VerifyDeployment
  • b) created pre-deployment script to disable cdc: EXEC sp_cdc_disable_db
  • c) created post-deployment script to enable cdc: EXEC sp_cdc_enable_db

Able to build and when try to publish would get the same error message.

At wits end and if you can please help would really, truly, deeply appreciated.

TIA!

1
sql-serversql-server-data-toolscdc
Pre-Deploy script wouldn't help because you still do the DB compare before that script runs. You'd need to make it a pre-SqlPackage script. If you're using some sort of batch/script to call SqlPackage, you will want to call your disable part first, then run sql package, then run the enable script. (which could probably be part of the post-deploy process and still work) - Peter Schott
Thank you @PeterSchott. I've been reading so much that I may be mixing things up. So, just to be sure if we disable cdc then the cdc tables won't get truncated, right? I think this happens when you're altering the cdc columns. Appreciate your replies! - scv
I need to double/triple check that. We were investigating that and stopped because we had more pressing concerns, but I do know that you need to run any major schema changes like that prior to running the SSDT/SQLPackage part. Even though you put something in a pre-deploy, the main body of the script expects that the DB won't have changed at that point so can blow up if you modify the schema in the pre-deploy script. Having a script that runs prior to everything else is the key. I'd definitely check some other sources on what happens when you disable/re-enable CDC. - Peter Schott

1 Answers

2
votes

Try creating a publish profile (right-click project and select publish).

Then click 'Advanced' and you should get a list of properties.

Clear / uncheck the box that says 'Do not alter Change Data Capture objects'

Do not alter Change Data Capture objects