I'm in a situation with two servers (SQL Server 7 ) in the same domain. I configured both servers as linked servers. I also run MSDTC on both servers. I have a trigger on the table in one DB which should update a table in the other DB on the other server. The trigger looks as follows :
CREATE TRIGGER UPDATE_SPECIE ON dbo.tbl_specie
SET sp_priority=B.sp_prior, sp_niv1=B.sp_grpcode, sp_niv2=B.sp_code,
sp_tara=B.sp_tara, sp_percent_liquid=B.sp_nat, sp_tolerance=B.sp_tol,
FROM vauveil.urk2000.dbo.tbl_species as c, tbl_specie AS B WHERE c.sp_code=B.sp_code
When I try to update the table I receive the following error message :
OLE/DB Provider returmed message : Only one transaction can be active on this session.
Could not start a transaction for oledb provider 'SQLOLEDB'.
I looked at the settings but I don't seem to have any way that I can adjust a setting. What is going wrong? Do I need to specify that it is a distributed transaction. I tried it and replaced the begin by begin distributed transaction and the end by commit transaction but this didn't work either. I need to strat this project up tomorrow so all suggestions are welcome.