连接到另一台服务器的 TSQL 命令 (SQL Server 2005)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/545364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL command to connect to another server (SQL Server 2005)
提问by Gerhard Weiss
Is there a TSQL command to connect to another server?
是否有连接到另一台服务器的 TSQL 命令？
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 Serverdialog in some screens but when I am in a Query Window already and hit Ctrl+N it just opens up another Query Window.
我已经看到 Ctrl+N在某些屏幕中弹出“连接到服务器”对话框，但是当我已经在查询窗口中并按 Ctrl+N 时，它只会打开另一个查询窗口。
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?
USE 命令可让您连接到当前服务器上的其他数据库，但是否有一个命令可让您连接到另一台服务器？
I am using SQL Server 2005.
我正在使用 SQL Server 2005。
采纳答案by Russ Cam
You can use OpenDataSourcewith a linked server
您可以将OpenDataSource与链接服务器一起使用
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-
从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.
与 OPENROWSET 函数一样，OPENDATASOURCE 应该只引用不常访问的 OLE DB 数据源。为多次访问的任何数据源定义链接服务器。OPENDATASOURCE 和 OPENROWSET 都不提供链接服务器定义的所有功能，例如安全管理和查询目录信息的能力。每次调用 OPENDATASOURCE 时都必须提供所有连接信息，包括密码。
回答by kevchadders
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 Enginedialog 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.
如果您想在服务器之间编写一些 TSQL，那么您需要创建一个链接服务器，然后在您的 SQL 中使用 OPENQUERY 或 OPENROWSET。之前的帖子中有一些关于如何做到这一点的好建议。
回答by Kevin Dahl
Another couple of options that may be viable depending on what you want to do are SQLCMD mode, and Registered Servers.
取决于您想要做什么，另外几个可能可行的选项是 SQLCMD 模式和注册服务器。
SQLCMD mode can be enabled under the query menu in SSMS. Once it's enabled you can do something like this with it:
SQLCMD 模式可以在 SSMS 中的查询菜单下启用。启用后，您可以使用它执行以下操作：
: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.
两者在许多 DBA 场景中都很有用，但我不确定这是否是您所追求的。
回答by MrTelly
Once you have setup a linked server you can run TSQL against it by fully qualifying each table/view
设置链接服务器后，您可以通过完全限定每个表/视图来针对它运行 TSQL
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.
通过这种方式，您可以从任何查询窗口与任何服务器对话 - 如果您需要的话。在大多数 Sql 中，您只提供表，因为其他所有内容都是默认值。使用这种技术，您甚至可以在服务器之间编写连接子句，低至分布式事务协调器 (MSDTC) 正在运行。当然，您只能这样做一次以证明它有效，因为它运行得非常慢。
回答by Mitch Wheat
You have the choice of creating a Linked Serverand use with OPENQUERYor use OPENROWSET.
您可以选择创建链接服务器并使用OPENQUERY或使用OPENROWSET。
If you are talking about, changing the connection to a query window, simply right-click in the query window and select change connection.
如果您正在谈论将连接更改为查询窗口，只需在查询窗口中单击鼠标右键并选择更改连接。
