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 system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Red
make sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> have
> calls
>
|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:

> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
sql

remote system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Redmake sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > I want to be able to have a stored procedure on server A to call a
> > stored procedure on Server B. I have my stored procedures in place. I
> have
> > used Linked servers on server A to define Server B so, that server A can
> > talk to server B. However I seem to have missed something. This is the
> > message that I get:
> > Could not find stored procedure 'sp_tt_load'.(42000,2812)
> > Procedure(sp_tt_dumpandload_for_standby).
> >
> >
> > sp_tt_dumpandload_for_standby (server A) does a database dump and then
> calls
> > sp_tt_load ( server B). Server B will load the dump onto it's standby
> > database.
> >
> > TIA for all the help.
> >
> > Red
> >
> >
>|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>

Remote Stored Proc Call

I'm calling this from another sql server...
I created a linked server... and want to restore database backups on the other box...
The restore script runs fine when ran locally but fails with the message below when calling it remotely

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.

CREATE PROCEDURE usp_restore_database_backups AS

RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

WAITFOR DELAY '00:00:05'

EXEC sp_dboption 'BesMgmt', 'single user', true
GO

I have set it to read only dbo only ... single user... still get the same message...
does anyone have any suggestions...try executing...

ALTER DATABASE <MyDB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

or

ALTER DATABASE <MyDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

before the restore statement and drop the sp_dboption.|||thanks Thrasymachus
I'm still getting the same error:
Executed as user: NFCU\sqlsvc. RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101). The step failed.|||post the script you are currently using and try running sp_who when it fails to see what connections are in use. Also when you execute this script in the QA are you connected to the database you are trying to restore? The database selected from the dropdown should not be the database you are trying to restore. If everything is OK with your script I suspect this is the case.|||The error does not tally with this but just belt and braces.

If you run
EXEC sp_helpserver

do you see RPC & RPC out in the status field?|||When I set the database manually into single user mode I get a different error when trying to restore the database... this is the error that comes from when the database is in single user mode....
"Executed as user: NFCU\sqlsvc. Cannot open database requested in login 'BESMgmt'. Login fails. [SQLSTATE 42000] (Error 4060). The step failed."

CREATE PROCEDURE usp_restore_database_backups AS

/*
declare @.x varchar(255)
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('BesMgmt')
exec (@.x)
*/

ALTER DATABASE BesMgmt SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

WAITFOR DELAY '00:00:05'

--EXEC sp_dboption 'BesMgmt', 'single user', true
GO|||The command is within a job...
the sql agent service is ran under a domain user acount...

Remote Procedure Call Failed

Hi,
We are running IIS 4 under NT Server 4.0 Server, Serice pack 3; SQL
Server 6.5, Service Pack 3 and ASP 2.0, for our development databases.
We recently upgraded to IIS 4 and since then we keep getting thid
Remote Procedure error.
The exact error reads:
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.
The next message that comes up is:
Server Application Error
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.HTTP/1.1 500 Server Error Server:
Microsoft-IIS/4.0 Date: Mon, 29 Mar 1999 22:09:57 GMT Connection:
close Content-Type: text/html Content-Length: 93 The remote procedure
call failed.
We keep rebooting the server but this error persists. I can't find
anything that points to what can be causing this.
Has anyone else come across this problem?
Thanx in advance,
-=lata=-Lata,
1. Does this error happen only on the IIS? At the time of error, are you
able to use Query analyser and enterpirse manager and other client
connections to connect to sql server?
2. When you say you rebooted the server, is it the IIS server or the sQL
Server or the Windows server?
If other sql server applications and client connections are running fine,
then it could be an IIS issue.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Remote Procedure Call Failed

Hi,
We are running IIS 4 under NT Server 4.0 Server, Serice pack 3; SQL
Server 6.5, Service Pack 3 and ASP 2.0, for our development databases.
We recently upgraded to IIS 4 and since then we keep getting thid
Remote Procedure error.
The exact error reads:
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.
The next message that comes up is:
Server Application Error
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.HTTP/1.1 500 Server Error Server:
Microsoft-IIS/4.0 Date: Mon, 29 Mar 1999 22:09:57 GMT Connection:
close Content-Type: text/html Content-Length: 93 The remote procedure
call failed.
We keep rebooting the server but this error persists. I can't find
anything that points to what can be causing this.
Has anyone else come across this problem?
Thanx in advance,
-=lata=-
Lata,
1. Does this error happen only on the IIS? At the time of error, are you
able to use Query analyser and enterpirse manager and other client
connections to connect to sql server?
2. When you say you rebooted the server, is it the IIS server or the sQL
Server or the Windows server?
If other sql server applications and client connections are running fine,
then it could be an IIS issue.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Remote procedure Call

The duplicacion is MErge.
when sinchronize this error is show:
Error calling rempote procedure
Please Help!
Gracias
Gabriel
Pizarro ,
please could you post up the complete error message.
Thanks,
Paul Ibison