Question

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.

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.

Answer 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

EXEC sp_addlinkedserver  MyOtherServer , N SQL Server 


EXEC sp_addlinkedsrvlogin 
    MyOtherServer , 
    false , 
    OtherServerDomainDomainUser , 
    DomainUserName , 
    DomainPassword

More Info Here And Here

Answer 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:

EXEC master.dbo.sp_addlinkedserver 
    @server =  MyServerConnection ,
    @srvproduct =   , 
    @datasrc =  SERVERNAMEINSTANCENAME ,
    @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]

Answer 3

I may be late to the party, but I found the following links worked for me:

为了执行初始链接，我使用了

EXEC sp_addlinkedserver @server= serverLinkPseudonym ,@srvproduct=  ,@provider= SQLOLEDB , @datasrc= 192.168.1.1 ;

然后，当我使用Windows身份验证登录时，我添加了Windows用户（这就解决了我的“未与受信任的SQL Server相关联”的错误）。

EXEC sp_addlinkedsrvlogin  serverLinkPseudonym ,  false ,  MACHINENAMEwindowsLogin ,  lnkSrvLogin ,  lnkSrvPswd ;

我还发现，如果我要运行调用 LinkedServer 的 SQL Server Agent 作业，我必须添加以下内容：

EXEC sp_addlinkedsrvlogin  serverLinkPseudonym ,  false ,  NT AUTHORITYSYSTEM ,  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.

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:

EXEC sp_addlinkedsrvlogin  serverLinkPseudonym ,  false ,  thisServerLogin ,  lnkSrvLogin ,  lnkSrvPswd ;

Then test it:

SELECT * FROM [serverLinkPseudonym].[DBName].[dbo].[TableName]

Answer 4

如果您想要查询另一个服务器，您需要创建一个链接服务器。

This page has a pretty thorough explination of how the sp works. http://doc.ddart.net/mssql/sql70/sp_adda_17.htm

如果您想要链接到另一个 SQL 服务器，只需执行以下操作：

sp_addlinkedserver @server= ServerName , @srvproduct= SQL Server

@server是您要添加的服务器名称。@srcproduct是它的服务器类型。您可能需要执行一些其他操作来将2008连接到2005，但是2008应该像这样工作。

UPDATE:

友情链接