July 2, 2011 at 11:22 pm
Hi Guys,
I am in bit of dilemma here. I have sql server 2008 installed on 2 different servers with windows 2008 R2 as OS.
I have created an linked server to read data from another server. I am able to read the data, but when I perform any Insert statement using triggers in the linked server, I get the following error.
OLE DB provider "SQLNCL0" for linked server <> returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 9
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "192.168.0.13" was unable to begin a distributed transaction.
I have checked the MSDTC settings on both the servers, and it seems fine.
Not able to identify whats the issue. Any Help would be appreciated.
July 3, 2011 at 10:40 pm
Ok further debugging, I found that when I ran the same query with the linked server in the query window it executes.
It only gives an error when used with trigger. Is there any option that i need to enable or disable for trigger on Server, in order to execute
July 5, 2011 at 4:47 pm
When you use a Linked Server in a trigger you're starting a transaction on the remote server within the context of an already running transaction in the local server...which requires the use of DTC.
Why are you trying to use a Linked Server in a trigger anyway? This can cause major problems in your database should the server being referenced by the Linked Server go down.
A better approach IMO for the trigger would be:
1) write whatever data you were going to write to the remote server to a local staging table
2) create a "sweeper process" in the form of a stored procedure that will copy data from the local staging table to the remote server using a Linked Server and remove it from the local staging table (an SSIS package may be an option instead of a stored proc using a Linked Server)
3) schedule the "sweeper process" to run as often as is required
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply