1

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

user2206329
  • 2,792
  • 10
  • 54
  • 81

1 Answers1

2

You will need to create a linked server.

Next you can something along the lines of:

SELECT *
FROM [server\instance].[database].[schema].[table]
Menno
  • 12,175
  • 14
  • 56
  • 88