Linked Server insert

• DuruG

  SSCrazy

  Points: 2089

  More actions

  February 7, 2003 at 7:10 am

  #79519

  Hi everybody!

  Here is the story:

  we have an application that we didn't designed that is inserting data into a DB.

  Since we don't know how it works, we add an insert trigger for a table which will every time when a new record is added, insert the same information into another table into a DB, which is configured as a linked server.

  The insert statement from the trigger looks like this:

  insert into linkedserver.repair.dbo.repair_info (ProcessId,TestID) values (1,2)

  The error that I'm receiving is:

  Server: Msg 7392, Level 16, State 2, Procedure updaterepairdb1, Line 127

  Could not start a transaction for OLE DB provider 'SQLOLEDB'.

  [OLE/DB provider returned message: Cannot start more transactions on this session.]

  where updaterepairdb1 is the trigger name and line 127 is the insert statement

  If I disable the trigger, and then I run the insert statement from query analyzer against the linked server, it's working.

  So probably it has to do something with transactions. But I have no clue what to do.

  Any ideas?

  Thanks a lot,

  Durug

  PS. Both machines are SQL7

• DuruG

  SSCrazy

  Points: 2089

  More actions

  February 7, 2003 at 7:41 am

  #448376

  I found the problem

  Set XACT_ABORT ON

  Here is were I found the solution and for more explantions:

  http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306649&