remote triggers on update

I don't think this is a problem, but I am curious as to why this is happening.

I have two machines, lets call them machine A and machine B.

On machine A I have a job that runs. It has 2 steps. The first step runs replication (no problem). The 2nd step (on success of step 1) updates a row in a table via a linked server on Machine B.

So, step 2 is basically:
update b.dbname.dbo.tablename set success=1 where id=1

On machine B, the table that is updated has an update trigger on it that checks the success flag. If the success flag is 1 then it runs a stored procedure that does a bunch of stuff on machine B (this stuff takes about 10 minutes).

This all works great, however if I check the job history on Machine A, it says that step 2 (the update) takes 10 minutes.

Why is this? Shouldn't the update statement be instantaneous? It seems like the update waits for the trigger to fire before returning. Is that correct?

Like I said, its not a problem, but I was just curious as to why the job on machine A waits for the trigger on machine B to complete.

Hope this makes sense. Thanks

JeffThe trigger is part of the update, and the transaction is not complete until the trigger completes.

blindman

remote instances

config - 2 machines in a workgroup, service running under local system account. Tried the following config. from Rushi. Running profiles on both machines. on source - starts the conversation, and a send. then get an event subclass 2-remote at a periodic interval (in a loop), no activty on the other end. any suggestions
In master:
1. Create master key for database 'master'.
2. Create certificate for user 'dbo' in database 'master'.
3. Create service broker endpoint using certificate created above.
4. Grant connect on above endpoint to the login 'Public'.

In your database:
1. Create master key.
2. Grant send on target service to user 'public'.
3. Create routes to initiator services on other servers.
4. Create routes to target services on other servers.

Now begin dialog with encryption = off and send.
It could be an incorrect address used in the route (a typo in the DNS name), a port number mismatch between the route and the endpoint configuration, a port being blocked by personal firewall, IPSEC blocking a TCP connection etc. What is the value of transmission_status column in the sys.transmission_queue view in the initiatr database?

Have you tried using the service listing stored procs from Rushi's site? They make this kind of tasks much easier.

Regards,
~ Remus|||The service listings stored procs are now available on our CodeGallery:

http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319
Later,

Rushi

Remote Database updates take long time to complete

Hi
I have have two linked SQL Servers and I am trying to get remote writes
working correctly (fast).

I have configured the DB link on both machines to:
Point at each others DB.
I have security set up to map each others server logins
and Server Options: Collation Compatible, Data Access, RPC, RPC Out, Use
Remote Collation all checked

My problem is that when a SP performs

Begin Transaction

Update Local Table

Update Remote Table

Commit Tran

It takes several seconds to complete. (about 7 seconds not acceptable to
us)
This is due to the remote update - how can I improve the response time?

example of a stored procedures that takes time

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 0Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> It takes several seconds to complete. (about 7 seconds not acceptable to
> us)
> This is due to the remote update - how can I improve the response time?

Have you tried moving the updates of the remote table to a stored
procedures that runs on the remote server, and then call that stored
procedure remotely? I would expect that to reduce the ping-pong over
the network.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have found the cause of the problem:

'For linked server DELETEs or UPDATEs, SQL Server retrieves data from the
table, performs any filtering that is necessary, and then performs the
deletes or updates through the OLEDB rowset. This processing can result in a
round-trip to the remote server for each row that is to be deleted or
updated. '

The solution is:

EXEC remserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

EXEC localserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94004CF4CD0FYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > It takes several seconds to complete. (about 7 seconds not acceptable
to
> > us)
> > This is due to the remote update - how can I improve the response time?
> Have you tried moving the updates of the remote table to a stored
> procedures that runs on the remote server, and then call that stored
> procedure remotely? I would expect that to reduce the ping-pong over
> the network.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp