Post reply

Distributed transaction Issue

  • January 17, 2006 at 2:52 pm

    #92849

    I have two database servers and I have a stored procedure which will do a distributed transaction. The procedure runs on serverA and gets the data from serverB onto serverA. But the procedure is getting failed and I am getting the following error.

    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300). The step failed.

    So when I checked the properties of my serverA on the connections tab

    Enforce Distributed Transactions(MTS) is not checked.

    Is this causing the above error or do I need to anything else to run my SP successfully.

    Thanks.

  • January 18, 2006 at 3:14 am

    #615407

    Add ServerB as a linked server..and run the stored procedure..

    regards..

    jyothi

  • January 18, 2006 at 4:46 am

    #615431

    Is one of the servers OS windows 2003?

    http://support.microsoft.com/default.aspx/kb/839279?

  • January 18, 2006 at 8:44 am

    #615511

    No,Both of them are on windows NT.

    Thanks.

  • January 18, 2006 at 10:57 am

    #615562

    NT-machines... hmm... which servicepack and which sql server version?

    Do the tables have a primary key?

    Both servers have MSDTC service running?

    Have you rebooted the machines?

  • January 18, 2006 at 11:04 am

    #615569

    Both are sql server 2000 and same service packs

    and the tables have primary key and MSDTC is running on both the servers and I haven't boot the serverB in recent days.

     

    Thanks.

  • January 18, 2006 at 12:10 pm

    #615588

    Autostart MSDTC is enabled on both sql servers?

    Unsure about this but there could be a problem when msdtc is started after sql server.

    Same servicepacks.. SP1,SP2,SP3,SP3a,SP4...?

    Nt servicepack SP6, SP6a?

    Any firewall in between?

  • February 1, 2006 at 4:22 am

    #618074

    I have experienced with a similar problem.
    It was a SQLServer - MSDTC configuration problem. You should obtain the same problem doing:
     
    SET xact_abort ON

    GO

    USE  DP_databasename

    GO

    BEGIN DISTRIBUTED TRANSACTION

    SELECT  *  FROM Servername.databasename.dbo.TABLE

    COMMIT TRAN

    GO
     
    In our experience this kind of problem arises also when the Computer name has been changed after that the SQL Server is installed. In this case MSDTC gives problems. Reinstalling SQLserver solves the problem, but in order to avoid this, check the following:
     
    USE Master
    select @@SERVERNAME
     
    If the result is NULL, it's case above described. Do the following:
     
    EXEC sp_dropserver 'ComputerName'

    EXEC sp_addserver 'ComputerName', LOCAL

    EXEC sp_serveroption 'ComputerName', 'data access', 'TRUE'

     
    Then Stop and Start SQLserver.
    I hope this help.
    Beppe

Viewing 8 posts - 1 through 7 (of 7 total)

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