SQL 2012 temp table in sp_cdc_ddl_event_internal causes SSIS OLEDB Source to error - by brent.souza

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 780107 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/27/2013 6:00:09 AM
Access Restriction Public


For an ETL process, we have user tables created that are used in place of temporary tables.  CDC is enabled on the database on several tables.  None of the work tables are marked for capture by CDC. The SSIS package that's failing has a data flow task with an OLEDB Source that executes a stored procedure. The stored procedure drops and recreates a work table, which causes the ddl database trigger to fire and call sp_cdc_ddl_event_internal.  This system proc uses a temp table, which is causing the SSIS package to error with the following message:

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The metadata could not be determined because statement 'insert #tran_altertable ( DDLsubtype, TableOwner, TableName, ColumnName, ColumnAttr ) 
                        exec sys.' in procedure 'sp_cdc_ddl_event_internal' uses a temp table.".

This seems to be an issue with 2012 as we have the same process running on a 2008 R2 instance without the error.

I attempted to work around the problem by altering the trigger to check if the table was in cdc.change_tables before calling the ddl proc, but this doesn't appear to work for SSIS. 
Sign in to post a comment.
Posted by Basil Worrall on 1/28/2017 at 2:53 AM
Hi, I got exactly this same error reported on our SQL Server 2012 SP 3 server starting yesterday afternoon, when a SSIS package failed to execute after I had enabled CDC on one of the databases mounted on the server. I will be able to share the SSIS package with you, but would prefer to use a less public channel than an attachment to this bug, as the package may contain proprietary information.
Posted by Microsoft on 6/25/2013 at 1:03 AM
Hi, since we haven't heard any feedbacks from you, this issue is going to be closed as "Not Repro". If you can please share additional information about the problem and your environment, we will gladly revisit it.
Posted by Microsoft on 5/10/2013 at 2:06 AM
Hi, we have not been able to reproduce this problem behavior. Is it possible for you to please share a sample package that reproduces this issue?
Posted by Microsoft on 4/11/2013 at 10:24 AM
Hello Brent. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)