TSQL command to connect to another server (SQL Ser

Question

Is there a TSQL command to connect to another server?

Or when you are in a Query Window, what are the Keyboard shortcuts to connect to another server and have a Query Window show up?

I have seen Ctrl+N pop up the Connect to Server dialog in some screens but when I am in a Query Window already and hit Ctrl+N it just opens up another Query Window.

The USE command lets you connect to other databases on the current server but is there a command that lets you connect to another server?

I am using SQL Server 2005.

Answer 1

You can use OpenDataSource with a linked server

OpenDataSource(provider_name, init_string)

For example

SELECT
FirstName,
Gender
FROM
OpenDataSource (
'SQLOLEDB',
'DataSource = NOLI\SQL2;UserID=myUserID;Password=myPassword'
).Organisation.dbo.Employees

From MSDN-

Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources that are accessed infrequently. Define a linked server for any data sources accessed more than several times. Neither OPENDATASOURCE nor OPENROWSET provide all the functionality of linked-server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided every time that OPENDATASOURCE is called.

Answer 2

You have the choice of creating a Linked Server and use with OPENQUERY or use OPENROWSET.

If you are talking about, changing the connection to a query window, simply right-click in the query window and select change connection.

Answer 3

Once you have setup a linked server you can run TSQL against it by fully qualifying each table/view

select * from [Server].[Database].[Owner].Table

In this way you can talk to any server from any query window - if that's what you need. In most Sql you only ever supply the table, as everything else is defaulted. Using this technique you can even write join clauses between servers, as low as the distributed transaction coordinator (MSDTC) is running. Of course you'll only do that once to prove it works, as it runs incredibly slowly.

Answer 4

Either via the Menu...

Query > Connection > Change Connection

or via the mouse...

(Right Click Mouse Button) > Connection > Change Connection

Both will pop up the Connect to Database Engine dialog box

If your wanting to write some TSQL between servers then you'll need to create a Linked Server and then use OPENQUERY or OPENROWSET in your SQL. There are some good pointers in the previous posts on how to do this.

Answer 5

Another couple of options that may be viable depending on what you want to do are SQLCMD mode, and Registered Servers.

SQLCMD mode can be enabled under the query menu in SSMS. Once it's enabled you can do something like this with it:

:CONNECT SERVER1
SELECT @@SERVERNAME;
GO

:CONNECT SERVER2
SELECT @@SERVERNAME;
GO

With Registered Servers (should be under the View menu) you can set up groups of servers and execute queries against the groups all at once.

Both can be useful in many DBA scenarios, but I'm not sure if that's what you're after.