December 21, 2017 at 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 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.
December 21, 2017 at 3:12 am
r.gall - Thursday, December 21, 2017 2:50 AMI 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_DEVIn 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.
December 21, 2017 at 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!
December 21, 2017 at 3:36 am
r.gall - Thursday, December 21, 2017 3:29 AMSorry 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
December 26, 2017 at 11:11 am
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.
December 26, 2017 at 2:41 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 27, 2017 at 10:05 am
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.
December 27, 2017 at 10:20 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 27, 2017 at 11:02 am
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply