Using Openquery Without Returning Resultset

[Phil Sandler]

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

[Erland Sommarskog]

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