Remotely Executing a Stored Proc

I have an ASP page that executes a stored procedure on the local SQL Server instance, but I need that stored proc to execute another stored proc on a remote server. Basically, proc A on server 1 executes and passes a parameter to proc B on server 2. Is there a way to do this without linking server 2?
TIAPlease take a look at the linked server functionality. This allows you to execute a distributed query or call a remote stored procedure.

Remote Stored Procedures

I have a stored proc on a server called lax-dd and the
stored proc name is test. Both servers are in the same
domain and the sa passwords are the same on both servers.
I in query analyzer run exec lax-dd.dbname.dbo.test.
I get errors it doesn't understand the dash(-) in the
servername. I've tried placing the string in quot and
have problems finding the store proc...
Any ideas?
When you get errors and are having problems troubleshooting
those, it's always a good idea to post the error numbers and
the exact error messages.
Anyway...I'm guessing the problem is that you need to set
up a linked server to the other remote SQL Server. See the
books online topic: OLE DB Provider for SQL Server
You can also find more information in books online under
sp_addlinkedserver
-Sue
On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
<jay_bukstein@.hotmail.com> wrote:

>I have a stored proc on a server called lax-dd and the
>stored proc name is test. Both servers are in the same
>domain and the sa passwords are the same on both servers.
>I in query analyzer run exec lax-dd.dbname.dbo.test.
>I get errors it doesn't understand the dash(-) in the
>servername. I've tried placing the string in quot and
>have problems finding the store proc...
>Any ideas?
|||No, in SQL 2000, This is Remote Server, not a linked
server, it looks like the difference is a remote server
is only for other MS SQL server's using native SQL
Drivers where link servers a OLE DB providers, and there
is no error number associated with it.
I also had no trouble define the Remote server, I just
can't execute a remote storder Procedure using the
following Query analyzer command
exec lax-dd.dbname.dbo.test.
Any other ideas.

>--Original Message--
>When you get errors and are having problems
troubleshooting
>those, it's always a good idea to post the error numbers
and
>the exact error messages.
>Anyway...I'm guessing the problem is that you need to
set
>up a linked server to the other remote SQL Server. See
the[vbcol=seagreen]
>books online topic: OLE DB Provider for SQL Server
>You can also find more information in books online under
>sp_addlinkedserver
>-Sue
>On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
><jay_bukstein@.hotmail.com> wrote:
servers.
>.
>
|||One of the biggest differences is that remote servers are for
backwards compatability. You should be using linked servers if this is
to access a SQL Server that is higher than version 6.5.
If there is no error message and no error number, how do you know you
are getting errors? If you actually do have error messages, please
post the exact error message.
And what steps did you take to set this up as a remote server?
-Sue
On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
<jay_bukstein@.hotmail.com> wrote:
[vbcol=seagreen]
>No, in SQL 2000, This is Remote Server, not a linked
>server, it looks like the difference is a remote server
>is only for other MS SQL server's using native SQL
>Drivers where link servers a OLE DB providers, and there
>is no error number associated with it.
>I also had no trouble define the Remote server, I just
>can't execute a remote storder Procedure using the
>following Query analyzer command
>exec lax-dd.dbname.dbo.test.
>Any other ideas.
>
>troubleshooting
>and
>set
>the
>servers.
|||In a query window I receive a message that it can't find
the store procedure. I can run the store procedure in
query analyzer when logged in directly on that server.
I've even tryed running system store procedures like
sp_helpdb, or sp_who and get the same message 'can't find
stored procedure.'
Too also mention that I can't create these as Link
servers because I have SQL replication running.
Replication creates the other servers as remote servers.

>--Original Message--
>One of the biggest differences is that remote servers
are for
>backwards compatability. You should be using linked
servers if this is
>to access a SQL Server that is higher than version 6.5.
>If there is no error message and no error number, how do
you know you
>are getting errors? If you actually do have error
messages, please
>post the exact error message.
>And what steps did you take to set this up as a remote
server?[vbcol=seagreen]
>-Sue
>On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
><jay_bukstein@.hotmail.com> wrote:
there[vbcol=seagreen]
numbers[vbcol=seagreen]
under[vbcol=seagreen]
the[vbcol=seagreen]
same[vbcol=seagreen]
and
>.
>
|||I had the problem myself.
Call it through
EXEC [<ServerName>].[<DBName>].[<Owner>].[<ObjectName>]
(i.e., add [ ] on the server name).
After a whole day of testing this trivial solution came up. duh
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Remote Stored Procedures

