Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004

    Unanswered: Insert ... Exec unable to begin a distributed transaction

    I have 3 development SQL Servers A, B & C, all running SQL 2000 sp3 and Windows 2003. Servers B & C have a linked server pointing to A, and A has one pointing to B & C. The linkedservers all have RPC , RPC out enabled. I have a stored procedure called test on server A.

    Create Proc test

    Select Top 5 first_name, last_name from people


    --code ran on Servers B & C:

    create table #tmptbl (nm varchar(100), nm2 varchar(100))

    insert into #tmptbl
    Exec ServerA.db1.dbo.test

    When the Insert....Exec code above is ran from server B it works fine, however when I run it from Server C, I get error 7391
    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]

    But regular linked server calls (directly to tables) and openquery calls work fine from either server...

    insert into #tmp
    Select top 5 first_name, last_name from ServerA.db1.dbo.people

    --and this works also

    insert into #tmp
    Select * from openquery(ServerA, 'Exec db1.dbo.test')

    Both servers (B & C) appear to be configured the same, and
    I have reconfigured MSDTC on all three boxes through control panel and component manager, have tried using SET xact_abort, SET implicit_transactions, registry hacks (TurnoffRPCsecurity), basically everything listed on Microsoft, and everything I've been able to find in these groups.

    If anyone has any ideas, I'd like to hear them.


  2. #2
    Join Date
    Nov 2002
    Well it doesn't make sense, but somethings going on...

    Did you run a trace or look at the error logs?

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Yep. can't see anything strange in the error logs, and running a trace just tells me what I already know, that SQL is trying to start and join a distributed transaction, but cannot.

    Tracing the calling db shows a DTCTransaction event ocurring twice, then the error and exception get thrown. I've tried begin distributed transaction also, but to no avail.

  4. #4
    Join Date
    Dec 2007
    Is there a solution to this problem yet? I have the same exact problem. Everything works fine, until I try the insert statement.

    So this tells me Linked Server is working fine. Something about an Insert statement doesn't work.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts