Visual Studio + Database Edition + CDC = Deploy Fail
- by Ben
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.
Pre-deployment Script
Tables
Indexes, keys, etc.
Procedures
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.
Comment out the sproc body
Deploy (CDC is created)
Uncomment sproc
Deploy
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!