在 T-SQL 中连接到远程服务器 (SQL server 2008)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/803103/
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
Connect to remote server in T-SQL (SQL server 2008)
提问by The real napster
Does anyone have an example of a stored procedure which makes a connection to a remote server?
有没有人有一个连接到远程服务器的存储过程的例子？
I have been searching the web and have so far discovered that it might can be done using sp_addlinkedserverand sp_addlinkedsrvloginbut I haven't found a good example and I don't understand the documentation that well.
我一直在网上搜索，到目前为止发现可以使用它来完成sp_addlinkedserver，sp_addlinkedsrvlogin但我还没有找到一个很好的例子，我也不太了解文档。
UPDATE:
更新：
None of the two first replies help me out, the closest I can get is using this:
前两个回复都没有帮助我，我能得到的最接近的是使用这个：
EXEC sp_addlinkedserver
@server = 'SiminnSrv',
@provider = 'SQLNCLI',
@catalog = 'devel',
@srvproduct = '',
@provstr = 'DRIVER={SQL Server};SERVER=my.serveradr.com;UID=my_user_name;PWD=my_pass_word;'
That actually makes me connect but when I query a table I get this message:
这实际上让我连接，但是当我查询表时，我收到以下消息：
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server >connection.
用户 '(null)' 登录失败。原因：未与受信任的 SQL Server > 连接相关联。
回答by Eoin Campbell
Essentially you create a linked server to the other server, and then provide login credentials to be used for SQL calls to that linked server. e.g. this will connect to "MyOtherServer" using a DomainAccount for that server with the username & password 'DomainUserName', 'DomainPassword'
本质上，您创建到另一台服务器的链接服务器，然后提供登录凭据以用于对该链接服务器的 SQL 调用。例如，这将使用该服务器的 DomainAccount 连接到“MyOtherServer”，用户名和密码为“DomainUserName”、“DomainPassword”
EXEC sp_addlinkedserver 'MyOtherServer', N'SQL Server'
EXEC sp_addlinkedsrvlogin
'MyOtherServer',
'false',
'OtherServerDomain\DomainUser',
'DomainUserName',
'DomainPassword'
回答by Marcos Dimitrio
I managed to connect to MSSQL Server 2008 through a linked server using the "SQL Server Native Client 10" (SQLNCLI10), but I had to use sp_addlinkedsrvlogininstead of @provstrto provide the connection details. This is based on the example from this article:
我设法使用“SQL Server Native Client 10” ( SQLNCLI10)通过链接服务器连接到 MSSQL Server 2008 ，但我不得不使用sp_addlinkedsrvlogin而不是@provstr提供连接详细信息。这是基于本文中的示例：
EXEC master.dbo.sp_addlinkedserver
@server = 'MyServerConnection',
@srvproduct = '',
@datasrc = 'SERVERNAME\INSTANCENAME',
@provider = 'SQLNCLI10',
@provstr = ''
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'MyServerConnection',
@useself = 'false',
--@locallogin = 'someLocalUser' -- Use to restrict the connection to specific login
@rmtuser = 'remoteUser',
@rmtpassword = 'secret'
Querying this linked server:
查询此链接服务器：
SELECT *
FROM [MyServerConnection].[SomeDatabase].[dbo].[TableName]
回答by kangacHASHam
I may be late to the party, but I found the following links worked for me:
我可能迟到了，但我发现以下链接对我有用：
To perform the intial link, I used
为了执行初始链接，我使用了
EXEC sp_addlinkedserver @server='serverLinkPseudonym',@srvproduct='',@provider='SQLOLEDB', @datasrc='192.168.1.1';
Then, as I was logging in with Windows authentication, I added the Windows user (this cured my " Not associated with a trusted SQL Server" error)
然后，当我使用 Windows 身份验证登录时，我添加了 Windows 用户（这解决了我的“与受信任的 SQL Server 无关”错误）
EXEC sp_addlinkedsrvlogin 'serverLinkPseudonym', 'false', 'MACHINENAME\windowsLogin', 'lnkSrvLogin', 'lnkSrvPswd';
I also found that if I was going to run SQL Server Agent jobs that made calls to the LinkedServer, I had to add the following:
我还发现，如果我要运行调用 LinkedServer 的 SQL Server 代理作业，我必须添加以下内容：
EXEC sp_addlinkedsrvlogin 'serverLinkPseudonym', 'false', 'NT AUTHORITY\SYSTEM', 'lnkSrvLogin', 'lnkSrvPswd';
For the sake of clarity: "192.168.1.1" is the IP of the server to be linked to. "lnkSrvLogin" is a login on the server to be linked to, that has access to the database(s) that you need to access. "lnkSrvPswd" is the password of that account.
为清楚起见：“192.168.1.1”是要链接到的服务器的 IP。“lnkSrvLogin”是要链接到的服务器上的登录名，它可以访问您需要访问的数据库。“lnkSrvPswd”是那个账户的密码。
If you are connecting to the linked server, using an account from the existing server, then you just use that account name in the sp_addlinkedsrvlogin command. eg:
如果您使用现有服务器中的帐户连接到链接服务器，则只需在 sp_addlinkedsrvlogin 命令中使用该帐户名。例如：
EXEC sp_addlinkedsrvlogin 'serverLinkPseudonym', 'false', 'thisServerLogin', 'lnkSrvLogin', 'lnkSrvPswd';
Then test it:
然后测试一下：
SELECT * FROM [serverLinkPseudonym].[DBName].[dbo].[TableName]
回答by DForck42
IF you want to be able to query another server, you will need to create a linked server.
如果您希望能够查询另一台服务器，则需要创建一个链接服务器。
This page has a pretty thorough explination of how the sp works. http://doc.ddart.net/mssql/sql70/sp_adda_17.htm
该页面对 sp 的工作方式进行了非常彻底的解释。 http://doc.ddart.net/mssql/sql70/sp_adda_17.htm
if you want to link to antoher sql server, just execute this:
如果要链接到另一个 sql 服务器，只需执行以下操作：
sp_addlinkedserver @server='ServerName', @srvproduct='SQL Server'
@server is the name of the server you want to add. @srcproduct is the type of server it is. there might be some other things you'll have to do to hook up 2008 to 2005, but 2008 should work like this.
@server 是您要添加的服务器的名称。@srcproduct 是它的服务器类型。您可能还需要做一些其他事情才能将 2008 年连接到 2005 年，但 2008 年应该像这样工作。
