Connect to remote server in T-SQL (SQL server 2008)

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', 
   'OtherServerDomain\DomainUser', 
   'DomainUserName', 
   'DomainPassword'

More Info Here And Here

Answer 2

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

if you want to link to antoher sql server, just execute this:

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.