Distributed transaction Issue

• Jyothirmayee Gummuluri

  Valued Member

  Points: 57

  More actions

  January 18, 2006 at 3:14 am

  #615407

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

  regards..

  jyothi

• Jo Pattyn

  SSC-Dedicated

  Points: 32571

  More actions

  January 18, 2006 at 4:46 am

  #615431

  Is one of the servers OS windows 2003?

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

• sql_fan

  SSC-Addicted

  Points: 462

  More actions

  January 18, 2006 at 8:44 am

  #615511

  No,Both of them are on windows NT.

  Thanks.

• Jo Pattyn

  SSC-Dedicated

  Points: 32571

  More actions

  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?

• sql_fan

  SSC-Addicted

  Points: 462

  More actions

  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.

• Jo Pattyn

  SSC-Dedicated

  Points: 32571

  More actions

  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?

• Beppe Altomare

  Grasshopper

  Points: 23

  More actions

  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