December 2, 2008 at 11:11 am
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 (Developer Edition)
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
515162Cannot insert the value NULL into column '__$end_lsn', table 'CDCTest.cdc.dbo_tblCustomers_CT'; column does not allow nulls. INSERT fails.
22863161Failed 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....
December 2, 2008 at 3:04 pm
Can't offer any suggestions but
I'm using Enterprise Edition 10.0.1600.22 evaluation. I created a test table, enabled cdc and it worked. I checked the cdc.change_tables table and the has_drop_pending column is nullable.
December 3, 2008 at 7:37 am
Thanks for the test.
I'm starting to think it's something to do with CU2. I'm in the process of building a VM and I'm going to put SQL 2008 RTM without any CU and see if I'm running into the same problem.
thanks again
December 12, 2008 at 11:24 am
So I totally reinstalled SQL 2008 on a new VM
connected to it via SSMS remotely
enabled CDC and got the same problem :w00t: what the f*ck? LOL
BUT when I log into the server directly and enable CDC from there it works
so I thought there was something wrong with my management studio on my desktop... I uninstalled and reinstalled... same problem.
DNA (or anyone else out there)
Can you connect to a server using management studio from your desktop and enable CDC and see if you get the same problem (cdc.change_tables getting has_drop_pending column created as NOT NULL)
Thanks
So the use case is:
Connect from desktop to server - doesn't work
Connect directly to server - works
Thanks
December 15, 2008 at 8:48 am
December 15, 2008 at 1:21 pm
I just tried it and it works fine for me.
No difference if I run it on the server or from a remote client.
The column has_drop_pending in cdc.change_tables is nullable.
I'm using developer edition build 1600.
Can you post the ddl of your tblcustomer table? Maybe it's something in there.
[font="Verdana"]Markus Bohse[/font]
December 15, 2008 at 1:23 pm
Thanks Markus but I was able to get an answer from Microsoft on this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387050
If the following option is set when you run sp_cdc_enable_db for a database
SET ANSI_NULL_DFLT_OFF ON
columns that do not have the NULL/NOT NULL option explicitly set will be assigned the NOT NULL attribute. The column has_drop_pending does not have this option explicitly set when the table cdc.change_tables is created so it takes on the NOT NULL attribute. (This can be checked by examining the column information for the table cdc.change_tables after it is created by sp_cdc_enable_db.) When sp_cdc_enable_table is called to enable a source table, the value assigned to the column has_drop_pending is always NULL, so that the insert into the table will fail.
We acknowledge that not explicitly setting the NULL attribute for the has_drop_pending column of cdc.change_tables is a defect. The problem can be worked around by explicitly setting the option ANSI_NULL_DFLT_ON to ON prior to enabling Change Data Capture for the database so that the column attributes are correct and then resetting it back to the (presumed) desired setting for your environment.
SET ANSI_NULL_DFLT_ON OFF
exec sys.sp_cdc_enable_db
go
SET ANSI_NULL_DFLT_OFF ON
Posted by Microsoft on 12/12/2008 at 1:57 PM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply