June 21, 2010 at 2:26 am
Hello,
I have the following situation:
I have two servers SERVER1 (which is a mirrored server) and SERVER2 (a failover cluster server) with MSDTC started on both servers (for SERVER2: local DTC and cluster DTC)
On both servers I've added links for the other server with the following settings:
-General: SQL Server Type
-Security: Local login sa and For a login not defined in the list above be made using this security context: Remote login sa and password...
-Server Options: Data Access: true, RPC: true, RPC out: true
If I execute any sp from one server remote to another (for example on Server1: exec server2.databasename.dbo.testsp), everything is ok.
But... I've made a trigger on SERVER1 (in which I call remote a procedure from SERVER2) and the following error appears:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SERVER2" was unable to begin a distributed transaction.
Both servers are Sql Server 2005 SP3 (OS on SERVER1: Windows Server 2008 R2 Enterprise, OS on SERVER2: Microsoft Windows Server 2003, Enterprise Edition, SP2)
Do you have any idea on what is happening? Please help me if you have one...
Wish you good ideas! 🙂
Andreea
June 21, 2010 at 2:33 am
This usually happens when your transcations escalate from local to distributed and you don't have XACT_ABORT set to ON.
Have you checked that?
-- Gianluca Sartori
June 21, 2010 at 2:48 am
Hello,
We knew about that option, our trigger looks like that:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[TRG_Loc_Status_Bol] on [dbo].[LV_StockContainer]
FOR update, delete, insert
AS
BEGIN
SET XACT_ABORT ON
DECLARE @loc_code varchar(13)
--Unblocklocation
SET @loc_code=(SELECT lbc.bol from deleted
INNER JOIN lv_location ll (nolock) on deleted.stc_locationid=ll.loc_id
INNER JOIN Oct_Loc_Bol_Cef lbc (nolock) ON ll.loc_Code=lbc.cef)
IF @loc_code IS NOT NULL
-- EXEC SERVER2.LVDelamode.dbo.Oct_Location_Status @loc_code,0
--Block Location
SET @loc_code=(SELECT lbc.bol from inserted
INNER JOIN lv_location ll (nolock) on inserted.stc_locationid=ll.loc_id
INNER JOIN Oct_Loc_Bol_Cef lbc (nolock) ON ll.loc_Code=lbc.cef)
IF @loc_code IS NOT NULL
BEGIN DISTRIBUTED TRANSACTION
SET XACT_ABORT ON
UPDATE SERVER2.LVDelamode.dbo.lv_location
SET loc_lockled = 1
FROM SERVER2.LVDelamode.dbo.lv_location ll
WHERE ll.loc_Code=@loc_code
COMMIT TRANSACTION
END
Wish you good ideas! 🙂
Andreea
June 21, 2010 at 2:58 am
I see at least three problems in you code:
1. Your trigger assumes only one record was modified. It's not guaranteed to be so.
2. You use the NOLOCK hint, that could read inconsistent data.
3. You're not trapping errors in your transaction and always assume a COMMIT to be done.
I suggest that you fix those points, first of all, then move on and work on the distributed transaction issue.
-- Gianluca Sartori
June 21, 2010 at 3:23 am
Thank you for highlighting the problems in the trigger code.
Assuming all the problems are solved, regarding the start-issue, can anyone help me with another suggestion?
Wish you good ideas! 🙂
Andreea
June 28, 2010 at 2:50 am
Hello,
I've found the problem.
I've checked for the cluster MSDTC the Network DTC Acces with both "allow inbound" and "allow outbound" checkboxes.
Many thanks
Wish you good ideas! 🙂
Andreea
June 28, 2010 at 3:20 am
Thanks for giving us feedback on your issue.
I hope this helps others experiencing the same.
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply