Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 972
Gerhard Weiss Avatar asked Feb 13 '09 09:02

Gerhard Weiss


2 Answers

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.

like image 157
Russ Cam Avatar answered Sep 20 '22 07:09

Russ Cam


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.

like image 42
kevchadders Avatar answered Sep 21 '22 07:09

kevchadders