Remoting timeout when calling SSIS package execute from a windows service

When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.

This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...

my code looks something like this:
<code>
dim foo as Microsoft.SqlServer.Dts.Runtime.Application
mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents)
results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing)
</code>

<error>
A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll
Exception in: frmMyForm.DoImports
Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server.
</error>

oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.

It also runs fine if the package can complete in under six minutes.

Any suggestions?

Mark

To give a little update, I'm still having this problem despite exhausting works around I've come up with.

* I've threaded the call to package.Execute, checking its status in a while loop every 60 seconds until it finishes. Still encounter the exception. This behaves as though it should work. The status and execution appear to work asynchronously.

* I've threaded the call to my service, calling my own status function which tests the status of shared MyPackage, every 60 seconds. Still encounter the remoting exception after ~6 minutes.

Every piece is busy making requests through the whole chain of accessible interactions frequently enough to maintain all known remoting timeout constraints and the problem persists.

It confuses me why the same code works in a windows form, but does not work in a windows service. The exception is defiantly being thrown from within package.Execute. I can loop indefinitely within the service without calling package Execute. I can also catch and disregard the exception and still provide status to the client within the service.

Looking for suggestions...

|||

SSIS does not use remoting. The exception is caused by remoting however, so you need to find out who introduced remoting? My ideas

1) you use remoting as link between client and server - then the exception is between the client and the service, which does not fit with the statement that it is thrown from within package.Execute and you can catch this exception (but did you try?)

2) you create multiple application domains in the service - if you do this, make sure you only use SSIS from default application domain. If you run the package from non-default application domain, some SSIS objects can be created in that app domain, but some in default app domain (a lot of SSIS is native code, and it is not aware of app-domains), which may cause failures like this one.

Remoting timeout when calling SSIS package execute from a windows service

When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.

This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...

my code looks something like this:
<code>
dim foo as Microsoft.SqlServer.Dts.Runtime.Application
mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents)
results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing)
</code>

<error>
A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll
Exception in: frmMyForm.DoImports
Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server.
</error>

oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.

It also runs fine if the package can complete in under six minutes.

Any suggestions?

Mark

To give a little update, I'm still having this problem despite exhausting works around I've come up with.

* I've threaded the call to package.Execute, checking its status in a while loop every 60 seconds until it finishes. Still encounter the exception. This behaves as though it should work. The status and execution appear to work asynchronously.

* I've threaded the call to my service, calling my own status function which tests the status of shared MyPackage, every 60 seconds. Still encounter the remoting exception after ~6 minutes.

Every piece is busy making requests through the whole chain of accessible interactions frequently enough to maintain all known remoting timeout constraints and the problem persists.

It confuses me why the same code works in a windows form, but does not work in a windows service. The exception is defiantly being thrown from within package.Execute. I can loop indefinitely within the service without calling package Execute. I can also catch and disregard the exception and still provide status to the client within the service.

Looking for suggestions...

|||

SSIS does not use remoting. The exception is caused by remoting however, so you need to find out who introduced remoting? My ideas

1) you use remoting as link between client and server - then the exception is between the client and the service, which does not fit with the statement that it is thrown from within package.Execute and you can catch this exception (but did you try?)

2) you create multiple application domains in the service - if you do this, make sure you only use SSIS from default application domain. If you run the package from non-default application domain, some SSIS objects can be created in that app domain, but some in default app domain (a lot of SSIS is native code, and it is not aware of app-domains), which may cause failures like this one.

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 Update takes forever

I have a SQL 2005 & SQL 2000 server. I am attempting to execute a simple update statement, something that looks like:

update AD

set AD.SomeDate = getdate()

from [ServerX].DB.dbo.Table

where ColumnX = 'X'

ServerX is the SQL 2000 box.

ServerY is the SQL 2005 box. Server Y is where this statement is invoked from. (Not shown in statement).

I have a linked server set up.

When executed from the 2000 box, it runs in < 1 second.

When both environments are 2005 to 2005, it takes less than < 1 second.

Try using OPENQUERY to perform the update - I've provided an (untested) example below.

http://msdn2.microsoft.com/en-us/library/ms188427.aspx

Chris

Code Snippet

--Note: Untested so you may need to play around with the syntax. Execute on Server Y.
UPDATE OPENQUERY(ServerX, 'SELECT SomeDate FROM DB.dbo.Table WHERE ColumnX = ''X''')
SET SomeDate = GETDATE()

Remote Stored Procedure exec from variable

Hello Folks,

I have a server that is linked to several other servers. I have been able to successfully execute a procedure manually to each from the common server by executing this:

exec server1.dbname.owner.procedure
go
exec server2.dbname.owner.procedure
go
exec server3.dbname.owner.procedure
go

While this is ok, I'd like to wrap this in a loop to execute the procedure but switch out the server name in each iteration (something like this):

while @.variable is not null
begin
select @.server = 'change name in loop'
select @.str = @.server+'.dbname.owner.procedure'
exec @.str
end

This unfortunately does not work. The execute is acting like the server portion of the name does not exist (defaulting to local). I have attempted to use the AT SERVERNAME syntax in a similar fashion and been unsuccessful.

Is there some way I could dynamically create the four part name and execute it?

Any assistance would be greatly appreciated.

Thanks, Mark

DECLARE @.server nvarchar(128)

DECLARE @.cmd nvarchar(1000)

SET @.server = 'MyServer'

SET @.cmd = 'EXEC ' + @.server + '.MyDatabase.MySchema.MySproc'

EXEC (@.cmd)

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remote
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUERY
but still takes ages to run.
Thanks
Open query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks
|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in
a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remot
e
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUER
Y
but still takes ages to run.
ThanksOpen query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

Remote server

Hi,

I have created 1 sqls server as a remote server in another sql server, so I can execute backup jobs on the backup server from the production server.

This was all working ok, but now everytime the backup is called I get an error message saying login falied for 'sa'.

I haven't changed anything and the remote logins look like they are set up correctly.

Any advise would be appreciatedMy guess would be that you applied sp3, and when it "hardened" the sa password on your slave server, things went amok. Open Query Analyzer on your master, and attempt to log into your slave as sa. If that breaks, you'll need to find (or change) the sa password for your slave, then fix the problem(s) (there may be more than one) in the remote login definitions.

-PatP|||Hi,

Did not apply sp3? they were both applied to both the servers before this error occured.

this is the kind thing that has stopped working

Run on server 1 as a job

exec Server2.msdb.dbo.sp_start_job @.job_name='Restore = Load Alpha AM'

Remote Query results

Folks,
Can anyone help me with getting results of a query that I'm trying to
execute on a linked server into a local table? I'm trying to avoid the
DTS package as I will have to create the DTS package on every single
linked server.
I have quite a few servers that I want to execute a proc on and store
the results of all of them into one central database table.
ThanksThis is a multi-part message in MIME format.
--000502000604040203080207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
BS wrote:
> Folks,
> Can anyone help me with getting results of a query that I'm trying to
> execute on a linked server into a local table? I'm trying to avoid the
> DTS package as I will have to create the DTS package on every single
> linked server.
> I have quite a few servers that I want to execute a proc on and store
> the results of all of them into one central database table.
> Thanks
>
Hi
There are as such no difference in inserting data from a linked server
into a local table than doing it with data from the local server. You
can use INSERT INTO or SELECT INTO. Since you don't tell anything about
the stored proc you want to run, it's difficult to to know what it
returns, but you can look up the syntax for INSERT INTO and SELECT..INTO
in Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--000502000604040203080207
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
BS wrote:
<blockquote
cite="mid1154381185.831914.162800@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Folks,
Can anyone help me with getting results of a query that I'm trying to
execute on a linked server into a local table? I'm trying to avoid the
DTS package as I will have to create the DTS package on every single
linked server.
I have quite a few servers that I want to execute a proc on and store
the results of all of them into one central database table.
Thanks
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
There are as such no difference in inserting data from a linked server
into a local table than doing it with data from the local server. You
can use INSERT INTO or SELECT INTO. Since you don't tell anything about
the stored proc you want to run, it's difficult to to know what it
returns, but you can look up the syntax for INSERT INTO and
SELECT..INTO in Books On Line.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--000502000604040203080207--

