Connecting to different SQL Server (T-SQL)

If you working on one database and you want to do something on another database you have to create a linked server. To create linked server use sp_addlinkedserver stored procedure (more info)

EXEC sp_addlinkedserver 
   @server = 'linkedServerName', 
   @srvproduct = '',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=servername\instancename;DATABASE=databeseName;UID=login;PWD=password;';

After you executed obove query you can check if linked server was added correctly. Using sp_linkedservers sp you should see :



Now you can quering linked server, for example :

select * FROM linkedServerName.master.dbo.sysdatabases

If you want to call remotly stored procedure you must execute these two statments too :

exec sp_serveroption @server='linkedServerName', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='linkedServerName', @optname='rpc out', @optvalue='true'