Nested Transaction Issue

More
3 years 3 months ago #13869 by peters
peters created the topic: Nested Transaction Issue
Hi,

I am developing a SQL 2008 R2 SSIS package to transfer data from SQL Server 2008 R2 to Redshift within a transaction.

I am using the latest PGNP driver (v1.4.0.3392) and connecting to Redshift. The connection has the RetainSameConnection set to True.

[attachment=0:16v67gov]<!-- ia0 -->SSIS Control Flow.jpg<!-- ia0 -->[/attachment:16v67gov]

When running the package I am encountering the following issue:

[i:16v67gov]
Client SQL 2015/11/20 10:54:18.289 4CF57A66
ROLLBACK TO SAVEPOINT PGNP_NESTED_TRANS
Error
Abort failed: conn=0000000008136B90 ERR=7 'ERROR: SQL command "ROLLBACK TO SAVEPOINT PGNP_NESTED_TRANS" not supported.
'[/i:16v67gov]

Additional error message added for information:
[i:16v67gov]ERROR: opening multiple cursors from within the same client connection is not allowed.[/i:16v67gov]

Any help you can give is appreciated.
Attachments:

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago #13873 by Moderator
Moderator replied the topic: Re: Nested Transaction Issue
This looks like a defect. We are currently working on a fix. I will update this thread about the fix availability soon. Thank you!

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago #13874 by Moderator
Moderator replied the topic: Re: Nested Transaction Issue
Also, regarding "ERROR: opening multiple cursors from within the same client connection is not allowed", two workarounds could help:
1. Upgrade Redshift cluster to higher tear that allows multiple cursors. At least this was possible in the past, I did not check recently.
2. Disable cursor in the OLEDB provider by adding "CURSOR=OFF;" to the Extended Properties in the connection string.

Could you try the above workarounds?

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago #13875 by peters
peters replied the topic: Re: Nested Transaction Issue
Hi,

I will try the suggestions and let you know the results.

regards,
Peter

Please Log in or Create an account to join the conversation.

Time to create page: 0.118 seconds
Powered by Kunena Forum