By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

P: n/a
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

Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.