Problem with linked server: A nested transaction was required because the XACT_ABORT option was set to OFF.

Question

Post reply

Problem with linked server: A nested transaction was required because the XACT_ABORT option was set to OFF.

the_fenix

Right there with Babe

Points: 789
More actions
June 11, 2009 at 9:49 am

#205935

Hi!
Does anyone know what i'm supposed to do in order to fix this issue?
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "LINKED_SERVER". A nested transaction was required because the XACT_ABORT option was set to OFF.
This error is happening on a new sql server we are adding for serving a very demanding site, and it's a COPY of those that are already working. On many places suggest to add "SET XACT_ABORT ON" before executing the problematic command, I can't add "SET XACT_ABORT ON" and it shouldn't be necesary since it's already working whihout that command on other instances.
I don't know what else to try 🙁
Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

Have you looked at the MS article?

http://support.microsoft.com/kb/306212

the_fenix Right there with Babe Points: 789 More actions · Answer 2

I'll take a look. Thanks much!!!

Anyhow, it's very weird. All servers are configured equally and now seems to be no difference.

The first step I had to set up the Network DTC access, after setting up that I'm getting this new error.

the_fenix Right there with Babe Points: 789 More actions · Answer 3

3 questions related to this problem

- How do I check the current value for XACT_ABORT?

- Once I ser XACT_ABORT to ON remains like that until you explicity set to OFF or it has at scope and after that come back to false?

- I've read this from other site

"Also, I looked at the Linked Server provider options for SQLNCLI and came accross the Nested Queries option being unchecked. I checked the option and applied it to the Linked Server."

Is this possible? I mean, activate nestes queries for SQLNCLI? If yes.. where?

Thanks a lot 🙂

Dave W. SSC Enthusiast Points: 182 More actions · Answer 4

Server Objects, Linked Servers, Providers, SQLNCLI. Right-click and choose Properties...

Be very careful about changing settings.

andyobl Right there with Babe Points: 767 More actions · Answer 5

This is what I did to fix my issue.

ALTER Procedure [dbo].[sp1]

@appID int

as

--begin

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET XACT_ABORT ON

Declare @TranID int

BEGIN TRAN

Select @TranID = NextTransactionID

From [linked_svr1].[db1].[dbo].[tbl1]

Where ApplicationID = @appID

SET @TranID = @TranID + 1

UPDATE [linked_svr1].[db1].[dbo].[tbl1]

SET NextTransactionID = @TranID

WHERE ApplicationID = @appID

IF @@ERROR <> 0

BEGIN

ROLLBACK TRAN

END

COMMIT TRAN

Select @TranID

SET XACT_ABORT OFF

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

Andy,

This type of sequence table logic can be problematic if 2 processes hit the table at the same time requesting the next TranID (sequence number). You can get the next sequence number and update the table in one statement that is already an implicit transaction. I've been using this type of sequencing for years with no problems. In this example I'm not using a distributed transaction

UPDATE Tbl1

SET

@TranID = NextTransactionID

, NextTransactionID = NextTransactionID + 1

WHERE ApplicationID = @appID

There is no need to create an explicit transaction since the UPDATE is an implicit transaction anyway.

I've not used this technique specifically across linked servers, but I see no reason for it not to work.

Regards,

Todd Fifield