Visual Studio + Database Edition + CDC = Deploy Fail

Question

Hi All,

I've got a database using change data capture (CDC) that is created from a Visual Studio database project (GDR2).

My problem is that I have a stored procedure that is analyzing the CDC information and then returning data. How is that a problem you ask? Well, the order of operation is as follows.

1. Pre-deployment Script
2. Tables
3. Indexes, keys, etc.
4. Procedures
5. Post-deployment Script

Inside the post-deployment script is where I enable CDC. Here-in lies the problem. The procedure that is acting on the CDC tables is bombing because they don't exist yet! I've tried to put the call to sys.sp_cdc_enable_table in the script that creates the table, but it doesn't like that.

Error 102 TSD03070: This statement is not recognized in this context. C:...\Schema Objects\Schemas\dbo\Tables\Foo.table.sql 20 1 Foo

• Is there a better/built-in way to enable CDC such that it's references are available when the stored procedures are created?
• Is there a way to run a script after tables are created but before other objects are created?
• How about a way to create the procedure dependencies be damned?
• Or maybe I'm just doing things that shouldn't be done?!?!

Now, I have a work around.

1. Comment out the sproc body
2. Deploy (CDC is created)
3. Uncomment sproc
4. Deploy
5. Everything is great until the next time I update a CDC tracked table. Then I need to comment out the 'offending' procedure.

Thanks for reading my question and thanks for your help!

Answer 1

I'm not really familiar with doing this thru a VS DB project, but I can at least attempt to offer an alternate workaround. It's only half an answer but could you DROP rather than ALTER the proc at step 1? At least that way you wouldn't have to monkey with the contents of the proc. In the "before we had diff for db" days, I'd usually drop all non-table bits before altering any tables.

I know that's not getting to the root cause, but may be at least an easier/clearer work around. If nothing else, you would know whether the proc is there or not, preventing you from thinking all is cool but your proc body is missing, hence it's not doing anything.