TSQL command to connect to another server (SQL Server 2005)

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

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.