Post reply

Stored procedures using different databases

  • July 14, 2004 at 4:44 pm

    #180964

    I have a system that queries multiple databases using store procedures.  My stored procedures have selects from these different databases and therefore I must reference these databases in my selects.  Although this is fine, when this system goes to production, the database names of the production databases will differ from what they were called in development.  What is the best method of handling this?  I would hate to think when my application goes into production that I would have to touch each of my stored procedures to change the database names to the appropriate names for production.  Do assume the databases I’m using reside on the same server as my system’s database.

    Does anyone have any guidance handling this?

     

  • July 15, 2004 at 11:30 am

    #514708

    Hello. A list of databases can be found in master.dbo.sysdatabases.



    Everett Wilson
    ewilson10@yahoo.com

  • July 15, 2004 at 11:40 am

    #514711

    Maybe I didn't make my problem clear enough.  I have a stored procedures in my database that resides on my development server.  This same server contains databases that my stored procedures use in select statements.  In order to query "external" tables in other databases, my stored procedures must reference the "external" tables using the name of the database in which those "external" tables resides.  When I move my database to production, which is a different server, the database names I used in my stored procedures to reference the "external" databases will change.  For example:

    My DEV database name is called "MyDBDev"

    DEV databases referenced in my SPs are "Data1Dev" and "Data2Dev"

    My PRD database name is called "MyDBPrd"

    PRF databases reference in my SPs are "Data1Prd" and "Data2Prd"

    What I’m wondering is what is the best way to ease the pain for moving my database to production and having several SPs that reference databases that don’t exist by the name on the production server?

    Thanks in advance!

  • July 15, 2004 at 11:53 am

    #514718

    Sorry about that.

    The only thing I can offer would be to dynamically build the query, but for a large group of sprocs this would be depressing.



    Everett Wilson
    ewilson10@yahoo.com

  • July 15, 2004 at 11:57 am

    #514719

    Thanks ewilson20.  Unless anyone else out there has a good way of handling this, I think my only solution is to script out all SPs before going to prod and manually changing the database names.  Then as a maintenance issue, every time we release a SP from Dev to Prod, we have to change the referenced database names.

     

  • July 15, 2004 at 10:30 pm

    #514790

    I would suggest let the stored procedure takes the database names as input parameter.

    Sorry if my answer sounds silly.

    Thanks,

    Ganesh

  • July 16, 2004 at 5:13 am

    #514808

    We have exactly the same issue.

    You could use dynamic sql as suggested above, I dont like it though.

    We solved it by using a deployment tool that I wrote. (a relatively simply .net tool that does a find and replace).

    Alternatively you could create a stored procedure that you call upon deployment that dynamically creates the stored procedure.

    Good luck

  • July 16, 2004 at 8:48 am

    #514881

    We have run into the same issue - some in our company would like to have database names suffixed with the environment name (Prod, Development, etc.) which works better for the DBAs.  Unfortunately, as you have indicated it can be a pain for anyone that has to do cross-database processing, which we do.

  • July 16, 2004 at 9:29 am

    #514892

    It sounds like to me there is a small, if not, large market for an application that does this for you.  The app could select a database, run through each stored procedure, present you a list of the alised databases and you could enter the new database name.  Then it would generate the ALTER scripts for applying the name names.

     

  • July 16, 2004 at 10:27 am

    #514901

    IF (@@SERVERNAME = 'MyDBProd')

     BEGIN

      -- PROD CODE HERE

     PRINT 'PROD SERVER'

     END

    ELSE

     BEGIN

      -- DEV CODE HERE

     PRINT 'DEV SERVER'

     END

    Although, the server names could change in the future.  So I might create an additional table on both the dev and production database that maps prod database names to dev database names.  Then create a UDF to return the correct value.

  • July 17, 2004 at 7:47 pm

    #514995

    Actually, there is a HUGE market for a tool that does this for you.  WahLah....CodeSmith.  You should check it out since it's free.

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply