Post reply

Msg 7391 Unable to begin a distributed transaction

  • May 17, 2007 at 4:25 am

    #120139

    Hi,

    I have two servers - On a daily basis Server A Executes a S. Proc on Server B which collects data (selects) from partictular tables and inserts into a local temp table on Server A.

    Im getting 60% Success Rate The rest of the time returns the following:

    Msg 7391, Level 16, State 2, Procedure procedurename, Line 20

    The operation could not be performed because OLE DB provider "SQLNCLI" for

    linked server "servername" was unable to begin a distributed transaction.

    Both Servers are Windows 2003 Sp1 & SQL 2000 sp4, although I have seen this problembetween SQL 2005 & SQL 2000 sp4

    They are in the same Workgroup. They are set up as Linked Servers in SQL and

    have entries in the Hosts file on each server.

    I've ruled out -

    The MSDTC Security Settings

    Firewall

    Its not a loopback query

    In fact.. the following query fails intermittently:

    SET XACT_ABORT ON

    BEGIN DISTRIBUTED TRANSACTION

    SELECT *

    FROM <server_name>.Pubs.dbo.Authors

    WHERE au_lname = 'Ringer'

    COMMIT

    This issue is causing major problems, due to its nature and because its failure rate is high

    Any help would be very much appreciated!!!!

  • May 18, 2007 at 10:52 am

    #707531

    you have to tweak properties of w2003 msdtc to allow it to talk to w2000, selecting  "no authentication required"...

  • May 21, 2007 at 2:24 am

    #707749

    thanks for the reply, I already have those settings correct

  • May 21, 2007 at 12:48 pm

    #707904

    Hi,

    with the same error message I have tried everything. DTC settings, permissions etc. The only thing that helped was to run my distributed queries from SQL Server 2005.

    Regards,Yelena Varsha

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

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