0

I found lot of things very interesting on the website but not this one. I try to find an answer to my issue with OpenQuery, and I can't find it.

Let me explain, the goal for me is to query lot of SQL Servers in my compagny throught OPENQUERY & LINKED SERVER. Once my query is done, put the result into a table throught INSERT INTO.

Example :

INSERT into TABLE
select *
from openquery(LinkedServer,'SQLQuery')

Question 1 : How can I loop the LinkedServer ? to do the same but with others servers
Question 2 : How can I do my SQLQuery as a var on the query ?

Thanks a lot for your help

T I
  • 9,785
  • 4
  • 29
  • 51
Olivier
  • 1
  • 1
  • I don't think you can. I just tried to use a variable for a Linked Server Name and got an error. – Dan Bracuk Nov 03 '14 at 15:50
  • It depends a bit on what you are trying to do. What *are* you trying to do? And why do you need to use `openquery` instead one of the other, less obscure, ways of querying a Linked Server? Also, do you have a list/table of servers to query against? – RBarryYoung Nov 03 '14 at 15:52
  • Actually, I've done some searching and found [this.](http://stackoverflow.com/questions/19894551/sql-server-cursor-loop-through-multiple-servers-and-execute-query) It looks like a Cursor may be a viable alternative here. (I've also removed my answer in light of this, I should of searched a little more before I answered.) – KidCode Nov 03 '14 at 15:59
  • @RBarryYoung, I use linked servers a lot. I have found openquery to be the fasted way to select data. If you use this syntax, Server.database.owner.table, sql server brings that entire table into the current database first, and applies the where clause afterwards. – Dan Bracuk Nov 03 '14 at 16:00
  • @DanBracuk I was asking the OP why *they* thought that they should be using `OpenQuery`. There are about six different ways to do this, and `OpenQuery` is not nearly the most obvious, best documented, not best supported. And yes, there are cases where bringing back the whole table first is the fastest (and OpenQuery is not the only way to do that). There are many other cases where that is nightmarishly slow. It depends. – RBarryYoung Nov 03 '14 at 16:04
  • @all Thanks for your answer. In fact I used OpenQuery because I have lot of SQL Servers with not the same version (2005-2008R2-2012), and if I used only linked server to query I have sometimes a "NULL" result on some columns. So the "best" way for me was to use OPENQUERY. In fact it was the "best working" way for me, but I'm open with other solutions ^^ – Olivier Nov 04 '14 at 07:54

0 Answers0