February 18, 2009 at 6:35 am
Hi All,
Imagine the situation....
You have a production environment consisting of a number of proprietary databases and a configuration database that you control, all running on SQl Server 2000. Since you are not allowed to release stored procedures to the proprietary database for fear of invalidating your support agreement, you have a large number of stored procs on your configuration database (lets call it config_db) that refer to tables in your proprietary database through hard-coded names. All well and good so far.
Now, you want to restore your environment (all databases) to a development environment where the database names are different to in production. Suddently the external database name references in your stored procedures stop working!
So, you can...
a.) painstakingly go through your stored procedures and re-release them with the correct database names - not ideal since there are several hundred stored procs.
b.) convert all the stored procs in production to use dynamic sql so that the database name is determined by use of a function or something similar. However, the internet is littered with dire warnings about the over-use of dynamic sql in stored procs for performance reasons, especially where the sp is called many times.
c.) apparently there is a possibility of using synonyms for this in SQL Server 2005 but we are not going to be able to upgrade for some time yet for political and budgetary reasons.
Does anyone else have a better suggestion for achieveing true portability of stored procedures between environments?
This problem is consuming a large amount of time and meaning that we are restoring our production DBs to dev as a last resort when really this should be done as a matter of course before any new developmental work is carried out, so any advice would be greatfully received.
Thanks in advance,
Matt
February 18, 2009 at 7:10 am
I have to ask, why are the database names different? Is your dev environment on the same box as your production environment?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 7:11 am
No but we have several databases on the development server so , since they need to have different names to each other, they also have different names to the production db.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply