469,112 Members | 2,016 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

Using Openquery Without Returning Resultset

Hello,

Quick, and possibly strange, question.

I am doing some work testing the running time of some dynamic SQL
statements on a remote machine. What I would like to do is execute
the SQL on the remote machine, without returning the result set to the
calling machine (this would skew the results, as my connection to the
remote machine is rather slow).

I believe SET ROWCOUNT 0 would work in principle, but the
documentation says:
"Causes Microsoft® SQL Server™ to stop processing the query after the
specified number of rows are returned."

I don't want processing to stop until all the records have been
identified. I also don't know if "SET ROWCOUNT" works with openquery
(althought a non-openquery solution would also be acceptable).

The code will be running in a stored procedure, if that matters.

Any input would be much appreciated.
Phil
Jul 20 '05 #1
1 4955
Phil Sandler (ps********@hotmail.com) writes:
I am doing some work testing the running time of some dynamic SQL
statements on a remote machine. What I would like to do is execute
the SQL on the remote machine, without returning the result set to the
calling machine (this would skew the results, as my connection to the
remote machine is rather slow).

I believe SET ROWCOUNT 0 would work in principle, but the
documentation says:
"Causes Microsoft® SQL Server™ to stop processing the query after the
specified number of rows are returned."

I don't want processing to stop until all the records have been
identified. I also don't know if "SET ROWCOUNT" works with openquery
(althought a non-openquery solution would also be acceptable).


And SET ROWCOUNT 0 means that all rows should be returned.

The best is probably to insert the result from the dynamic SQL in a
table, temp table or permanent. True, this will add the cost to
write to disk, but it's probably less skewed than getting data over
a network link.

Using temp tables with OPENQUERY is not completely trivial, because
the SQLOLEDB provider does some tricks behind your back. I have some
discussion about this on
http://www.algonet.se/~sommar/share_data.html#OPENQUERY.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Stu | last post: by
2 posts views Thread by Joe Van Dyk | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.