Linked server insert

[durug]

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

 

[TMRO]

Use Set XACT_ABORT ON before the insert statement.

Hope it will work,
TMRO