MS SQL Server: How to alias a linked server

This is regarding MS SQL Server.
I have two DB servers for my application named Torch

TorchProd and TorchTest are the db servers.
There is also a data mining server DataMineProd and DataMineTest
I have linked servers to these shown below...

TorchProd has a link to DataMineProd

TorchTest has a link to DataMineTest

I have a proc called TorchCRUD. Inside that proc is this sql...
select * from DataMineTest.mydatabase.dbo.mytable


If I push this proc up to production it will fail because it can't find DataMineTest. It could find DataMineProd How do I create an alias such that I can move this proc freely from Test to Prod?

Here is how we've handled a very similar situation.

On both Torch SQL instances, create a linked server pointing to the matching DataMine server. But don't include the Test/Prod designation in the name of the linked server. Something like this on TorchTest:

EXEC master.dbo.sp_addlinkedserver @server = N'DataMine', @srvproduct=N'sql_server', @provider=N'SQLNCLI', @datasrc=N'DataMineTest'

And this on TorchProd:

EXEC master.dbo.sp_addlinkedserver @server = N'DataMine', @srvproduct=N'sql_server', @provider=N'SQLNCLI', @datasrc=N'DataMineProd'

Notice that the name of the linked server is "DataMine" in both cases, but the one on TorchProd points to DataMineProd, and the one on TorchTest points to DataMineTest.

Then, your code can refer to the "DataMine" linked server.

select * from DataMine.mydatabase.dbo.mytable

Push the code to Test, and when it executes, it will call across the link to the DataMineTest SQL server. Then push it to Prod, and it will call across the link to the DataMineProd instance.

Note: This is in no way an endorsement of using Linked Servers. Most of the cases in which we use them would be better off handled in a different way. See https://www.brentozar.com/archive/2021/07/why-are-linked-server-queries-so-bad/. But when we have to use them, naming them in this way helps allow our code to move from Dev, to Test, to Prod mostly seamlessly.

StackExchange Database Administrators Q#337557, answer score: 4