"Steve Thorpe" <st***********@nospam.hotmail.com> wrote in message
news:bk**********@sparta.btinternet.com...
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
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
