Transaction Context in use by another session - Linked Server's Trigger

• sridhar.gudeti

  SSC Veteran

  Points: 223

  More actions

  May 18, 2009 at 4:56 am

  #384608

  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 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))

  [highlight=#ffff11] Insert into Server2.MyDB2.DBO.MyTable(Sno,Name) Values(1,'Sri')[/highlight]

• Jeffrey Williams

  SSC Guru

  Points: 90346

  More actions

  May 18, 2009 at 1:39 pm

  #996595

  I would think the problem is caused because you are using dynamic SQL and you have started the transaction outside the dynamic SQL. Additionally, the way you are coding this is going to cause you other problems when you try to insert more than a single row at a time.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• sridhar.gudeti

  SSC Veteran

  Points: 223

  More actions

  May 19, 2009 at 3:03 am

  #996787

  In real scenario I can insert only one record at a time, because the trigger fires when we insert a record into this table.

  Pl. correct my above trigger.

  Thanks,

  Sri