How to access another database located on a different server in a SP? [duplicate]

Question

I am working on a stored procedure that has been written a few years ago. The stored procedure accesses another database in the join query as shown below,

select * from C1
join D2.DBO.C2
where .....

Currently it works great as both databases reside on the same server. The organisation wants to move the 'D2' database to another server. As soon as this happens the SP will fail.

They also want to pass the server name as a parameter into the sp

is that possible?

so something like this

CREATE PROC [dbo].[sp_Test]    
(  
 @DB2Name VARCHAR(100)  
)  
AS   

Select * from C1
join @DB2Name.DBO.C2

Thanks in advance

just go through linked servers

mohan111
– mohan111

2015-06-21 06:31:35 +00:00
Commented Jun 21, 2015 at 6:31 — mohan111
– mohan111, Commented Jun 21, 2015 at 6:31

Menno · Accepted Answer · 2015-06-21 06:33:19Z

2

You will need to create a linked server.

Next you can something along the lines of:

SELECT *
FROM [server\instance].[database].[schema].[table]

answered Jun 21, 2015 at 6:33

Menno

12.8k14 gold badges59 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to access another database located on a different server in a SP? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related