Referring to external database dynamically

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Strategies » Referring to external database dynamically

Referring to external database dynamically

Rate Topic

Display Mode

Topic Options

Author

Message

mattaustin

Posted Wednesday, February 18, 2009 6:35 AM

Forum 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

Jack Corbett

Posted Wednesday, February 18, 2009 7:10 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 10,613, Visits: 11,959

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

mattaustin

Posted Wednesday, February 18, 2009 7:11 AM

Forum 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 »

Permissions