I have a stored proc on a server called lax-dd and the
stored proc name is test. Both servers are in the same
domain and the sa passwords are the same on both servers.
I in query analyzer run exec lax-dd.dbname.dbo.test.
I get errors it doesn't understand the dash(-) in the
servername. I've tried placing the string in quot and
have problems finding the store proc...
Any ideas?When you get errors and are having problems troubleshooting
those, it's always a good idea to post the error numbers and
the exact error messages.
Anyway...I'm guessing the problem is that you need to set
up a linked server to the other remote SQL Server. See the
books online topic: OLE DB Provider for SQL Server
You can also find more information in books online under
sp_addlinkedserver
-Sue
On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
<jay_bukstein@.hotmail.com> wrote:

>I have a stored proc on a server called lax-dd and the
>stored proc name is test. Both servers are in the same
>domain and the sa passwords are the same on both servers.
>I in query analyzer run exec lax-dd.dbname.dbo.test.
>I get errors it doesn't understand the dash(-) in the
>servername. I've tried placing the string in quot and
>have problems finding the store proc...
>Any ideas?|||No, in SQL 2000, This is Remote Server, not a linked
server, it looks like the difference is a remote server
is only for other MS SQL server's using native SQL
Drivers where link servers a OLE DB providers, and there
is no error number associated with it.
I also had no trouble define the Remote server, I just
can't execute a remote storder Procedure using the
following Query analyzer command
exec lax-dd.dbname.dbo.test.
Any other ideas.

>--Original Message--
>When you get errors and are having problems
troubleshooting
>those, it's always a good idea to post the error numbers
and
>the exact error messages.
>Anyway...I'm guessing the problem is that you need to
set
>up a linked server to the other remote SQL Server. See
the
>books online topic: OLE DB Provider for SQL Server
>You can also find more information in books online under
>sp_addlinkedserver
>-Sue
>On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
><jay_bukstein@.hotmail.com> wrote:
>
servers.[vbcol=seagreen]
>.
>|||One of the biggest differences is that remote servers are for
backwards compatability. You should be using linked servers if this is
to access a SQL Server that is higher than version 6.5.
If there is no error message and no error number, how do you know you
are getting errors? If you actually do have error messages, please
post the exact error message.
And what steps did you take to set this up as a remote server?
-Sue
On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
<jay_bukstein@.hotmail.com> wrote:
[vbcol=seagreen]
>No, in SQL 2000, This is Remote Server, not a linked
>server, it looks like the difference is a remote server
>is only for other MS SQL server's using native SQL
>Drivers where link servers a OLE DB providers, and there
>is no error number associated with it.
>I also had no trouble define the Remote server, I just
>can't execute a remote storder Procedure using the
>following Query analyzer command
>exec lax-dd.dbname.dbo.test.
>Any other ideas.
>
>
>troubleshooting
>and
>set
>the
>servers.|||In a query window I receive a message that it can't find
the store procedure. I can run the store procedure in
query analyzer when logged in directly on that server.
I've even tryed running system store procedures like
sp_helpdb, or sp_who and get the same message 'can't find
stored procedure.'
Too also mention that I can't create these as Link
servers because I have SQL replication running.
Replication creates the other servers as remote servers.

>--Original Message--
>One of the biggest differences is that remote servers
are for
>backwards compatability. You should be using linked
servers if this is
>to access a SQL Server that is higher than version 6.5.
>If there is no error message and no error number, how do
you know you
>are getting errors? If you actually do have error
messages, please
>post the exact error message.
>And what steps did you take to set this up as a remote
server?
>-Sue
>On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
><jay_bukstein@.hotmail.com> wrote:
>
there[vbcol=seagreen]
numbers[vbcol=seagreen]
under[vbcol=seagreen]
the[vbcol=seagreen]
same[vbcol=seagreen]
and[vbcol=seagreen]
>.
>|||I had the problem myself.
Call it through
EXEC [<ServerName>].[<DBName>].[<Owner>].[<ObjectName>]
(i.e., add [ ] on the server name).
After a whole day of testing this trivial solution came up. duh
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.

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 scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc o
n
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same
,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
--
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:
> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> > I've got an interesting situation. I have two SQL 2000 servers and a proc
> > on
> > one that updates data on the other. If I use literal values in the where
> > clause of the update statement then the query plan uses a "remote query"
> > to
> > do the update. If I use variables then it uses a "remote scan" and
> > filters
> > locally. Obviously that takes too long.
> >
> > Here is some sample script.
> > -- This uses a remote query
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = 'ROB'
> > WHERE OrderNumber = 12345
> >
> > -- This uses a remote scan
> > DECLARE @.Salesperson CHAR(3)
> > DECLARE @.OrderNumber INT
> > SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> >
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > -- This uses a remote query; same table and index structure; linked server
> > defined the same
> > UPDATE [Server3].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > I can't figure out any reason why it does it that way. To make things
> > more
> > odd, that proc updates a couple other servers in the same way for the same
> > table and those use remote queries. The linked servers are defined the
> > same,
> > the table structure and indexes are the same.
> >
> > Does anyone have any idea why it would work this way?
> >
> > Thanks.
> >
> > Dale.
> >
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.
Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>
|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote queries

