SQL Server 7.0 trigger help

DeRay_Scholz

Active Member
This is to all of you SQL Server gurus out there.

I am running SQL Server 7.0 and am adding a trigger to a JDE table. The trigger will execute on any insert to the table and insert the data into another JDE table which resides on another server.

I am getting the following error when I try and add a record to the original table:

Server: Msg 7392, Level 16, State 2, Procedure TEST, Line 3
Could not start a transaction for OLE DB provider 'SQLOLEDB'.
[OLE/DB provider returned message: Cannot start more transactions on this session.]

I can't seem to figure out what I am doing wrong or if there is some setting that I am missing.

Any help would be appreciated.

Thanks,
DeRay Scholz
The Upper Deck Company
B733.2 SP11.1 NT SQL 7.0
 

michelle_perrin

Active Member
I had the same problem yesterday and today.

I resolved the problem by including a 'COMMIT' statement in the SQL Table
trigger.

Be aware though!

1) I am not a SQL guru and it was only trail and error to find a solution,
therefore I am not sure if it is the optimum solution.

2) If there are any other table triggers on the same table, that execute
after your trigger there will be no data available if these triggers do a
SELECT x from INSERTED.
 

DeRay_Scholz

Active Member
Top