BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

Post by Steve Thor » Wed, 24 Sep 2003 00:13:38


Hi have have two linked SQL Servers and I am trying to get things working
smootly/quickly.

Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?

Basicly, these SPs update a local table and a remote table in the same
transaction. I cant have one table updated and not the other. Please dont
say replicate the tables either as at this time, this is is not an option.

I have for example a number of stored procedures that are based around the
following:
where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@strResult varchar(8) = 'Failure' output,
@strErrorDesc varchar(512) = 'SP Not Executed' output,
@strVFEID varchar(16),
@strDescription varchar(64),
@strVFEVirtualRoot varchar(255),
@strVFEPhysicalRoot varchar(255),
@strAuditPath varchar(255),
@strDefaultBranding varchar(16),
@strIPAddress varchar(23)
as
declare @strStep varchar(32)
declare @trancount int

Set XACT_ABORT ON
set @trancount = @@trancount
set @strStep = 'Start of Stored Proc'

if (@trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @strDescription,
VFEVIRTUALROOT = @strVFEVirtualRoot,
VFEPHYSICALROOT = @strVFEPhysicalRoot,
AUDITPATH = @strAuditPath,
DEFAULTBRANDING = @strDefaultBranding,
IPADDRESS = @strIPAddress
where
VFEID = @strVFEID;

set @strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @strDescription,
VFEVIRTUALROOT = @strVFEVirtualRoot,
VFEPHYSICALROOT = @strVFEPhysicalRoot,
AUDITPATH = @strAuditPath,
DEFAULTBRANDING = @strDefaultBranding,
IPADDRESS = @strIPAddress
where
VFEID = @strVFEID

/* end insert sp code here */

if (@@error <> 0)
begin
rollback tran mytran
set @strResult = 'Failure'
set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Error
return -1969
end
else
begin
set @strResult = 'Success'
set @strErrorDesc = ''
end
-- commit tran if we started it

if (@trancount = 0)
commit tran

return 0
 
 
 

BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

Post by Simon Haye » Wed, 24 Sep 2003 04:43:02


Since you're doing an UPDATE on the remote server, the two are equivalent -
MSSQL will promote the local transaction to a distributed one automatically.
This doesn't necessarily happen for executing stored procedures remotely,
though - in that case you do need to use BEGIN DISTRIBUTED TRAN, or set
'remote proc trans' on for the server, which will make it automatic for
procedure calls also.

Simon