Error linked server and transaction

• Hardy-478735

  SSC Rookie

  Points: 44

  More actions

  January 16, 2008 at 7:04 am

  #98784

  Hello,

  I've a problem with a software developped in C# with the framework 3.0.

  This is the error I receive :

  The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.".

  If I try directly to restart the process, it works fine.

  Is there someone who can help me ?

  This is the process

  1. In C# --> Call of a StoreProcedure : select from the linked server (db in sql 2000) and insert into a table SQL 2005

  In the StroreProcedure, there is an other SP who do an update in the SQL 2005 if necessary. In the table SQL 2005, there is also a trigger who do an update in the SQL 2000 database.

  2. In the C# --> using (TransactionScope scope = new TransactionScope())

  and insert in a table in SQL 2005 and an update in the SQL 2000 Database.

  Thank in advance.

• Zach Austin

  Valued Member

  Points: 59

  More actions

  February 4, 2008 at 12:03 pm

  #774856

  I'm having the same problem between SQL2005 and SQL2000.

  I'm using a standard T-SQL AFTER INSERT Trigger to update data in the SQL2000 database. Did you find a solution this?

• Hardy-478735

  SSC Rookie

  Points: 44

  More actions

  February 5, 2008 at 12:01 am

  #774997

  Yes, I've found a solution.

  I had also a trigger on a table and in my storeprocedure I do an insert into this table if the customer doesn't exist.

  I desactive this trigger before the insert and reactive it after the insert.

  That works fine now.

  ALTER TABLE CLIENT DISABLE TRIGGER TRIGGER_CLIENT_UPDATE

  ALTER TABLE CLIENT ENABLE TRIGGER TRIGGER_CLIENT_UPDATE

  I hope this help you.

• ankemprasanna

  Newbie

  Points: 5

  More actions

  May 15, 2008 at 11:34 am

  #816067

  I have same problem. But what i am doing is just a SELECt query. Even than i am getting same above error.