SQL Server, Remote Stored Procedure, and DTC Transactions

Posted by marc on Stack Overflow See other posts from Stack Overflow or by marc
Published on 2008-09-22T16:22:45Z Indexed on 2010/04/24 20:23 UTC
Read the original article Hit count: 280

Filed under:

sql-server

|

msdtc

|

sql

|

adabas

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?

© Stack Overflow or respective owner

Related posts about sql-server

SQL SERVER – Beginning SQL Server: One Step at a Time – SQL Server Magazine

as seen on SQL Authority - Search for 'SQL Authority'
I am glad to announce that along with SQLAuthority.com, I will be blogging on the prominent site of SQL Server Magazine. My very first blog post there is already live; read here: Beginning SQL Server: One Step at a Time. My association with SQL Server Magazine has been quite long, I have written nearly… >>> More
How to install SQL Server 2005 Configuration Manager without installing SQL Server Management Studio

as seen on Server Fault - Search for 'Server Fault'
Hi, I need to configure SQL Server aliases on a public-facing production server. To do that, I need to install SQL Server Configuration Manager. I was not able to find a standalone installer for that, so I am having to install SQL Server 2005 Client Components. This approach is not ideal as we don't… >>> More
[MAJ] SQL Server 2005 Express Edition - La version gratuite de SQL Server 2005

as seen on ASP-PHP.net - Search for 'ASP-PHP.net'
Modification technique pour le site de l'article. >>> More
How to create SQL Server Express DB from SQL Server DB

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a SQL Server 2008 DB. I want to extract SOME tables (and associated schema, constraints, indexes, etc) and create a SQL Server Express DB. It isn't a sync of the target, we stomp on it. We ONLY need to do this in the file system (not across the wire). We are not fond of the synchronization… >>> More
sql server 2000 error, error trying to connect to sql server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
i am connecting to sql server 2000 on a remote computer with a dotnet application, but when i try to open the connection it gives the following error: When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections What… >>> More

Related posts about msdtc

MSDTC - Communication with the underlying transaction manager has failed (Firewall open, MSDTC netwo

as seen on Server Fault - Search for 'Server Fault'
I'm having problems with my ASP.NET web forms system. It worked on our test server but now we are putting it live one of the servers is within a DMZ and the SQL server is outside of that (on our network still though - although a different subnet) I have open up the firewall completely between these… >>> More
MSDTC - Communication with the underlying transaction manager has failed (Firewall open, MSDTC network access on)

as seen on Server Fault - Search for 'Server Fault'
I'm having problems with my ASP.NET web forms system. It worked on our test server but now we are putting it live one of the servers is within a DMZ and the SQL server is outside of that (on our network still though - although a different subnet) I have open up the firewall completely between these… >>> More
MSDTC - Communication with the underlying transaction manager has failed (Firewall open, MSDTC netwo

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm having problems with my ASP.NET web forms system. It worked on our test server but now we are putting it live one of the servers is within a DMZ and the SQL server is outside of that (on our network still though - although a different subnet) I have open up the firewall completely between these… >>> More
Do MSDTC and disaster recovery go together?

as seen on Stack Overflow - Search for 'Stack Overflow'
Our application writes to multiple Sql Server databases within a distributed transaction. The Ops guys are saying that this messes up their disaster recovery plan because while the transactions on the live tables may commit at the same time, the log shipping on the separate databases happen at slightly… >>> More
confusion about transactions and msdtc

as seen on Stack Overflow - Search for 'Stack Overflow'
I have some basic confusion about how transactions and msdtc work together. I have a basic server/client winforms app. The app uses transactionscope to encapsulate several sql commands that are executed on the sql server. The app seemed to work fine when I enabled msdtc network access on the server… >>> More