distributed transaction

• akila.baskaran-1093037

  SSC Veteran

  Points: 282

  More actions

  May 25, 2009 at 8:38 am

  #137977

  Hi Friends,

  My scenario is as follows

  I have a table1 in source

  I need to copy the records from table1 to table2 which is in target

  I also need to delete all the rows from table1.

  I use link servers to access both the source and target tables.

  If any error occors in the middle or if the network is down, i had to rollback all the operation.

  But am getting an error "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

  If i include "BEGIN TRANSACTION" then it shows me

  "Transaction context in use by another session."

  I am new to sql server and i donot know what is to be done in this issue.

  Kindly find my code below

  BEGIN transaction

  declare

  @err1 int,

  @err2 int,

  @err3 int,

  @strcreateparent nvarchar(2000),

  @strcreatechild nvarchar(2000)

  begin

  --parent

  set @strcreateparent ='insert into [new_target_testing].[target_testing].[dbo].test_copy select * from [new_source_testing].[source_testing].[dbo].test where id in (1,2)'

  print @strcreateparent

  EXEC sp_executesql @strcreateparent

  SELECT @err1 = @@error

  print @err1

  set @strcreatechild = 'deletes from [new_source_testing].[source_testing].[dbo].test Where (id) In (2)'

  print '@strcreatechild'

  print @strcreatechild

  EXEC sp_executesql @strcreatechild

  SELECT @err3 = @@error

  print @err3

  if (@err1 <>0 or @err3 <>0)

  BEGIN ROLLBACK TRANSACTION

  end

  end

  end

  Kindly help me ASAP.

  Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738693

  More actions

  May 25, 2009 at 10:06 am

  #999695

  Is DTC running on both computers? Not sure if it's needed here, but it helps to control transactions across two computers, and you are starting a transaction in the linked server.

  Does this apply?

  http://support.microsoft.com/kb/947486/en-us

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  May 25, 2009 at 10:31 am

  #999704

  For a distributed transaction the correct syntax is BEGIN DISTRIBUTED TRANSACTION as opposed to BEGIN TRANSACTION.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.