Here's the issue I'm having. I am trying to create a stored proc that will be deployed to DEV, QA, and PROD environments. Because of the strict requirements on the deployment process, I have to make sure my proc in the same across all three environments and has to work (of course!). The problem is that this proc references a table in a different database. In DEV and QA this is ok, because the database is on the same server, however in PROD the database in question is located on a separate server. The following is a code snippet from my proc that tries to deal with the different environment issues:
IF @@SERVERNAME<>'Production'
BEGIN
select distinct m.acct_id
from l_map m (nolock)
join #llist ll on ll.acct_id = m.acct_id
where ll.acct_id not in (select l_number from [OTHERDATABASE].[dbo].[OTHERTABLE] where lmi_status_code not in (select item from #ruleItems))
END
ELSE
BEGIN
select distinct m.acct_id
from l_map m (nolock)
join #llist ll on ll.acct_id = m.acct_id
where ll.acct_id not in (select l_number from [OTHERSERVER].[OTHERDATABASE].[dbo].[OTHERTABLE] where lmi_status_code not in (select item from #ruleItems))
END
My proc is called from within a different proc. When I test the above logic directly, I get the results I expect. However, when I try to test it in context in DEV or QA (from the top level proc), I get an error saying that [OTHERSERVER] could not be found. I cannot (and don't need to) create a linked server in DEV and QA, but I need to be able to use the linked server in the PROD environment. Does anyone know how to accomplish this?
Use synonyms, see here.
Also see these two SO examples: one, two.
Synonym definition on each server may be (is) different, but the code (stored procedure) does not change.