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
