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),
@.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

Before Delete Trigger?

I have a database that will be used as the back end of a distributed application that holds information based on application users. The application needs to log on and see if there are updates for the user. My current thoughts on this are that the application will log in and check a Date column named [modified] in the users table (I am not worried about what individual changes have occured, but more if anything has changed). To implement this I have now put Insert, Update triggers that use the tables relationships to track which users need to be updated on the tables that need to be watched... they look something like this:

CREATE TRIGGER MOD_UP_INS_GROUPS
ON dbo.Groups
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @.IDVar1 as int
SET @.IDVar1 = (SELECT GroupID FROM inserted)
UPDATE Users
SET Modified = GetDate()
WHERE (AccountName IN
(SELECT DISTINCT dbo.Users.AccountName
FROM dbo.Groups INNER JOIN
dbo.GroupUserDetail ON dbo.Groups.GroupID = dbo.GroupUserDetail.GroupID INNER JOIN
dbo.Users ON dbo.GroupUserDetail.AccountName = dbo.Users.AccountName
WHERE (dbo.Groups.GroupID = @.IDVar1)))

This appears to be working great... however the Delete Trigger is where my problems start... I can not use the above trigger (with deleted in place of inserted) because it appears the delete action takes place prior to the Delete Trigger and with the referential deletes, etc. The path to the user is lost before I can track it with the delete trigger. Is there a way to make a BEFORE DELETE TRIGGER... or any other thoughts would be helpfull.

Thank You,
KentDo Instead of Trigger.|||Not sure I'm following...but you can code an INSTEAD OF trigger...look it up in BOL...|||I tried a Instead of Trigger but I had the following error:

Cannot ALTER INSTEAD OF DELETE or UPDATE TRIGGER 'MY TRIGGER NAME' on table 'dbo.Group' because the table has a FOREIGN KEY with cascaded DELETE or UPDATE.

Doing a little reading I have found I can't define this trigger on tables with foreign key relationships with cascading deletes.

The other problem I thought of with this type of trigger... is how do you let an outside programs sql delete requests continue with this trigger?|||Well...can you simply explain what your goal is, in business terms...

I'm having a hard time seeing what you're trying to do..

Also, you got other problems

SET @.IDVar1 = (SELECT GroupID FROM inserted)

You do know that inserted may have many rows...so doing that will give you the last value in the result set...|||First of all thanks for your replies:

The Database is the backend of a Client-Server Application that assigns Startup Scripts for Company Programs to individual users within the company (Based on Windows Login Names that are stored in the users table). It does this in a method similar to the Windows Server Environment where you assign individual Company Programs to a Group then assign a group to a user or a user to a group.

The front end that the users see needs to be able to connect to the database and determine if anything has changed, and if so, update itself with the new settings whether it be the user has been added\deleted from a group or if an actual Company Program Startup Script has changed.

My attempted solution to this problem was to create a modified column in the users table. When a table is modified that effects a user or users the modified column for the user or users in question would be updated with the current date (getdate()). The front end then accessess the database and compares it's last updated date with the date in the users.modified column to determine if it needs to update. With the update, Insert Triggers I have been able to accomplish this very nice. However, the delete trigger causes problems because lets say a group is deleted... the group is deleted then referential updates delete the users who where assigned to that group in a groupdetails table then I am unable to track which users need to be modified...

The front end does not contain a database... but rather stores items in an ini file and the registry, therefore replication is not an option. The other thing is clients are not always connected to the network so the settings are stored on the local machine for the individual users.

I hope this is clearer... thanks again.