SQL Server linked server: "Cannot start more transactions on this session."

Question

i am trying to perform an INSERT operation against a linked server:

DBCC TRACEON (3604, 7300)

BEGIN TRANSACTION

INSERT INTO LIVE.Contoso.dbo.Events (EventGUID, EventDate, LoginGUID, UserGUID, EventType, Notes, TargetGUID) 
VALUES ('{494D023F-CD5A-11E2-9F18-C86000D0B92A}', getdate(), '{3B4F90C0-CD5A-11E2-9F18-C86000D0B92A}', '{494D023D-CD5A-11E2-9F18-C86000D0B92A}', 1, N'Test notes', '{494D023E-CD5A-11E2-9F18-C86000D0B92A}')

ROLLBACK TRANSACTION

and it returns the error:

OLE DB provider "SQLNCLI" for linked server "LIVE" returned message "Cannot start more transactions on this session.".
Msg 7395, Level 16, State 2, Line 3
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "LIVE". A nested transaction was required because the XACT_ABORT option was set to OFF.

This local database was moved from 2000 (where queries worked) to 2005 (where queries no work). The remote server is 2008 R2.

What have you tried?

The exhaustive list of things from this question that i asked two years ago.

How did you create the linked server?

--EXEC master.dbo.sp_dropserver @server = N'LIVE'
EXEC master.dbo.sp_addlinkedserver @server = N'LIVE', @srvproduct=N'', @provider=N'SQLOLEDB', @datasrc=N'vader'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LIVE', @locallogin = NULL, @useself = N'False', @rmtuser = N'Contoso', @rmtpassword = N'Battery Horse Staple Correct'

But what have you tried?

• i disabled all MSDTC security options on both servers

  

• checked that the clocks are in sync (which for some unknown reason will break various authentication schemes if they're out of sync)

  

• i disabled firewalls on both servers (no screenshot; you'll have to just trust me)

• leaving the rejoining the domain

What are the versions of the servers?

• Local: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
• Linked remote: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Solution

From the documentation of SET XACT_ABORT:

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions."

Perhaps you were using a provider that supported nested transactions on 2000. Seems like you need to set XACT_ABORT. Not sure if this fits with what your production code is doing though.

This answer may help: What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?

Comments

[Mark]

It is possible that Microsoft's provider (SQLOLEDB) did support nested transactions. Even though i created the linked server on 2005 using SQLOLEDB, the error is being reported from the "newer" SQLNCLI; which might not be as capable as the older provider.