We have a number of SQL db environments for our CRM system, e.g. LIVE, TEST, DEVELOP, etc. and occasionally we want to take an up to date copy of the LIVE environment and restore it over the other environments. When we do this we have to manually change some stored procedures that use a Linked Server to point to the corresponding ERP database environment for integration purposes.
For instance, in the LIVE SQL db there may be a stored procedure that gets data from the ERP database using syntax like this:
SELECT ... FROM OPENQUERY(ERPLIVE, 'SELECT ... FROM .... WHERE ...')
where ERPLIVE is the Linked Server name for the connection to the LIVE ERP database.
In the TEST SQL db this syntax would normally read:
SELECT ... FROM OPENQUERY(ERPTEST, 'SELECT ... FROM .... WHERE ...')
where ERPTEST is the Linked Server name for the connection to the TEST ERP database.
When we restore of a copy of the LIVE SQL db over the TEST SQL db then the stored procedures in TEST will now need updating to change the Linked Server name from ERPLIVE to ERPTEST.
What I would like to know is if there is an easier way to do this, for instance using a variable for the Linked Server name in the stored procedure that gets the correct Linked Server name for that database. I was thinking maybe having a table with 2 columns, 1 for the db name and 1 for the linked server name so that the variable reads the Linked Server name for that db from this table.
I'm not sure if it's possible, how to go about doing it, or if there is a better way altogether.