SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using hardcoded database names in query when database names are different on development and...


Using hardcoded database names in query when database names are different on development and production server

Author
Message
Rob
Rob
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 136
I have a database 'DatabaseA' which exists on a development server and a production server (called 'DatabaseA' on both servers). I am creating stored procedures that select or retrieve data from tables in a different database ('DatabaseB') on the same server. So I have 4 databases:

\productionserver\DatabaseA
\\productionserver\DatabaseB_LIVE

\developmentserver\DatabaseA
\developmentserver\DatabaseB_DEV

In a stored procedure in DatabaseA on the Development server I would write something like:

SELECT * FROM [DatabaseB_DEV].dbo.tblOrder

When I script the stored procedures and run them on the production server they no longer work because they are looking for 'DatabaseB_DEV' but it is actually called 'DatabaseB_LIVE'.

How can get around this without having to remember to run a search and replace on my scripts before I run them on the production server?

I would like to name the database to 'DatabaseB' on both Development and Production servers but the guy who set them up is not permitting me to do so. I tried using a linked server and giving it an alias but it says I cannot create a link to a database on the same (local) server.
anthony.green
anthony.green
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62440 Visits: 8587
r.gall - Thursday, December 21, 2017 2:50 AM
I have a database 'DatabaseA' which exists on a development server and a production server (called 'DatabaseA' on both servers). I am creating stored procedures that select or retrieve data from tables in a different database ('DatabaseB') on the same server. So I have 4 databases:

\productionserver\DatabaseA
\\productionserver\DatabaseB_LIVE

\developmentserver\DatabaseA
\developmentserver\DatabaseB_DEV

In a stored procedure in DatabaseB_DEV on the Development server I would write something like:

SELECT * FROM [DatabaseB_DEV].dbo.tblOrder

When I script the stored procedures and run them on the production server they no longer work because they are looking for 'DatabaseB_DEV' but it is actually called 'DatabaseB_LIVE'.

How can get around this without having to remember to run a search and replace on my scripts before I run them on the production server?

I would like to name the database to 'DatabaseB' on both Development and Production servers but the guy who set them up is not permitting me to do so. I tried using a linked server and giving it an alias but it says I cannot create a link to a database on the same (local) server.

As the procedure lives in the same database context there is no need to use 3 part naming, what is the reason behind using 3 part naming within the same context?

The only time you would need to use 3 part naming is when you break out from DatabaseB into DatabaseA.




How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Rob
Rob
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 136

Sorry Anthony - I have updated my question as it did not reflect my problem correctly.

The stored procedure exists inside Database A, and it is select data from DatabaseB_DEV on the development server. When I script and deploy to production, DatabaseB_DEV is now called DatabaseB_LIVE so the query fails. I need the 3 part naming because I'm selecting from a different database to DatabaseA.

Hope that makes more sense!


anthony.green
anthony.green
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62440 Visits: 8587
r.gall - Thursday, December 21, 2017 3:29 AM

Sorry Anthony - I have updated my question as it did not reflect my problem correctly.

The stored procedure exists inside Database A, and it is select data from DatabaseB_DEV on the development server. When I script and deploy to production, DatabaseB_DEV is now called DatabaseB_LIVE so the query fails. I need the 3 part naming because I'm selecting from a different database to DatabaseA.

Hope that makes more sense!


Thanks for the confirmation Rob, that makes more sense now as to why 3 part naming is used.

The best option would be to create a synonym in DatabaseA which looks into DatabaseB_###
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-synonym-transact-sql

So something like this

IF @@SERVERNAME = 'ProductionServer'
BEGIN
CREATE SYNONYM DBB_tblOrder FOR DatabaseB_Live.dbo.tblOrder
END

IF @@SERVERNAME = 'DevelopmentServer'
BEGIN
CREATE SYNONYM DBB_tblOrder FOR DatabaseB_Dev.dbo.tblOrder
END



Then in the procedure in DatabaseA you would just do

SELECT * FROM DBB_tblOrder





How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Steve Jones
Steve Jones
SSC Guru
SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)

Group: Administrators
Points: 327721 Visits: 20091
Synonyms is how I'd do this, perhaps with idempotent scripts like Anthony shows. There is a PIA in terms of maintenance in that you have difference copies of the code for ServerA v ServerB, but it's minimal here.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ScottPletcher
ScottPletcher
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46217 Visits: 8027
That's going to be a royal pita.

Why not just stop using different names for dev and prod? It's easier to mess up the aliasing than it is to just use the same name and use other methods to insure the DEV is only used on dev and PROD/LIVE is only used on prod.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Mike Good
Mike Good
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3845 Visits: 1103
Synonyms. Minor PIA, much less so than alternative of editing DB names every time you migrate code. Fixing your DB names is better long term strategy, and you should keep that in mind. But synonyms are easy short term solution.



ScottPletcher
ScottPletcher
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46217 Visits: 8027
Maybe easy, maybe not. You have to create a synonym for every remote object you reference. And keep them in sync if, for any reason, you have to rename an object.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

Group: General Forum Members
Points: 504851 Visits: 44238
You should use synonyms for all references outside of the current database even if your database names are identical between Prod and Dev and you should ALWAYS (there's a term I don't use much) use only two part naming within your code because databases can be renamed (especially for multiple differently named instances in Dev to support multiple projects) or can be moved to a different server altogether, etc, etc, ad infinitum. Is it a pain? Yep... it's a bit of a pain but it's a hell of a lot less pain that going through all databases objects and all GUI code to change 3 or 4 part naming or, much much worse, writing code to determine which server and database it's currently running in and trying to be self-adapting.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search