SQL Server Performance

"No transaction is active

Discussion in 'SQL Server 2005 General Developer Questions' started by dineshasanka, Dec 5, 2006.

  1. dineshasanka Moderator

    I have requriment of update data in a linkedserver SQL Server from a Trigger.
    How Ever, I am getting follwing error when I tried it.
    Update can be done from simple query and it is failing only in Trigger.

    OLE DB provider "SQLNCLI" for linked server SOURCESERVER returned message "No transaction is active.".
    Msg 7391, Level 16, State 2, Procedure TR_U_DimClient_sanj, Line 32
    The operation could not be performed because OLE DB provider "SQLNCLI" for linked server SOURCESERVER was unable to begin a distributed transaction.


    I have set XACT_ABORT ON and REOMTE PROCEDURE ON also MSDTC is also running. HOSTS file is also updated.
    both servers named servers. Is that can be the problem


    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  2. ranjitjain New Member

    Hi Dinesh,
    I had read few times back that if we run any query inside transaction involving linked server, then it fails with above error message,
    so workaround is to run those operations on linked servers outside any transactions.
    So you need to make sure that those queries are not running in any transaction.

    I think if you run them inside trigger than it will involve transaction and that's the reason it will fail.

    Also in 2005 you can access virtual tables inside Stored procedures, so no need to write triggers.

    I Hope this info helps you in some manner.
  3. satya Moderator

  4. dineshasanka Moderator

    it says
    Check the return value of the system function @@SERVERNAME on both servers. Verify whether the
    return value matches the computer name of each server. If it does not match, you have to
    rename the server.

    but @@SERVERNAME is not equal to machine name becos of named instance

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/

Share This Page