Why is using OPENQUERY on a local server bad?
openquery, sql, sql-server, sql-server-2005, t-sql
Solution
- 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
Problem
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?