CDC code

[Pramod Jamadagni]

Hi guys,

The standard edition of sql server doesnot support CDC. If your client is using standard edition of SQL server and wondering how to implement CDC. Then here is the code for it. This is very easy to install and works similar to that of CDC feature of SQL server. Only constraint is the table which is being monitored has to have primary key J.

Execution order:

1.       Implement SplitArray.sql

2.       sp_StandardCDC_enable_db.sql

3.       sp_StandardCDC_enable_table.sql

4.       sp_StandardCDC_disable_table.sql

5.       sp_StandardCDC_disable_db.sql

 

In my case i had a table called Faculty which didn’t had PK. So this is how i make this happened. Initial 2 steps are to create primary key. You can skip them if there is primary key

ALTER TABLE dbo.FACULTY ADD FID INT IDENTITY(1,1);

GO

ALTER TABLE dbo.FACULTY ADD CONSTRAINT pk_acl_user_id PRIMARY KEY(FID);

GO

EXEC sp_StandardCDC_enable_db;

GO

EXEC sp_StandardCDC_enable_table

@sourceschema = N'dbo',

@sourcename   = N'FACULTY',

@rolename     = NULL,

@CapturedColumnList = NULL,

@FileGroupName = 'primary',

@CaptureUser=''

GO

 

for more details look for http://standardeditioncdc.codeplex.com/. I’ve modified few things to make this work.

 

Hope this helps !  Happy coding ;)

 

Thanks,

Pramod.