I nee to execute a proc on few servers and store the results on a
central database in a table. How can I do this without writing the DTS
packages?
I tried the Sybase way ( Server...sp_myproc) and it did not work. I
added the linked server before trying this. Any pointers'
ThanksIt 'should' work by using the Four part name, e.g., ServerName.Database.Sche
ma.Object.
For Example:
Execute MyServer.Northwind.dbo.CustOrderHist
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BS" <minimyme@.yahoo.com> wrote in message news:1154381608.618328.125960@.s13g2000cwa.googleg
roups.com...
>I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
>
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
>
> Thanks
>|||Hi
You could do this in a single DTS and use a Dynamic properties task to
change source server, database and table information.
John
"BS" wrote:

> I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
> Thanks
>|||Here is the SQL that I'm trying to execute and the columns that the
proc returns and the table structute that I want the data to be
inserted into.
create table DBSpace (
ServerName nvarchar(20) null,
DBName nvarchar(20) null,
SegType nvarchar(20) null,
AllocatedSpace int null,
UsedSpace int null,
[Free%] int null )
insert into DBSpace
execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
If I try to execute this, I get the error as below
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
Remove the quotes from around
'WHP-MactiveTest.master.dbo.sp_db_space_used'
Roy Harvey
Beaon Falls, CT
On 1 Aug 2006 12:16:38 -0700, "BS" <minimyme@.yahoo.com> wrote:

>Here is the SQL that I'm trying to execute and the columns that the
>proc returns and the table structute that I want the data to be
>inserted into.
>create table DBSpace (
>ServerName nvarchar(20) null,
>DBName nvarchar(20) null,
>SegType nvarchar(20) null,
>AllocatedSpace int null,
>UsedSpace int null,
>[Free%] int null )
>
>insert into DBSpace
>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>If I try to execute this, I get the error as below
>Msg 102, Level 15, State 1, Line 2
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||Two problems.
1. No should NOT be Quotes around the object name
2. In order to use a server name that includes a dash [-] or instance na
me that includes a slash [\], you need to surround ONLY the server name
with square brackets.
E.g.,
EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BS" <minimyme@.yahoo.com> wrote in message news:1154459798.626453.71360@.m73g2000cwd.googlegr
oups.com...
> Here is the SQL that I'm trying to execute and the columns that the
> proc returns and the table structute that I want the data to be
> inserted into.
>
> create table DBSpace (
> ServerName nvarchar(20) null,
> DBName nvarchar(20) null,
> SegType nvarchar(20) null,
> AllocatedSpace int null,
> UsedSpace int null,
> [Free%] int null )
>
>
> insert into DBSpace
> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>
> If I try to execute this, I get the error as below
>
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
>

Remote queries

I nee to execute a proc on few servers and store the results on a
central database in a table. How can I do this without writing the DTS
packages?
I tried the Sybase way ( Server...sp_myproc) and it did not work. I
added the linked server before trying this. Any pointers'
ThanksThis is a multi-part message in MIME format.
--=_NextPart_000_0078_01C6B4B9.9CC3E0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It 'should' work by using the Four part name, e.g., =ServerName.Database.Schema.Object.
For Example:
Execute MyServer.Northwind.dbo.CustOrderHist
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"BS" <minimyme@.yahoo.com> wrote in message =news:1154381608.618328.125960@.s13g2000cwa.googlegroups.com...
>I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
> > I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
> > Thanks
>
--=_NextPart_000_0078_01C6B4B9.9CC3E0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It 'should' work by using the Four part =name, e.g., ServerName.Database.Schema.Object.
For Example:
Execute MyServer.Northwind.dbo.CustOrderHist
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"BS" =wrote in message news:1154381608.618328.125960@.s13g2000cwa.googlegroups.com...>I =nee to execute a proc on few servers and store the results on a> central database =in a table. How can I do this without writing the DTS> =packages?> > I tried the Sybase way ( Server...sp_myproc) and it did not =work. I> added the linked server before trying this. Any =pointers'> > Thanks>

