September 15, 2011 at 12:46 pm
I am writing this to see if there is some kind of explanation for this. The problem is resolved, but I am unable to explain why. I presume the issue is with the try catch block inside the outer transaction but not 100% sure. I should also note I did not write the code, just tasked to fix the issue. Please feel free to ask question and for any clarification, I am here for information.
When executing a stored procedure in a transaction an error is returned. The error is only returned on the production clustered version, a separate nonclustered production server and the development nonclustered servers do not return the error. This stored procedure calls an extended stored procedure via linked server.
Details:
Local stored proc calls a remote extended stored procedure on sql 2000 sp4 machine.
When local proc call is inside transaction, an error is returned
OLE DB provider "SQLNCLI10" for linked server "CZAR_SERVER" returned message "No transaction is active.".
(1 row(s) affected)
(1 row(s) affected)
Msg 3930, Level 16, State 1, Line 22
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
When the same statement executed not in a transaction there are no errors and data is returned.
Development : 4 named instances, Sql 2008 sp1 cu5 – non clustered
Production : 2 instances on Sql 2008 sp1 cu5 active/active cluster
More information:
The external stored procedure call is in a try catch block.
Linked server settings on non clustered
@optname=N'collation compatible', @optvalue=N'true'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=null
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'
Linked server settings for clustered
Same as above except:
@optname=N'remote proc transaction promotion', @optvalue=N'false'
Resolution:
Change linked server setting for remote proc transaction promotion to false and issue cleared
September 15, 2011 at 2:01 pm
current settings are allow inbound and outbound .. is this incorrect?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply