SET XACT_ABORT ON/OFF ?

Last Post 16 Jun 2004 10:56 PM by satya. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Jim_SQL
New Member
New Member

--
10 Sep 2002 01:54 PM
Creating triggers on DB1..table1 to insert/update/delete records in DB2..table2

when both databases are on the same server instance this works fine... when they are on different instances... I have to wrap the action transaction on DB1..table1 using SET XACT ON/OFF or else I get a message saying that nested transactions are not supported.

I don't want to be forced to put the two database on one server to get the trigger to work, but I am supporting a vendor application where their code will not use SET XACT ON/OFF.

The servers are linked (both are using SQL2000 on Win2K) ... am I missing something in the connection settings that will enable this to work without SET XACT ON/OFF ? or is there something I can do within the trigger itself?



verbose
New Member
New Member

--
11 Sep 2002 06:30 AM
What provider(s) are you using to create your linked servers?
Jim_SQL
New Member
New Member

--
11 Sep 2002 08:11 AM
all of the servers involved are SQL Server 2000, running on Win2000... the linked server name is the same as the network name of the server. They are two instances of SQL Server installed on one physical server.

verbose
New Member
New Member

--
12 Sep 2002 11:53 AM
Unfortunately, the OLE DB Providers for SQL Server/ODBC do not support nested transactions, this KB article has more details.
Jim_SQL
New Member
New Member

--
12 Sep 2002 11:59 AM
Thanks, the article is right on target. Now I can stop beating my head against the wall and find another way to solve our problem.

bigelectricmac
New Member
New Member

--
16 Jun 2004 08:13 PM
Hi

We have exactly same problem. May you suggest options for us? How you solved your problem?

Thanks
.jai
satya
New Member
New Member

--
16 Jun 2004 10:56 PM
May refer to the KB article referred by Verbose.
You are not authorized to post a reply.

Acceptable Use Policy