Begin Distributed Transaction Problem

• madhu_r

  SSC Veteran

  Points: 298

  More actions

  January 21, 2003 at 11:25 pm

  #80676

  Hi,

  We are facing a problem using the Begin Distributed Transaction..Commit Transaction block. If the statement is commented the code works fine

  Error generated is as follows:

  ==================

  Server: Msg 7391, Level 16, State 1, Line 1

  The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

  [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

  ============================

  SP

  ============================

  ALTER PROCEDURE SP_EventCopy

  @LocalAddress as varchar(20),

  @ServerAddress as varchar(20)

  AS

  begin

  Declare @Error int

  -- begin distributed transaction

  declare @StrSql nvarchar(3000)

  set @strsql= N'Insert into ['+N''+

  @ServerAddress +

  N'].verinet.dbo.vmc_rawevents (Rawtype,Eventid,Doorid,Networkid,Cardno,Updateflag,communication,Logdate,Tono,Eventtime,Logtime,Operatorid)

  select Rawtype,Eventid,Doorid,Networkid,Cardno,Updateflag,communication,Logdate,Tono,Eventtime,Logtime,Operatorid

  from [' +N''+

  @LocalAddress +

  N'].verinet.dbo.vmc_rawevents'

  Execute sp_executesql @StrSql

  print @error

  select @error = @@error

  if @error !=0

  goto ERR_HANDLER

  else

  set @strsql= N'Delete from ['+N''+ @LocalAddress +N'].verinet.dbo.vmc_rawevents'

  Execute sp_executesql @StrSql

  select @error = @@error

  if @error !=0

  goto ERR_HANDLER

  -- commit tran

  Execute @error= sp_executesql

  N'Execute sp_dropserver @server=@Svr1',

  N'@Svr1 varchar(30)',

  @Svr1 = @LocalAddress

  Select @error = @@error

  if @error != 15028 AND @error !=0

  begin

  RAISERROR ('Error In addding second server', 16, 1)

  return @error

  print @error

  end

  print @error

  Execute sp_executesql

  N'Execute sp_dropserver @server=@Svr2',

  N'@Svr2 varchar(30)',

  @Svr2 = @ServerAddress

  return 0

  end

  ERR_HANDLER:

  begin

  -- rollback transaction

  if @error != 15028 AND @error !=0

  BEGIN

  RAISERROR ('Error in spEventCopy', 16, 1)

  return @error

  END

  end

  =================

  (Note: The servers are linked with the IP that is passed as a parameter to this SP)

  Thanks

• January 24, 2003 at 8:00 am

  This was removed by the editor as SPAM