Remote Query Performance

I have a query that when executed against the host server, it completes in 1
1
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Window
s
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issu
e
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in
11
> seconds. When I execute the same query against a linked server it takes 1
00
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The results
et
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Wind
ows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance is
sue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating o
n
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:

> Brandon Lunt wrote:
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating
on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query pl
an
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:

> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>

Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?First, why are you using linked servers. In most cases this is not necessary
with RS. You can have multiple datasets against multiple data sources.
SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
I learned this while working with loading a datamart. If you must use linked
servers then you need to be using Openquery to use it. Do not use the 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?|||The report I am producing combines data from 3 different servers. I
typically use linked servers so I don't have to put usernames and passwords
into my query strings (not all users have access to all databases so I have
to use a different account). I have usually had good results with Linked
servers (slight performance hit but never this bad). The other linked query
(to the 3rd server) runs in about 2 seconds and returns approximately the
same number or results.
My confusion I guess is why does it not run the same as a query analyzer
client connecting to the server?
"Bruce L-C [MVP]" wrote:
> First, why are you using linked servers. In most cases this is not necessary
> with RS. You can have multiple datasets against multiple data sources.
> SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
> I learned this while working with loading a datamart. If you must use linked
> servers then you need to be using Openquery to use it. Do not use the 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
>
>|||Are you doing this in a stored procedure?
If not, I have seen people post about issues where the query plan created
when a query is executed from RS is different than from query analyzer. As I
said, I haven't seen this but I have seen posts about that.
If you are using a stored procedure then that would not be an issue. If not,
try moving this to a stored procedure and see if that helps.
I have thought of one other thing. Depending on the parameterization, I have
seen in 2000 based on the parameters and the where clause where SQL Server
will decide to bring over all the data from the remote table and process it
locally on the server rather than having the query executed remotely and
bringing back the result. Based on 11 seconds when hitting the server
directly going to 100 seconds, I bet that is what is happening. If you use
openquery this will not occur. With 4 part naming you have to really be
careful.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> The report I am producing combines data from 3 different servers. I
> typically use linked servers so I don't have to put usernames and
> passwords
> into my query strings (not all users have access to all databases so I
> have
> to use a different account). I have usually had good results with Linked
> servers (slight performance hit but never this bad). The other linked
> query
> (to the 3rd server) runs in about 2 seconds and returns approximately the
> same number or results.
> My confusion I guess is why does it not run the same as a query analyzer
> client connecting to the server?
> "Bruce L-C [MVP]" wrote:
>> First, why are you using linked servers. In most cases this is not
>> necessary
>> with RS. You can have multiple datasets against multiple data sources.
>> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> better.
>> I learned this while working with loading a datamart. If you must use
>> linked
>> servers then you need to be using Openquery to use it. Do not use the 4
>> part
>> naming.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >I have a query that when executed against the host server, it completes
>> >in
>> >11
>> > seconds. When I execute the same query against a linked server it
>> > takes
>> > 100
>> > seconds to run. I have checked the execution plan for the linked
>> > server
>> > query and it shows that 100% of the cost is the Remote Query. The
>> > resultset
>> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
>> > Windows
>> > 2000 OS.
>> >
>> > Since both queries ran from query analyzer on my workstation, why is
>> > there
>> > such a drastic difference in speed? Also, how do I fix the performance
>> > issue
>> > for the remote query so that it runs in 15 seconds or less?
>>|||Currently this is in query form. I tried the openquery and got the same
results as the 4 part linked query. I also tried the openrowset and the time
came down to 57 seconds, but nowhere near the 10-15 seconds I would expect.
Also, the problem with openquery is that I need to pass parameters used by
the query. I didn't see a way to get those dynamically into the query.
I went ahead and created the sp and executed that against the linked server
and it ran in 50 seconds. Better, but still not what I was expecting.
"Bruce L-C [MVP]" wrote:
> Are you doing this in a stored procedure?
> If not, I have seen people post about issues where the query plan created
> when a query is executed from RS is different than from query analyzer. As I
> said, I haven't seen this but I have seen posts about that.
> If you are using a stored procedure then that would not be an issue. If not,
> try moving this to a stored procedure and see if that helps.
> I have thought of one other thing. Depending on the parameterization, I have
> seen in 2000 based on the parameters and the where clause where SQL Server
> will decide to bring over all the data from the remote table and process it
> locally on the server rather than having the query executed remotely and
> bringing back the result. Based on 11 seconds when hitting the server
> directly going to 100 seconds, I bet that is what is happening. If you use
> openquery this will not occur. With 4 part naming you have to really be
> careful.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> > The report I am producing combines data from 3 different servers. I
> > typically use linked servers so I don't have to put usernames and
> > passwords
> > into my query strings (not all users have access to all databases so I
> > have
> > to use a different account). I have usually had good results with Linked
> > servers (slight performance hit but never this bad). The other linked
> > query
> > (to the 3rd server) runs in about 2 seconds and returns approximately the
> > same number or results.
> >
> > My confusion I guess is why does it not run the same as a query analyzer
> > client connecting to the server?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> First, why are you using linked servers. In most cases this is not
> >> necessary
> >> with RS. You can have multiple datasets against multiple data sources.
> >>
> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
> >> better.
> >> I learned this while working with loading a datamart. If you must use
> >> linked
> >> servers then you need to be using Openquery to use it. Do not use the 4
> >> part
> >> naming.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >> >I have a query that when executed against the host server, it completes
> >> >in
> >> >11
> >> > seconds. When I execute the same query against a linked server it
> >> > takes
> >> > 100
> >> > seconds to run. I have checked the execution plan for the linked
> >> > server
> >> > query and it shows that 100% of the cost is the Remote Query. The
> >> > resultset
> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> >> > Windows
> >> > 2000 OS.
> >> >
> >> > Since both queries ran from query analyzer on my workstation, why is
> >> > there
> >> > such a drastic difference in speed? Also, how do I fix the performance
> >> > issue
> >> > for the remote query so that it runs in 15 seconds or less?
> >>
> >>
> >>
>
>|||The only way to get a parameter to openquery (that I know of) is to
dynamically create the sql string. You end up having to do lots of messing
with single quotes.
In your stored procedure, are you able to insert into a temp table the
results from each individual query and then join the temp tables?
Also, just so you can see how it is done, here is a an example of
dynamically
select @.SQL = 'insert ' + @.TABLENAME + ' select * from
openquery(linkedservername,''' + 'SELECT * from ' + @.TABLENAME + ' where ' +
@.SYNCDATEFIELD + '> '' + convert(varchar(30),@.STARTDATE,9) + '' and '
+ @.SYNCDATEFIELD +' < '' + convert(varchar(30),@.ENDDATE,9) + '')'
execute (@.SQL)
Note all the single quotes mess. Not too friendly but it is the fastest way
to work with linked tables. In the above I am inserting into a real table
but you could easily have a temp table created that you insert into.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:EDE9A4A4-0992-4B9B-ACA3-2E6A0FB1645B@.microsoft.com...
> Currently this is in query form. I tried the openquery and got the same
> results as the 4 part linked query. I also tried the openrowset and the
> time
> came down to 57 seconds, but nowhere near the 10-15 seconds I would
> expect.
> Also, the problem with openquery is that I need to pass parameters used by
> the query. I didn't see a way to get those dynamically into the query.
> I went ahead and created the sp and executed that against the linked
> server
> and it ran in 50 seconds. Better, but still not what I was expecting.
> "Bruce L-C [MVP]" wrote:
>> Are you doing this in a stored procedure?
>> If not, I have seen people post about issues where the query plan created
>> when a query is executed from RS is different than from query analyzer.
>> As I
>> said, I haven't seen this but I have seen posts about that.
>> If you are using a stored procedure then that would not be an issue. If
>> not,
>> try moving this to a stored procedure and see if that helps.
>> I have thought of one other thing. Depending on the parameterization, I
>> have
>> seen in 2000 based on the parameters and the where clause where SQL
>> Server
>> will decide to bring over all the data from the remote table and process
>> it
>> locally on the server rather than having the query executed remotely and
>> bringing back the result. Based on 11 seconds when hitting the server
>> directly going to 100 seconds, I bet that is what is happening. If you
>> use
>> openquery this will not occur. With 4 part naming you have to really be
>> careful.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
>> > The report I am producing combines data from 3 different servers. I
>> > typically use linked servers so I don't have to put usernames and
>> > passwords
>> > into my query strings (not all users have access to all databases so I
>> > have
>> > to use a different account). I have usually had good results with
>> > Linked
>> > servers (slight performance hit but never this bad). The other linked
>> > query
>> > (to the 3rd server) runs in about 2 seconds and returns approximately
>> > the
>> > same number or results.
>> >
>> > My confusion I guess is why does it not run the same as a query
>> > analyzer
>> > client connecting to the server?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> First, why are you using linked servers. In most cases this is not
>> >> necessary
>> >> with RS. You can have multiple datasets against multiple data sources.
>> >>
>> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> >> better.
>> >> I learned this while working with loading a datamart. If you must use
>> >> linked
>> >> servers then you need to be using Openquery to use it. Do not use the
>> >> 4
>> >> part
>> >> naming.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >> >I have a query that when executed against the host server, it
>> >> >completes
>> >> >in
>> >> >11
>> >> > seconds. When I execute the same query against a linked server it
>> >> > takes
>> >> > 100
>> >> > seconds to run. I have checked the execution plan for the linked
>> >> > server
>> >> > query and it shows that 100% of the cost is the Remote Query. The
>> >> > resultset
>> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000
>> >> > and
>> >> > Windows
>> >> > 2000 OS.
>> >> >
>> >> > Since both queries ran from query analyzer on my workstation, why is
>> >> > there
>> >> > such a drastic difference in speed? Also, how do I fix the
>> >> > performance
>> >> > issue
>> >> > for the remote query so that it runs in 15 seconds or less?
>> >>
>> >>
>> >>
>>

Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in 11
> seconds. When I execute the same query against a linked server it takes 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance issue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating on
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:
> Brandon Lunt wrote:
> > I have a query that when executed against the host server, it completes in 11
> > seconds. When I execute the same query against a linked server it takes 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query plan
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>

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!
>

Remote login

Hi,
I have messed around with some remote login stuff and really screwed things up, now all of my jobs that execute prcedures on the remote server all fail saying the login failed for user 'sa'.
I need to get this working again and could really do with some help.Depending on how you've got your security configured, you need to clear out the remote login matrix on either the local or the linked server. Pop into Enterprise Mangler, and remove any local to remote mappings with constant values (I don't think you can delete the "passthrough" mapping, but don't even try just in case you could).

-PatP|||These are listed as remote servers not linked servers.

How do I remove remote mappings in Enterprise Mangler?|||Check BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_62p8.asp).

-PatP

Remote Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.
Use OSQL the command line utility to run execute stored procedure
sp_start_job.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>

Remote Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.Use OSQL the command line utility to run execute stored procedure
sp_start_job.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>