1

If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full SP each time we push to Live.

We want a single of line of code such as

select col1, col2 from databaseName..ourTable

Where databaseName is somehow set at a server level.

I understand synonyms are the wrong solution for this. Is there something similar we can use that doesn't include writing a synonym for every single object?

1

You can create a synonym to the object, however you cannot currently create a synonym to a database. This is a popular request on Connect:

So, as you've stated in your question, you'd need to run CREATE SYNONYM for each object affected. Like this, in the dev instance:

CREATE SYNONYM MyObjectSyn
FOR Database1Dev.dbo.MyObject;

And like this in the prod instance:

CREATE SYNONYM MyObjectSyn
FOR Database1.dbo.MyObject;

Then the stored procedure code references MyObjectSyn instead.

Another way to get around this problem, that I don't recommend, is to use a linked server connected to each database. The linked server can be repointed on dev to Database1Dev, and Database1 on prod.

Or, as Scott Hodgin suggested: use dynamic SQL and a configuration table on each server that would 'map' <database1> to a specific database on that server. The stored procedure would read the configuration table and REPLACE <database1> with the name retrieved from the configuration table.

  • I did say anyway without doing this. I'm tempted to edit your answer to just "No" and mark it as accepted :D – Paul Aug 25 '17 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.