Change Data Capture (not creating cdc tables properly?)

Question

So I've enabled Change Data Capture

EXEC sys.sp_cdc_enable_db

and then I try to enable Change data capture for a table

EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', @source_name = 'tblCustomers', @role_name = null, @supports_net_changes = 1

and I get this error

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607
Could not update the metadata that indicates table [dbo].[tblCustomers] is enabled for Change Data Capture. The failure occurred when executing the command 'insert into [cdc].[change_tables]'. The error returned was 515: 'Cannot insert the value NULL into column 'has_drop_pending', table 'CDCTest.cdc.change_tables'; column does not allow nulls. INSERT fails.'. Use the action and error to determine the cause of the failure and resubmit the request.

I'm using SQL 2008 RTM 10.0.1779.0 (Enterprise Edition) Cumulative Update 2

 

Any help would be GREAT and I'm hoping for more help then altering the column to allow for nulls hehehe


EDIT: So i made the column nullable LOL and that worked for allowing the table to be enabled for CDC.... BUT then when I make a change to a column using an update statement I go and query the CDC table expecting to see my changes:

Select * from cdc.dbo_tblCustomers_CT

and I get nothing back

so then I query the error table and I get this:

Select * from sys.dm_cdc_errors 

515        16        2        Cannot insert the value NULL into column '__$end_lsn', table 'CDCTest.cdc.dbo_tblCustomers_CT'; column does not allow nulls. INSERT fails.

22863        16        1        Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.


same type of NULL issues....

Answer 1

Hi ,

Go through this document step by step ,you can configure easily

https://skydrive.live.com/view.aspx?cid=6AEBF8592FF08FE9&resid=6AEBF8592FF08FE9%21105

---

Sri.Tummala

Answer 2

make sure agen is running

---

Sri.Tummala