SQL 2012 temp table in sp_cdc_ddl_event_internal causes SSIS OLEDB Source to error

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

Active

Type:	Bug
ID:	780107
Opened:	2/27/2013 6:00:09 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Windows Server 2008 R2 Enterprise

US English

1. Enable CDC on a database.
2. Create a stored proc that drops a table then returns some information.
3. Create an SSIS package with a data flow task.
4. Create an OLEDB Source that executes the stored procedure
5. Run the package.

Package errors because of a temp table in the system proc mentioned in the description

Since the table that is being dropped is not tracked by CDC, one would expect that any cdc related system procedures would not execute.

X64

0 attachments

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)