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"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkn3j2$2om$1@.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
No comments:
Post a Comment