Using the 'Use' command, or equivilant to point to a different server

Question

I have a large dynamic SQL statement that I need to execute on multiple databases and potentially on multiple servers in the future.

At the start of the dynamic SQL I can use

USE Database1

and everything works fine. But I was wondering if there was a way I could specify the server name for linked servers? For example

USE Server1.Database1

I'm aware I could use fully qualified names in queries, this however make the sql harder to read. Is there a way I can avoid fully qualified names?

To summarise what I'm trying to achieve - I have a central database with a stored procedure that fetches and processes data from multiple databases into one location so that the client can easily report on it.

@Zane I know nothing about aliasing - I'm more a developer than a DBA. I'm going to guess I can use aliasing to make a remote instance appear as if it was local? — Will Calderwood, Jun 17 '14 at 15:51
Why is readability important in a dynamically generated statement? Just make sure the script doing the generation is readable and go for the 4-part naming in the output. A dynamic USE will work as long as you execute it within the same batch as the subsequent statements — nathan_jr, Jun 17 '14 at 16:02
@NathanSkerl They dynamically generated SQL also needs to be debugged at times. — Will Calderwood, Jun 17 '14 at 16:11
Are all the servers known? Is the list finite or do you need to read in the list of servers from a table? — nathan_jr, Jun 17 '14 at 16:17
@WillCalderwood Depending on how large that list is it could be a real pain to manage linked servers. Is there an app/service involved here? It seems a lot simpler to pull the list up into your client app and iterate through the server connections there and bring the data to a central db. You could also create linked servers on-the-fly but i'm unsure of the security implications of that option. — nathan_jr, Jun 17 '14 at 16:42
possible duplicate of [Selecting data from two different servers in SQL Server](http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server) — nathan_jr, Jun 17 '14 at 16:53

score 1 · Answer 1 · answered Jun 17 '14 at 15:48

1

I would consider using sqlcmd, instead, which is intended for this purpose. You would specify your variable at the top of the script like this:

:setvar MyLinkedServer "MyLinkedServerName"
:setvar DatabaseName "MyDb"

Then call it in the script like this:

[$(MyLinkedServer)].[$(DatabaseName)].SomeSchema.SomeTable

Before the script is executed sqlcmd will replace the values, even if it is in a text string, as would be the case in your dynamic sql. The variables can also be set as a parameter to the script file by using sqlcmd.exe to execute the script. This should help keep the code looking a little cleaner.

answered Jun 17 '14 at 15:48

Mark Wojciechowicz

4,287
1
17
25

I don't think he's running this in a script task of any kind. – Zane Jun 17 '14 at 15:52
SQL Server Management Studio can also be configured to run in SQLCMD mode. – Hannah Vernon Jun 17 '14 at 15:54
I've just added a summary of exactly what I'm trying to achieve to the original question. – Will Calderwood Jun 17 '14 at 15:56

score 1 · Answer 2 · edited Jun 18 '14 at 07:13

Since you mentioned readability as a reason to not just simply use fully qualified names, and also mentioned that you aren't familiar with aliasing, I'm left to assume your query looks like this.

select
    serverA.Database1.dbo.TableName.ColumnExample,
    serverB.Database2.dbo.TableName.SecondExample
from serverA.Database1.dbo.TableName
inner join serverB.Database2.dbo.TableName
    on serverA.Database1.dbo.TableName.BlahID = serverB.Database2.dbo.TableName.BlergID

And if this is the case then yes this sucks on ice and is very unreadable. So what you can do is add aliases to clean this code up so you have a query that looks like this.

select
    A.ColumnExample,
    B.SecondExample
from serverA.Database1.dbo.TableName as A --By setting the Alias as A you can now
inner join serverB.Database2.dbo.TableName as B --reference that instead of 
    on A.BlahID = B.BlergID --qualifying the whole statement

This would allow it to be much simpler to build a readable dynamic SQL statement that calls many databases on many servers. Then simply use whatever method you are using to call your dynamic SQL statement as

N'select
    A.ColumnExample,
    B.SecondExample
from ' + @Server1 + @Database1 + '.dbo.TableName as A --By setting the Alias as A you can now
inner join ' + @Server2 + @Database2 + '.dbo.TableName as B --reference that instead of 
    on A.BlahID = B.BlergID --qualifying the whole statement'

I would recommend adding the .prior to the database in the server variable, that way you can leave it off if it's a local server.

score 0 · Answer 3 · answered Jun 18 '14 at 08:25

If it is one and the same query that just needs to be executed in different contexts, you could try specifying the context by calling the corresponding instance of sp_executesql to execute your dynamic query:

linkedserver.remotedatabase.sys.sp_executesql N'
SELECT
  ...
FROM dbo.Table1 AS a
INNER JOIN dbo.Table2 AS b ON ...
';

That way linkedserver.remotedatabase would be the current database and indeed linkedserver the current server of the specified query.

That method, however, might require an additional level of dynamicity since you are reading the names from a table. For the following illustration of how the query building part would look like in such a case, I am assuming that the names of the server and the database are stored in variables (as if, for instance, you have populated the vars in a cursor iteration):

DECLARE @sql nvarchar(max), @metasql nvarchar(max);
SET @sql = N'your dynamic query (without USE)';
SET @metasql = QUOTENAME(@servername) + N'.' + QUOTENAME(@databasename) + N'.sys.sp_execute @sql';
EXECUTE sp_execute @metasql, N'@sql nvarchar(max)', @sql;

This kind of double nesting may not be very good for the occasional debugging you are mentioning in one of your comments, though. But then you could go with incorporation of the @sql's contents into @metasql instead of parametrisation:

DECLARE @sql nvarchar(max), @metasql nvarchar(max);
SET @sql = N'your dynamic query (without USE)';
SET @metasql = QUOTENAME(@servername) + N'.' + QUOTENAME(@databasename) + N'.sys.sp_execute N' + QUOTENAME(@sql, '''');
EXECUTE sp_execute @metasql;

Using the 'Use' command, or equivilant to point to a different server

3 Answers3