Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Location
    Australia
    Posts
    5

    Lightbulb Unanswered: Sharing 1 set of stored procs between a number of databases

    Hi

    Im looking for an efficient way of using a singe set of stored procedures to access a number of different databases (Sql Server).

    The most feasible option Ive come up with so far is to send the specific database name through as a parameter. Unfortunately the function use isnt available to stored procs, so it seems I must move the query into a varchar, to then exec it.

    Theres been another suggestion of using a web service to store the procedures, but I imagine that this would be very slow and cumbersome.

    There must be a better way any ideas?

    Thanks,
    indra

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    if you want them available in each database, here are four options.

    script them all and run the scripts in each db
    or
    manually create them over and over again in each db
    or
    script them and place them into the model database and then recreate all of your databases.
    or
    use dts to copy database objects to other databases

    i would go with the first or fourth one.
    2 and 3 are just for show......

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Or option 5: make your application truly SCALABLE by not using separate databases.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by blindman
    Or option 5: make your application truly SCALABLE by not using separate databases.
    i didnt want to assume anything on this one, so i just answered the question.

    but dont worry, next time i will sharpshoot...

  5. #5
    Join Date
    Jan 2005
    Location
    Australia
    Posts
    5

    Arrow

    Thanks guys.

    blindman - yes, I couldn't agree with you more but *sigh* the client insists on a separate database for each of their customers (despite the schema being specifically designed to cater for multiple customers). Frustrating

    Ruprect - we currently use option 1, which is what I'm trying to work around. I'll do a bit of research into dts.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Option #6 - create the set of those procedures in MASTER and issue sp_MS_marksystemobject on each of them. This way the procedures will be executed in the database context of the connection that invokes them, not where they are created.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2005
    Location
    Australia
    Posts
    5
    Fantastic, this is exactly what I was after - thanks rdjabarov.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •