0

How do you create a cross server query use statement to run a select. This is what I have but I cant seem to get anything than 'does not exist'

Thanks!

DECLARE @ServerVar AS NVARCHAR (MAX)
SET @ServerVar = 'servera/server1';

DECLARE @DBVar AS NVARCHAR (MAX)
SET @DBVar = 'db';
Declare @reportVar nvarchar(max);
Set @reportVar = 'USE ' + quotename(@ServerVar) + '.' + quotename(@DBVar);
EXEC (@reportVar);

SELECT * FROM myTable;
  • Have you linked remote server to your local one? – Yuriy Galanter Jun 11 '13 at 19:53
  • 3
    (1) You can't say `USE servername.databasename;` (2) even if you could, it would only last for the lifetime of the `EXEC()`. Why don't you build a query like `SET @sql = N'SELECT * FROM ' + QUOTENAME(@ServerVar) + '.' + QUOTENAME(@DBVar) + '.dbo.myTable;';`? – Aaron Bertrand Jun 11 '13 at 19:55
  • The code you have does not work because the `EXEC()` function creates a new execution context. Within that context you successfully set the server you want to use, but then you're out of code. That context is destroyed, and you're back up the stack to where you were before. – Joel Coehoorn Jun 11 '13 at 19:58
  • @AaronBertrand A possible answer to your question is that it's far too slow. This is the problem I'm trying to get around at the moment. – Stewart Sep 17 '20 at 16:59
  • @Stewart The syntax you're using to pull data from a linked server is making it too slow? I don't think the syntax has the effect you think it does - if the linked server is slow, there's something more impactful to troubleshoot. This is like saying, my car doesn't go fast enough, what if I different colored hat? – Aaron Bertrand Sep 17 '20 at 21:09
  • @AaronBertrand Nope, the linked server isn't slow. If I try connecting to that server directly and running the query thereon, it runs something like 100 times as fast. Furthermore, I don't think it's a fair assumption that cross-server USE statements, if SQL Server supported them, would run at the same speed as server-qualifying the table inline, as it would depend on how USE is implemented. – Stewart Sep 18 '20 at 08:44
  • @Stewart I'm still going to suggest you have a different problem, but /shrug I'm not going to argue with you about how much more efficient it might be to use theoretical syntax that doesn't exist. – Aaron Bertrand Sep 18 '20 at 13:48

2 Answers2

0

You need to link the remote server to your local one. After that you can query remote server from your local one even without using Use command

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
0

This is a good time to use a Linked Server and possibly even synonyms and dynamic sql.

See similar post: Set database name dynamically in SQL Server stored procedure?

Community
  • 1
  • 1
wilsjd
  • 2,178
  • 2
  • 23
  • 37