I have a stored procedure that runs against a local database, and fills a temp table. I'd then like to connect to a remote database and query it based on the values of the local temptables. Is that possible?
Thanks.
I have a stored procedure that runs against a local database, and fills a temp table. I'd then like to connect to a remote database and query it based on the values of the local temptables. Is that possible?
Thanks.
Yes, it is. You can create a linked server to the other server and then do a linked server query to the other server within 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)
Plug in server name, make sure your login credentials work on both servers, and follow the 4 part naming scheme on the last line.