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 the query window, which keyboard shortcuts connect to another server and the query window appears?

I saw that Ctrl + N opens the Connect to Server dialog box on some screens, but when I’m already in the request window and press Ctrl + N, it just opens another request window.

The USE command allows you to connect to other databases on the current server, but is there a command that allows you to 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 Access to OLE DB data sources not often. Defining a linked server for access to any data sources than several times. neither OPENDATASOURCE or OPENROWSET provide all the functions of a linked definition server, such as management security and the ability to query directory information. All connection information, including passwords, must be provided every time OPENDATASOURCE is called.

Answer 2

Or through the menu ...

Requests> Connection> Change Connection

or with the mouse ...

(right mouse button)> Connection> Change connection

Both will appear in the Connect to Database dialog box .

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

Answer 3

Another pair of options that may be viable depending on what you want to do is SQLCMD mode and registered servers.

SQLCMD mode can be enabled in the query menu in SSMS. Once turned on, you can do something like this with it:

 :CONNECT SERVER1 SELECT @@SERVERNAME; GO :CONNECT SERVER2 SELECT @@SERVERNAME; GO 

With registered servers (should be in the "View" menu), you can configure server groups and execute requests for all groups at the same time.

Both can be useful in many DBA scenarios, but I'm not sure what you need.

Answer 4

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

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

Answer 5

Once you have configured the linked server, you can run TSQL against it, fully qualifying each table / view

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

Thus, you can talk to any server from any request window - if you need it. In most Sql, you only ever supply a table, since everything else is by default. Using this method, you can even write offers for connections between servers, as it works with the Distributed Transaction Coordinator (MSDTC). Of course, you will only do this once to prove that it works, because it works incredibly slowly.