Connecting to a remote server in T-SQL (SQL Server 2008)

Question

Does anyone have an example of a stored procedure that makes a connection to a remote server?

I searched on the Internet and still found that this can be done with sp_addlinkedserver and sp_addlinkedsrvlogin , but I did not find a good example, and I do not understand the documentation very well.

UPDATE:

None of the first two answers will help me, the closest I can use is:

 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;' 

It really makes me connect, but when I query the table, I get this message:

Login failed for user '(null)'. Reason: Not related to a reliable SQL Server> connection.

Answer 1

Essentially, you create a linked server on another server, and then provide the credentials that will be used for SQL calls to that linked server. for example it will connect to "MyOtherServer" using DomainAccount for this server with username and password "DomainUserName", "DomainPassword"

 EXEC sp_addlinkedserver 'MyOtherServer', N'SQL Server' EXEC sp_addlinkedsrvlogin 'MyOtherServer', 'false', 'OtherServerDomain\DomainUser', 'DomainUserName', 'DomainPassword' 

Read More Here And Here

Answer 2

I was able to connect to MSSQL Server 2008 through a linked server using the "native SQL Server 10 client" ( SQLNCLI10 ), but I had to use sp_addlinkedsrvlogin instead of @provstr to provide connection information. This is based on the example of this article :

 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' 

Linked server request:

 SELECT * FROM [MyServerConnection].[SomeDatabase].[dbo].[TableName] 

Answer 3

If you want to be able to request another server, you need to create a linked server.

This page has a fairly detailed explanation of how sp. http://doc.ddart.net/mssql/sql70/sp_adda_17.htm

if you want to set the link to antoher sql server, just do this:

 sp_addlinkedserver @server='ServerName', @srvproduct='SQL Server' 

@server is the name of the server you want to add. @srcproduct is a server type. there may be some other things that you will need to do to connect from 2008 to 2005, but 2008 should work as follows.

Answer 4

Maybe I'm late for the party, but I found the following links for me:

To execute the internal link, I used

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

Then, when I logged in with Windows Authentication, I added a Windows user (this caused a "Not Reliable SQL Server Connected" error)

 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 LinkedServer calls, I had to add the following:

 EXEC sp_addlinkedsrvlogin 'serverLinkPseudonym', 'false', 'NT AUTHORITY\SYSTEM', 'lnkSrvLogin', 'lnkSrvPswd'; 

For clarity: "192.168.1.1" is the IP address of the server you want to connect to. "lnkSrvLogin" is the login on the connected server that has access to the database that you need to access. "lnkSrvPswd" is the password for this account.

If you connect to a linked server using an account from an existing server, you simply use that account name in the sp_addlinkedsrvlogin command. eg:

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

Then check it out:

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