Geek Answers Handbook

SQL query through two connections?

I have a stored procedure that runs against a local database and populates a temporary table. Then I would like to connect to the remote database and query it based on the values ​​of local temptations. Is it possible?

Thanks.

+5
source share
3 answers

Yes it is. You can create a linked server with another server, and then run a linked server request with another server in the same batch. Here's how:

USE [master]
GO
--Add linked server
EXEC master.dbo.sp_addlinkedserver @server = N'ServerName', @srvproduct=N'SQL Server'
GO
--Add login info
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ServerName', @locallogin = NULL , @useself = N'True'
GO



--Using Linked server
USE [UserDB]
Create Table #Test
(
    Test int not null
);

insert into #Test
select 1


select * 
from ServerName.DBName.dbo.Table
where Col1 in (select Test from #Test)

Connect the server name, make sure that your login credentials work on both servers and follow the naming scheme of the 4 parts on the last line.

+7
source

, .

+2

More articles:


All Articles