SQL Server, Remote Stored Procedure, and DTC Transactions

Question

Our organization has a lot of its essential data in a mainframe Adabas database. We have ODBC access to this data and from C# have queried/updated it successfully using ODBC/Natural "stored procedures".

What we'd like to be able to do now is to query a mainframe table from within SQL Server 2005 stored procs, dump the results into a table variable, massage it, and join the result with native SQL data as a result set.

The execution of the Natural proc from SQL works fine when we're just selecting it; however, when we insert the result into a table variable SQL seems to be starting a distributed transaction that in turn seems to be wreaking havoc with our connections.

Given that we're not performing updates, is it possible to turn off this DTC-escalation behavior?

Any tips on getting DTC set up properly to talk to DataDirect's (formerly Neon Systems) Shadow ODBC driver?

Answer 1

Check out SET REMOTE_PROC_TRANSACTIONS OFF which should disable it. Or sp_serveroption to configure the linked server generally, not per batch.

Because you are writing on the MS SQL side, you start a transaction. By default, it escalates whether it needs to or not. Even though the table variable does not particapate in the transaction.

I've had similar issues before where the MS SQL side behaves differently based on if MS SQL writes, in a stored proc and other stuff. The most reliable way I found was to use dynamic SQL calls to my Sybase linked server...

Answer 2

The following code sets the "Enable Promotion of Distributed Transactions" for linked servers:

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

This will allow you to insert the results of a linked server stored procedure call into a table variable.