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

    Question

  • 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:

     

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [dbo].[trig_test]

       ON  [dbo].[tblCMOffense]

       AFTER INSERT,UPDATE

    AS

    BEGIN

          DECLARE @OffNum int

          DECLARE @Time DateTime

          SET @OffNum = (SELECT OffNumber FROM Inserted)

          SET @Time = GetDate()

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

    END

     

    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:

     

    set ANSI_NULLS ON

    GO

    set QUOTED_IDENTIFIER ON

    GO

     

    ALTER PROCEDURE [dbo].[sp_TestTrigger]

          @OffNum int,

          @Time datetime

    AS

    BEGIN

          SET XACT_ABORT ON;

          SET NOCOUNT ON;

     

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

    END

     

    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:

    http://support.microsoft.com/kb/817064 How to enable network DTC access in Windows Server 2003

    http://support.microsoft.com/?kbid=873160 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

    Error:

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

    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

    -OR-

    b.  Bad WINS/DNS entries

    -OR-

    c.  Misconfigured network

    -OR-

    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.

     

     

    AMB

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

    Hi,
    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"
    http://blog.wadewegner.com/index.php/2007/08/13/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