Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sridhar.gudeti@gmail.com
Starting Member
1 Post |
 Posted - 2009-05-18 : 07:08:40
|
| Hello,Please bare with me as I am new to this. We have two SQL 2005 Database Systems. 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?Thank you for your time. Best RegardsSridhar1) 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)) Insert into Server2.MyDB2.DBO.MyTable(Sno,Name) Values(1,'Sri') |
|
|
|
|
|
Transaction context in use by another session