May 18, 2009 at 8:41 am
Hello,
Please bare with me as I am new to this. We have two SQL 2005 Database Systems
meaning Linked servers. After an insert at a MyTable1 in Database MyDB1,
TR_MyTrigger1 is fired which tries to insert a record in MyTable2 table on
Database MyDB2 on Server2. Now I always get the error: Transaction context in use
by another session - Msg 3910.
If I write insert statement individually (see below bold) it work, but I can't
insert thru trigger. How can I avoid this?
Both servers having SQL2005 with SP3 and Distributed Transaction Coordinator
service is in Start mode.
MSDTC - Security Configuration
Network DTC Access - Yes
Allow Remote Clients - Yes
Allow Inbound - Yes
Allow Outbound - Yes
No Authentication Required - Yes
Firewall's on both servers disabled
Event though I can't insert a record thru trigger.
Thank you for your time.
Best Regards
Sridhar
1) Server Name: Server1
a) Database Name : MyDB1
Table Creation:
Create Table MyTable1(Sno int, Name varChar(100))
Trigger Creation:
Create Trigger dbo.TR_MyTrigger1 on dbo.MyTable1
for Insert
as
Declare @Sno int,
@Name varChar(100)
Select @Sno = Sno,
@Name = Name
From Inserted
Set Ansi_Nulls on
Set Ansi_Warnings on
Set XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
Selct @SQL1 = 'Insert into Server2.MyDB2.DBO.MyTable(Sno,Name) Values(' +
Convert(Varchar(15),@Sno)+','+'''+@Name+''')'
Exec @SQL1
IF XACT_STATE() = -1
BEGIN
PRINT 'ROLLBACK TRAN'
ROLLBACK TRAN
END
ELSE
IF XACT_STATE() = 1
BEGIN
PRINT 'COMMIT TRAN'
COMMIT TRAN
END
ELSE
IF XACT_STATE() = 0
BEGIN
PRINT 'NO TRANSACTIONS'
END
SET XACT_ABORT OFF
2) Server Name: Server2
a) Database Name: MyDB2
Table Creation:
Create Table MyTable1(Sno int, Name varChar(100))
Select * from Server2.MyDB2.DBO.MyTable
May 18, 2009 at 10:23 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic718937-338-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply