Post reply

Unable to begin a distributed transaction

  • October 29, 2009 at 2:55 pm

    #216267

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

    Msg 7391, Level 16, State 2, Line 1

    The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "server1" was unable to begin a distributed transaction.

    This is the error message I'm getting when trying to execute a stored proc and save its output into a temp table.

    This is basically what I have

    1. SQL Server 2005 Standard on both machines Server1 and Server2

    2. Temp table called #temp on Server2. It is predefined

    3. Stored proc on Server1

    4. Server3 where everything works fine.

    I'm trying to execute from Server2 the following SQL code

    create table #temp (field1 varchar(10))

    exec server1.database.dbo.storedproc 'myparam'

    --above statement returns rows

    insert into #temp

    exec server1.database.dbo.storedproc 'myparam'

    -- it fails right here with the message I wrote above.

    Now, I also have Server3, which has a similar config to Server2. And when I run this SQL command it works fine.

    I went through different user groups and forums and I can't find adequate answer to this.

    What should I be checking? Any suggestions?

    Thanks!

  • October 29, 2009 at 3:27 pm

    #1072858

    Have you checked the DTC configurations between Server2 and Server 3? You will find that through Admistrative Tools, Component Services.

  • October 29, 2009 at 6:54 pm

    #1072919

    Yes I did, and this is even more confusing because according to different posts I have gone through my query shouldn't work at all.

    So basically the way it looks like right now is this:

    1. Server1 has DTC enabled.

    2. Server2 has DTC disabled. The query fails.

    3. Server3 has DTC disabled. The query runs fine.

    The event viewer doesn't have anything logged.

    BTW. This is Windows 2k3 with latest service pack. SQL Server is 2005 with SP3 on all machines.

  • October 5, 2010 at 11:42 am

    #1231940

    Hi,

    I have the same issue. Inserting into a table by executing from a 4 part qualified remote stored procedure...

    Did you ever get it working?

    Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply