Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


3
0
Sign in
to vote
Type: Bug
ID: 780107
Opened: 2/27/2013 6:00:09 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
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)
Sign in to post a workaround.