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.