Solved

SQL Server DTC URGENT PLEASE....

Posted on 2000-05-10
3
492 Views
Last Modified: 2012-06-27
Hi,
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
FOR  UPDATE
AS
BEGIN
           UPDATE  vauveil.urk2000.dbo.tbl_species
           SET sp_priority=B.sp_prior, sp_niv1=B.sp_grpcode, sp_niv2=B.sp_code,
                   sp_box_content=B.sp_qubox, sp_min_amount_startprice=B.sp_minkg,
                   sp_tara=B.sp_tara, sp_percent_liquid=B.sp_nat, sp_tolerance=B.sp_tol,
                        sp_shape=B.sp_shape, sp_size=B.sp_size
           FROM vauveil.urk2000.dbo.tbl_species as c, tbl_specie AS B   WHERE c.sp_code=B.sp_code
END

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.
0
Comment
Question by:TomDedecker
3 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 2798233
I'd recommend using the logical 'inserted' table:

CREATE TRIGGER UPDATE_SPECIE ON dbo.tbl_specie
FOR  UPDATE
AS
BEGIN
           UPDATE  vauveil.urk2000.dbo.tbl_species
           SET sp_priority=B.sp_prior, sp_niv1=B.sp_grpcode, sp_niv2=B.sp_code,
                  sp_box_content=B.sp_qubox, sp_min_amount_startprice=B.sp_minkg,
                   sp_tara=B.sp_tara, sp_percent_liquid=B.sp_nat, sp_tolerance=B.sp_tol,
                   sp_shape=B.sp_shape, sp_size=B.sp_size
           FROM inserted AS B
   WHERE vauveil.urk2000.dbo.tbl_species.sp_code IN (Select sp_code from inserted)

END

This will only update those that changed, and will not involve the original table at all, thus (hopefully) avoiding the problem with the transaction conflict.
0
 

Author Comment

by:TomDedecker
ID: 2799192
You' re right but this isn't a solution for our problem. Further tests hqve shown that the OLEDB transaction error isn't significant. As a matter a fact even zhen you have a syntax error in a trigger it gives the same error.
0
 

Accepted Solution

by:
bucodi earned 100 total points
ID: 2799577
We have done some tests and found the following solution:
Jo just put a COMMIT at the beginning of your trigger
CREATE TRIGGER UPDATE_SPECIE ON dbo.tbl_specie
FOR  UPDATE
AS
BEGIN
COMMIT
           UPDATE  vauveil.urk2000.dbo.tbl_species
           SET sp_priority=B.sp_prior, sp_niv1=B.sp_grpcode, sp_niv2=B.sp_code,
                  sp_box_content=B.sp_qubox, sp_min_amount_startprice=B.sp_minkg,
                   sp_tara=B.sp_tara, sp_percent_liquid=B.sp_nat, sp_tolerance=B.sp_tol,
                   sp_shape=B.sp_shape, sp_size=B.sp_size
           FROM inserted AS B
   WHERE vauveil.urk2000.dbo.tbl_species.sp_code IN (Select sp_code from inserted)

END
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question