Post reply

Procs and funcs for serveral databases possible?

  • January 1, 2008 at 6:46 pm

    #121526

    Originally we had only 1 database. Now we are going to let other companies use our system and server too. So I have created 15 identical databases, one for each company, and more to come. We expect more than 100. This means I have to modify all databases whenever there is a change in a procedure or function. I expect changes to happen quite often and there are a lot of procedures.

    My question is; Is there a way to have the procedures in a central place instead of repeating them for each database? Any tip will help.

    Regards

    Atle

  • January 1, 2008 at 8:34 pm

    #763898

    I think keeping them in separate databases is usually a good idea... allows for customization by customer, if need be. If you write a script to accept a little dynamic SQL, it's not difficult to propagate functions, views, and procs nearly automatically.

    The only other way I know to do this is generally accepted as "Bozo-no-no #1"... you could write them to the Master database.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 2, 2008 at 6:11 am

    #764003

    Except for the whole "customizing by customer" (fight that with every ounce of energy you have - you will lose and end up doing it anyway, but fight it) I agree with Jeff.

    I would suggest creating (or buying) a small application that will allow you to run a script on each database and then get a comparison tool to allow you to verify everything is in sync.

    Don't underestimate how much effort it will take to manage this process.

  • January 2, 2008 at 8:03 am

    #764051

    Thak you guys.

    You convinced me. I will keep the procs within the databases and find myself a good tool to help me. That will work until most of them have some customized procedures... but yes, I'll try fight it 🙂

    Atle

  • January 3, 2008 at 9:58 am

    #764452

    Hi,

    btw, check http://www.red-gate.com. Thay have tool for managing scripts applying to the many servers and databases.

    I hope it can help you.

    Regards.

  • January 3, 2008 at 3:22 pm

    #764598

    you'd likely have an issue trying to centralise, especially if you have one out of a 100 who want's to be different!

    scripting is your friend for this. You could look at dbghost too, it's good at creating scripts.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 7, 2008 at 6:01 am

    #765292

    Try to use SYNONYM

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41313809-e970-449c-bc35-85da2ef96e48.htm


    Jacob Milter

  • January 7, 2008 at 9:16 am

    #765400

    Additional advantage of implementing changes on each database is that if you expand you application for customer usage, soon some customers will host it on-site so they woul need your procedures.

    You can have your own database containing all procedures and call them by fully qualified names, but sometimes it leads to unexpected results.

    Regards,Yelena Varsha

Viewing 8 posts - 1 through 7 (of 7 total)

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