Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
LChesnut
Contributor
Contributor

Stream component terminated ... uses a temp table.

We are seeing Replicate tasks that worked before now begin to fail with messages that reference use of a "temp table".

Why is this suddenly starting to happen, what is causing it, and how do we correct this problem?

Example error message (received on numerous tables):

Stream component 'st_5_Az ITS Facets' terminated

Stream component failed at subtask 5, component st_5_Az ITS Facets

Error executing data handler

Get statement failed in preparing statement for full load

Failed to prepare statement 'INSERT INTO [dbo].[CMC_NWNW_NETWORK]([NWNW_ID],[MCAR_AREA_ID],[NWNW_NAME],[NWNW_ADDR1],[NWNW_ADDR2],[NWNW_ADDR3],[NWNW_CITY],[NWNW_STATE],[NWNW_ZIP],[NWNW_COUNTY],[NWNW_CTRY_CD],[NWNW_PHONE],[NWNW_PHONE_EXT],[NWNW_FAX],[NWNW_FAX_EXT],[NWNW_EMAIL],[NWNW_MCTR_TYPE],[NWNW_NAME_XLOW],[NWNW_CITY_XLOW],[NWNW_LOCK_TOKEN],[ATXR_SOURCE_ID]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'

RetCode: SQL_ERROR  SqlState: 42000 NativeError: 11526 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The metadata could not be determined because statement 'SELECT

    @lxPzId     = SES0_PZAP_ID,

    @lxUsFnc    = SES0_USER_FUNC,

    @lnTxnId    = SES0_T' in procedure 'CERSP_TXN1_INSERT_TXN1_ID' uses a temp table. Line: 1 Column: -1

Labels (1)
4 Replies
john_wang
Support
Support

Hello @LChesnut ,

It's hard to tell with the limited information. I'd like suggest you to open a support case with TARGET_APPLY set to Verbose, reproduce the error and attach:

1. the Diagnostics Package

2. The source and target table creation DDL especially if there is trigger defined in the target table

It's better to let us know how to reproduce the behavior in our local environment.

thank you,

John. 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
LChesnut
Contributor
Contributor
Author

Hello John Wang - 

Can you tell us whether this is a problem the Support team has seen before?  Is it a common problem?  Is it a common problem associated with SQL Server 2019 or with a specific Replicate release?

Or is it a problem no one has reported on Replicate before?  Is there no instance of anyone encountering an ODBC Temp Table error while replicating data to SQL Server?

This is important information that you have access to and which would be useful to us, since it is possible this is a problem with the SQL Server 2019 installation, not Replicate.  If Support has never seen this problem before, then our analysis shifts to the SQL Server side, away from Replicate.  

Of course we are aware we can open a support ticket.  Of course we are aware we can put a trace on and collect diagnostics.  As to reproducing the behavior in your local environment, we understand how helpful that would be for your own analysis, but it is unlikely you would achieve that without extensive investment of time and resources.

We would prefer first to establish whether this is even a Replicate problem.  If you can provide any of the above background information, that will provide us with a useful indication one way or the other.

Thank you.

john_wang
Support
Support

Hello @LChesnut ,

I do not think it's a common issue, and I'm afraid it's too earlier to conclude SQL Server 2019 issue or Replicate issue at present, that's why we need Diag Package to learn more.

If you think it's inconvenient to collect Diag Package, I'd love to help if you can let me know how to reproduce the issue eg what's the source DB and the source/target table creation DDL.

And I'd like suggest to check:

1- how about if you use "Transactional Apply Mode";

2- if there is trigger defined in the target table.

However the Diagnostics Package will speed up our analysis.

 

thank you,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
PGN
Creator
Creator

Did you get this resolved?  I see you you're using Facets as your source, as am I.  We're trying to snapshot CMU tables and getting this error.  We've never done any replication on these tables (snapshot or transactional).  Transactional is not doable since they are truncated daily, however I'm not seeing any reason that a snapshot shouldn't work.