---

--=_NextPart_000_0078_01C6B4B9.9CC3E0B0--|||Hi
You could do this in a single DTS and use a Dynamic properties task to
change source server, database and table information.
John
"BS" wrote:
> I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
> Thanks
>|||Here is the SQL that I'm trying to execute and the columns that the
proc returns and the table structute that I want the data to be
inserted into.
create table DBSpace (
ServerName nvarchar(20) null,
DBName nvarchar(20) null,
SegType nvarchar(20) null,
AllocatedSpace int null,
UsedSpace int null,
[Free%] int null )
insert into DBSpace
execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
If I try to execute this, I get the error as below
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
Remove the quotes from around
'WHP-MactiveTest.master.dbo.sp_db_space_used'
Roy Harvey
Beaon Falls, CT
On 1 Aug 2006 12:16:38 -0700, "BS" <minimyme@.yahoo.com> wrote:
>Here is the SQL that I'm trying to execute and the columns that the
>proc returns and the table structute that I want the data to be
>inserted into.
>create table DBSpace (
>ServerName nvarchar(20) null,
>DBName nvarchar(20) null,
>SegType nvarchar(20) null,
>AllocatedSpace int null,
>UsedSpace int null,
>[Free%] int null )
>
>insert into DBSpace
>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>If I try to execute this, I get the error as below
>Msg 102, Level 15, State 1, Line 2
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||This is a multi-part message in MIME format.
--=_NextPart_000_03A4_01C6B567.F7543DF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Two problems.
1. No should NOT be Quotes around the object name
2. In order to use a server name that includes a dash [-] or instance =name that includes a slash [\], you need to surround ONLY the server =name with square brackets.
E.g.,
EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"BS" <minimyme@.yahoo.com> wrote in message =news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com...
> Here is the SQL that I'm trying to execute and the columns that the
> proc returns and the table structute that I want the data to be
> inserted into.
> > create table DBSpace (
> ServerName nvarchar(20) null,
> DBName nvarchar(20) null,
> SegType nvarchar(20) null,
> AllocatedSpace int null,
> UsedSpace int null,
> [Free%] int null )
> > > insert into DBSpace
> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
> > If I try to execute this, I get the error as below
> > Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
>
--=_NextPart_000_03A4_01C6B567.F7543DF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Two problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>1. No should NOT be Quotes around the =object name</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2. In order to use a server name that =includes a dash [-] or instance name that includes a slash [\], you need to =surround ONLY the server name with square brackets.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>E.g.,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, =Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Most good judgment comes from =experience. <BR>Most experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>"BS" <</FONT><A href=3D"mailto:minimyme@.yahoo.com"><FONT face=3DArial size=3D2>minimyme@.yahoo.com</FONT></A><FONT face=3DArial size=3D2>> =wrote in message </FONT><A href=3D"news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com"><FONT =
face=3DArial size=3D2>news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com</FONT>=</A><FONT face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> =Here is the SQL that I'm trying to execute and the columns that the<BR>> proc returns =and the table structute that I want the data to be<BR>> inserted =into.<BR>> <BR>> create table DBSpace (<BR>> ServerName nvarchar(20) =null,<BR>> DBName nvarchar(20) null,<BR>> SegType nvarchar(20) null,<BR>> AllocatedSpace int null,<BR>> UsedSpace int null,<BR>> [Free%] int =null )<BR>> <BR>> <BR>> insert into DBSpace<BR>> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'<BR>> <BR>> If I try =to execute this, I get the error as below<BR>> <BR>> Msg 102, Level =15, State 1, Line 2<BR>> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.<BR>></FONT></BODY></HTM=L>
--=_NextPart_000_03A4_01C6B567.F7543DF0--

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posti
ng.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google. com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry
|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>