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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With