Home > SQL Server > Distributed Transactions fail on Linked server

Distributed Transactions fail on Linked server

2013/08/19

I was working on a complex situation : there are 2 SQL 2008 servers , 1 SQL2008 with a local linked server versus an Oracle instance and 1 SQL 2008 R2 on which i must transfer heavy jobs running on the SQL 2008 instance (this page can be useful as a fast reference when you examine an existing SQL network, using SELECT @@VERSION).

First of all when the linked server is created is requested to set the remote credentials, in this case i’m on the SQL 2008 R2 instance and these are the remote SQL 2008 linked server properties:


Otherwise aren’t visible tables and view of the remote server, so in my example “sa” user and password are those of the SQL 2008 instance.

The problem was on a stored procedure that on the SQL 2008 instance was working , instead on the new SQL 2008 r2 i got

Invalid object name ‘GTI_DIV_Drivers_History’.

This is a view, that on the original SQL 2008 instance was a select from the remote (SQL 2008) linked server on a table resident on the remote Oracle (for the SQL 2008 instance) linked server .

This can be resolved using a SYNONIM, for example

CREATE SYNONYM GTIDBGTI_DIV_Drivers_History FOR SRVSQLTRASP.GTIDB.dbo.GTI_DIV_Drivers_History;

Using the GTIDBGTI_DIV_Drivers_History synonim instead of SRVSQLTRASP.GTIDB.dbo.GTI_DIV_Drivers_History in the SQL joins no more problems for the remote objects name.

But then came the big problem : launching the stored (that gives as result a SELECT, with joins from remote SQL and remote ORACLE tables) the result was the error

Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "S-DATABASE.AUTO.SV" was unable to begin a distributed transaction.

OLE DB provider "SQLNCLI10" for linked server "S-DATABASE.AUTO.SV" returned message "No transaction is active.

Note that in my stored there was no explicit BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION.

Anyway the first thing that i tried was to set the MSDTC properties.

On the Windows 2008 instance (running the SQL 2008 R2) in Start->Control Panel->Administrative Tools->Component Services right click on My Computer->Distribuited Transactions->Local DTC


And i changed settings as in figure (the No Authentication Required could not be secure …) ; on the Windows 2003 instance running SQL 2008 Start->Control Panel->Administrative Tools->Component Services right click on My Computer and the select the MSDTC tab, here clic on Security Configuration


Then configured as in below image:

Image

But this was not enough…same error…even after a reboot of both servers.

I also discovered this page on StackOverflow that can be used as a troubleshooting guide , but in my case i thinked to work on the transaction issue and in my code wrote an

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

before the query that was joining remote SQL and Oracle tables… and it works !

Naturally with a COMMIT TRANSACTION after the query.

The discovery is that must be specified the transaction level , in this case the one used by default, but it is needed to be written down: otherwise there is still the error “The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction”.

The other transaction level that works is READ UNCOMMITTED; REPEATABLE READ and SERIALIZABLE are not working because the error “The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.” , for SNAPSHOT the remote access is not supported.

In my case no problem: the query is on truck drivers hystorical data (paths from GPS) that once acquired *MUST* remains untouched; for other cases could be that the READ COMMITTED transaction level must be evaluated.

Advertisements
Categories: SQL Server
%d bloggers like this: