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 [].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
    EXEC [].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