5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Scott Klarenbach
  • 37,171
  • 15
  • 62
  • 91

3 Answers3

7

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.

Anon246
  • 1,821
  • 13
  • 16
2

I think you would need to set up a linked server.

Gratzy
  • 9,164
  • 4
  • 30
  • 45
0

you can this over registered servers.

Helpfull links:

http://www.sqlskills.com/blogs/kimberly/sql-server-2008-central-management-servers-have-you-seen-these/

MasallahOzen
  • 1
  • 3
  • Your answer should stand on its own in the case of the link breaking. Please include any relevant information in your answer. – Andrew Sep 25 '13 at 15:01