locked
How to NOT start a distributed transaction RRS feed

  • Question

  • Hi There

     

    We have a linked server setup between 2 instances across a WAN.

     

    This works fine:

     

    EXEC [11.45.101.100].OLTP1PRO.dbo.APT_getAccountDetails
    @HomeBusinessUnit = '03301',
    @CustomerID = '033014369',
    @OrderNo = null

     

    However as soon as i try to insert to a local table like this:

     

    INSERT INTO #test
     (Order_No_XX)
    EXEC [11.45.101.100].OLTP1PRO.dbo.APT_getAccountDetails
    @HomeBusinessUnit = '03301',
    @CustomerID = '033014369',
    @OrderNo = null

     

    I get the following error:

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Server: Msg 7391, Level 16, State 1, Line 1
    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
    [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

    Now i know what this error is i have seen it many times before, i have setup MSDTC many times to get distributed transactions working, however i cannot get it to work between thes to instances, they are across a WAN and only port 1433 is open , so i am not sure if other ports must be open or whatever for MSDTC.

    Bottom line it is a network issue that we simply dont have time to figure out.

     

    So my question is , how do i NOT start a distributed transaction , how do i tell sql that i do not want to begin a  distributed transaction, i have tried setting IMPLICIT_TRANSACTIONS OFF, this did not help.

     

    Thank You

     

    Tuesday, March 27, 2007 7:58 AM

All replies