Why is using OPENQUERY on a local server bad?

Question

I'm writing a script that is supposed to run around a bunch of servers and select a bunch of data out of them, including the local server. The SQL needed to SELECT the data I need is pretty complicated, so I'm writing sort of an ad-hoc view, and using an OPENQUERY statement to get the data, so ultimately I end up looping over a statement like this:

exec('INSERT INTO tabl SELECT * FROM OPENQUERY(@Server, @AdHocView)')

However, I've heard that using OPENQUERY on the local server is frowned upon. Could someone elaborate as to why?

Neville

check linked servers – Raster

This is an adminstirative script, so I'm not worried about permissions. My question, specifically, is are there any concerns when the script loops over the list of servers and runs into it's own server name? This usually throws an error, server not configured for data access, which can be rectified by EXEC sp_serveroption 'LocalServer', 'DATA ACCESS', TRUE – Neville

Answer 1

• Although the query may return multiple result sets, OPENQUERY returns only the first one.
• OPENQUERY does not accept variables for its arguments.
• OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name.
• If the sp_addlinkedserver stored procedure is used within same script, the credentials used on the remote server are hardcoded into the script, visible to anyone who has a copy

Reference:

• OPENQUERY

Paraldehyde

I happened to have occasion to use OPENQUERY the other day and realized your 4th point was incorrect; OPENQUERY operates on a linked server so no credentials are hard-coded. You may be thinking of OPENROWSET. – Worthington

@Aaronaught: If a linked server instance exists, why use OPENQUERY at all? If the sp_addlinkedserver stored procedure is used within the same script, my point has merit. – Paraldehyde

Well, since you asked, there actually is a reason to use OPENQUERY: Performance. OPENQUERY allows the query to be processed on the remote server, whereas standard 4-part naming has to copy all the rows to the local server, which is pretty bad for large data sets. Of course this has to be weighed against the other trade-offs you've mentioned. – Worthington

@OMGPonies This is old but I had another example today. Another reason to use OPENQUERY on a linked server is that the linked server might not be using SQL compliant with the SQL in which your code is being written making it uncompilable if a SQL call were to be done normally. – Lucindalucine

Answer 2

In addition to what @OMG Ponies said, it's simply unnecessary. There's no reason to introduce ad-hoc query and distributed transaction semantics when you don't have to. When you use OPENQUERY you take on all of the negative aspects of dynamic SQL, including less predictable plans and the server's inability to accurately track dependencies.

OPENQUERY also requires the local user to have permissions to the target server, which is probably not what you want unless it's an administrative script. You can't expect every user of one database to have the same permissions to every other database.

Worthington

Answer 3

Just a followup.

OpenQuery is good when you have to compare or manipulate some rowsets from stored procedures.

for example if you have to compare results from two servers (test and rollout server) when you migrate from SQL Server 2005 to SQL server 2008 for example, then you can do the following query:

select * into test_table from OpenQuery(testServer, 'exec testdb.dbo.test_sp');
select * into rollout_table from OpenQuery(rolloutServer, 'exec testdb.dbo.test_sp');

select * from test_table
except
select * from rollout_table;

select * from rollout_table
except
select * from test_table;

to see any discrepancies.

Fitch