Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Referring to external database dynamically Expand / Collapse
Author
Message
Posted Wednesday, February 18, 2009 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 1:54 AM
Points: 7, Visits: 45
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
Post #659307
Posted Wednesday, February 18, 2009 7:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 10,206, Visits: 13,153
I have to ask, why are the database names different? Is your dev environment on the same box as your production environment?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #659351
Posted Wednesday, February 18, 2009 7:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 1:54 AM
Points: 7, Visits: 45
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.
Post #659356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse