SQL Server Performance

No transaction is active.

Discussion in 'SQL Server 2005 General Developer Questions' started by Roshan_kumar, May 5, 2008.

  1. Roshan_kumar New Member

    Hi all,

    I m trying to get record from one server to another using Commit and Rollback feature in the Sql Server and getiing error message--

    "No transaction is active."

    below is my sql block

    BEGIN TRAN
    BEGIN TRY
    INSERT INTO [SERVERNAME1].[DATABASENAME].[dbo].[TABLENAME](PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype)
    SELECT PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype
    FROM [SERVERNAME2].[DATABASENAME].[dbo].[TABLENAME]
    WHERE Convert(smalldatetime,purchasedate)>=Convert(smalldatetime,'2/14/2008 00:00:00') and Convert(smalldatetime,purchasedate)<= Convert(smalldatetime,'2/14/2008 23:59:59')
    COMMIT TRAN
    END TRY
    BEGIN CATCH
    Rollback TRAN
    END CATCH
    END
  2. Netic New Member

    Howdy, the BEGIN TRAN should be inside of the BEGIN TRY !
    Best regards,
    Paulo Condeça.
  3. Roshan_kumar New Member

    No working same error is comming
    OLE DB provider "SQLNCLI" for linked server "SERVERNAMESQLEXPRESS" returned message "No transaction is active.".
    Msg 7391, Level 16, State 2, Line 3
    The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SERVERNAMESQLEXPRESS" was unable to begin a distributed transaction.

  4. Netic New Member

    That is a differente issue !
    Configure the Microsoft Distributed Transaction Coordinator and check that it is up n' running !
    When you are implementing transactions in more than 1 server, you need MSDTC.
    Hope this gives a light to you .
    Best Regards,
    Paulo Condeça.
  5. Roshan_kumar New Member

    MSDTC is running on both the server
    Here what is selected:

    Network DTC Access (Checked)
    Allow Remote Client (Checked)
    Allow Remote Administration(Checked)
    Allow Inbound and Allow outbound (Both Checked)
    Manual Authentication Required(Checked)
    Enable Transaction Internet Protocol(TIP) Transactions(Checked)

    Enable XA Transaction (Checked)

    DTC Logon Account
    Account:NT AUTHORITYNetworkService
  6. Netic New Member

    Roshan,
    What are the Transaction Statistics & DTC LOG C:Windowssystem32MSDtc (Default Path).
    By the way... are there any Firewalls in the middle ?
    Best Regards,
    Paulo Condeça.
  7. satya Moderator

    One of the technet article refers:
    MSDTC Is Not Fully Enabled on WindowsBecause the Microsoft Distributed Transaction Coordinator (MS DTC) is not completely configured in Windows, applications might fail to enlist SQL Server Express resources in a distributed transaction. This problem can affect linked servers, distributed queries, and remote stored procedures that use distributed transactions. To prevent such problems, you must fully enable MS DTC services on the server where SQL Server Express is installed.
    To fully enable MS DTC:
    1. In Control Panel, open Administrative Tools, and then double-click Component Services.
      • In the left pane of Console Root, click Component Services, and then expand Computers.
        • Right-click My Computer, and then click Properties.
          • On the MSDTC tab, click Security Configuration.
            • Under Security Settings, select all of the check boxes.
              • Verify that the DTC Logon Account name is set to NT AUTHORITYNetworkService.
  8. Roshan_kumar New Member

    I Checked for the MSDTC in both the server it is enabled and running
  9. Roshan_kumar New Member

    Thanks for the replys,

    Satya I followed your instructions and can confirm msdtc is fully enabled on both servers.

    Netic there is a windows firewall, and I have added an exception for MSDTC and opened port 135.


    Following is the steps i took...

    To fully enable MSDTC:

    1 - In Control Panel, open Administrative Tools, and then double-click Component Services.
    2 - In the left pane of Console Root, click Component Services, and then expand Computers.
    3 - Right-click My Computer, and then click Properties.
    4 - On the MSDTC tab, click Security Configuration.
    5 - Under Security Settings, select all of the check boxes.
    6 - Verify that the DTC Logon Account name is set to NT AUTHORITYNetworkService.

    NEXT I MADE CHANGES TO WINDOWS FIREWALL...

    1 - Click Add Program to display the Add a Program dialog box.
    2 - Click Browse and navigate to %system32%msdtc.exe.
    3 - Click to select msdtc.exe and click Open.
    4 - Click Change scope to specify the set of computers for which MSDTC communications should be allowed and click OK. (I ADDED THE REMOTE SERVER IP ADDRESSES)
    5 - Open port 135
    6 - Stop and restart the Distributed Transaction Coordinator service.
    Launch a command prompt, type net stop msdtc and press Enter.
    After the Distributed Transaction Coordinator service has stopped, type net start msdtc and press Enter.


    THEN I RESTARTED BOTH SERVERS AND USED DTCTESTER AND GOT THE FOLLOWING RESULTS...


    C:>dtctester.exe test "username" "password"
    Executed: dtctester.exe
    DSN: test
    User Name: "value"
    Password: "value"
    tablename= #dtc17056
    Creating Temp Table for Testing: #dtc17056
    Warning: No Columns in Result Set From Executing: 'create table #dtc17056 (ival
    int)'
    Initializing DTC
    Beginning DTC Transaction
    Enlisting Connection in Transaction
    Error:
    SQLSTATE=25S12,Native error=0,msg='[Microsoft][SQL Native Client]The transaction
    has already been implicitly or explicitly committed or aborted
    '
    Error:
    SQLSTATE=24000,Native error=0,msg=[Microsoft][SQL Native Client]Invalid cursor s
    tate
    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.

    ANY IDEA'S WHAT MIGHT STILL BE CAUSING THE PROBLEM?

    THANKS,

    ROSHAN

  10. satya Moderator

    If checked & confirmed on everything its better to raise a support case for the problem [:)], last resort.
  11. Kewin New Member

    Another option: skip the distributed transaction altogether.
    Instead, just do a plain insert - select from.. into a staging table on the destination server.
    Validate
    Do the final insert into the prod table as a local transaction. (well, local in the sense only one server is involved..)
    Now, I haven't tried this with Express, so I'm not sure that it still won't complain about distributed transactions, since it may do it implicit anyway.
    But, maybe worth a try?
    /Kenneth

Share This Page