OLE DB provider "SQLNCLI" for linked server "lgkprog" returned message "No transaction is active.".


  • Not sure i this is the right forum, but here is my problem:


    I am having a problem with distributed transactions, at least that is what the error messages are stating.  My intentions are to do the following:

    1.  Set up a trigger on Server1.database1.table1 to fire after Insert/Update

    2.  The trigger will call a stored procedure on Server2 that will insert data into server2.database2.table2 passed as parameters from within the trigger.

    3.  I will use a linked server on Server1 to Server2.

    4.  This must work for version 2000/2005 SqlServers.


    The following is the trigger I set up as a test trigger on Server1:





    CREATE TRIGGER [dbo].[trig_test]

       ON  [dbo].[tblCMOffense]




          DECLARE @OffNum int

          DECLARE @Time DateTime

          SET @OffNum = (SELECT OffNumber FROM Inserted)

          SET @Time = GetDate()

          EXEC [lgkprog].[traffic].[dbo].[sp_TestTrigger] @OffNum,@Time



    lgkprog is server2 which is setup as a linked server on server1 where the table ‘tblCMOffense’ is located.


    The following is the script for the stored procedure sp_TestTrigger:







    ALTER PROCEDURE [dbo].[sp_TestTrigger]

          @OffNum int,

          @Time datetime



          SET XACT_ABORT ON;

          SET NOCOUNT ON;


        Insert into tblTestTrigger (offensenumber,insert_date) Values (@OffNum,@Time)



    When I run the following query in SQL Server Management Studio new query window:


    insert into tblCMOffense (Case#,PartyNumber) Values ('SUCR2005000002',1)


    I get the following error:


    OLE DB provider "SQLNCLI" for linked server "lgkprog" returned message "No transaction is active.".

    Msg 7391, Level 16, State 2, Procedure trig_test, Line 15

    The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "lgkprog" was unable to begin a distributed transaction.


    Server1 is running on Windows XP Pro SP2, Server2 is running on Windows Server 2003 Enterprise Edition SP2.


    I have enabled MSDTC by following the steps outlined in: How to enable network DTC access in Windows Server 2003 You may receive a 7391 error message in SQLOLEDB when you run a distributed transaction against a linked server after you install Windows XP Service Pack 2 or Windows XP Tablet PC Edition 2005.


    Any help on this would be greatly appreciated.


    Thanks, Les


    I downloaded and ran 'dtctester.exe' and obtained the following results from server1 machine connected using dsn to server2 machine:


    Executed: dtctester

    DSN:  lgkprog

    User Name: sa

    Password: ****

    tablename= #dtc2462

    Creating Temp Table for Testing: #dtc2462

    Warning: No Columns in Result Set From Executing: 'create table #dtc2462 (ival int)'

    Initializing DTC

    Beginning DTC Transaction

    Enlisting Connection in Transaction


    SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver]Distributed transaction error'


    SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cursor state

    Typical Errors in DTC Output When

    a.  Firewall Has Ports Closed


    b.  Bad WINS/DNS entries


    c.  Misconfigured network


    d.  Misconfigured SQL Server machine that has multiple netcards.

    Aborting DTC Transaction

    Releasing DTC Interface Pointers

    Successfully Released pTransaction Pointer.

    Friday, October 10, 2008 5:35 PM

All replies

  • Could you try starting the distributed transaction manually?


    begin distributed transaction


    insert into [dbo].[trig_test]



    commit transaction



    Remember to add code to handle errors.




    Friday, October 10, 2008 6:02 PM
  • A possible solution - URL provided

    I have wasted half a day's work trying to understand the origin of this problem. Since this forum post ranks pretty high on a googe search for the search string "2147168242 Distributed transaction error", I decided to post here what's helped me solve this problem:

    "Warning: the CID values for both test machines are the same"

    The problem stemmed from two duplicate machines (as a result of a VMware template) - both having the same Security Identifiers (SID's).

    In case the URL above will become invalid in the future, this is the process that was required to solve my issue:

    1. Use Add Windows Components, and remove Network DTC.
    2. Go to the command line and run: MSDTC -uninstall
    3. Go to the registry and delete the MSDTC keys in HKLM/Software/Microsoft/Software/MSDTC, HKLM/System/CurrentControlSet/Services/MSDTC, and HKEY_CLASSES_ROOT\CID (if they’re still there).
    4. Reboot
    5. Go to the command line and run: MSDTC -install
    6. Use Add Windows Components, and add Network DTC.
    7. Go to the command line and run: net start msdtc
    8. Go to services.msc and change Distributed Transactions Coordinator's startup method to Automatic.

    Sunday, June 21, 2009 12:04 PM