在T-SQL中连接到远程服务器（SQL server 2008）

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_addlinkedserver and sp_addlinkedsrvlogin but 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>连接无关。

4 个解决方案

#1

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'

More Info Here And Here

更多信息在这里和这里

#2

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_addlinkedsrvlogin instead of @provstr to 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]

#3

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:

如果你想链接到antoher sql server,只需执行以下命令:

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年应该像这样工作。

#4

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]

#1

EXEC sp_addlinkedserver 'MyOtherServer', N'SQL Server'


EXEC sp_addlinkedsrvlogin 
   'MyOtherServer', 
   'false', 
   'OtherServerDomain\DomainUser', 
   'DomainUserName', 
   'DomainPassword'