BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Steve Thorpe
    #1

    BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

    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),
    @strVFEVirtualR oot varchar(255),
    @strVFEPhysical Root varchar(255),
    @strAuditPath varchar(255),
    @strDefaultBran ding 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 = @strVFEVirtualR oot,
    VFEPHYSICALROOT = @strVFEPhysical Root,
    AUDITPATH = @strAuditPath,
    DEFAULTBRANDING = @strDefaultBran ding,
    IPADDRESS = @strIPAddress
    where
    VFEID = @strVFEID;

    set @strStep = 'Write VFE to PSM'

    update
    ACSPSM.psmprim. psm.VFECONFIG
    set
    DESCRIPTION = @strDescription ,
    VFEVIRTUALROOT = @strVFEVirtualR oot,
    VFEPHYSICALROOT = @strVFEPhysical Root,
    AUDITPATH = @strAuditPath,
    DEFAULTBRANDING = @strDefaultBran ding,
    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



    Tags: mssql transaction
    • Simon Hayes
      #2
      Re: BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION


      "Steve Thorpe" <stephenthorpe@ nospam.hotmail. com> wrote in message
      news:bkn3j2$2om $1@sparta.btint ernet.com...[color=blue]
      > 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),
      > @strVFEVirtualR oot varchar(255),
      > @strVFEPhysical Root varchar(255),
      > @strAuditPath varchar(255),
      > @strDefaultBran ding 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 = @strVFEVirtualR oot,
      > VFEPHYSICALROOT = @strVFEPhysical Root,
      > AUDITPATH = @strAuditPath,
      > DEFAULTBRANDING = @strDefaultBran ding,
      > IPADDRESS = @strIPAddress
      > where
      > VFEID = @strVFEID;
      >
      > set @strStep = 'Write VFE to PSM'
      >
      > update
      > ACSPSM.psmprim. psm.VFECONFIG
      > set
      > DESCRIPTION = @strDescription ,
      > VFEVIRTUALROOT = @strVFEVirtualR oot,
      > VFEPHYSICALROOT = @strVFEPhysical Root,
      > AUDITPATH = @strAuditPath,
      > DEFAULTBRANDING = @strDefaultBran ding,
      > 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
      >
      >
      >[/color]

      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


        Comment

        Previous template Next
        Working...