Results 1 to 7 of 7

Thread: DB Change

  1. #1
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31

    Angry Unanswered: DB Change

    I need to create a SP in Master while I'm in a user DB in a script.
    I can´t remember very well but I think I saw a trick to do that in a single script...
    Do you know any way to do this?
    I've tried use %db and sp_executesql with no luck...
    Thankx in advance

  2. #2
    Join Date
    Dec 2002
    Posts
    63
    This may seem simple, but have you tried using the USE <dbname> command?

    USE master
    create proc
    USE <userdb>

    Hope that helps.

  3. #3
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31
    Problem is user can change it's db name and I want to distribute a single script, something like this would be the exact solution, except because it can´t be done:

    -- prev code

    declare %db nvarchar(500)
    select %db = db_name()

    use master
    create procedure...

    use %db

    -- subsecuent code

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Are you looking for this ?

    previous article

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Changes in database context last only until the end of the EXECUTE statement (BOL topic "EXECUTE"). CREATE PROC must be the first ...
    a USE database statement is not allowed in a procedure or trigger...
    So you must use new thread (osql, automation(sp_OA...), ADODB.COMMAND,..)

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    exec('use master exec(''create proc sp_TestCreate1 as select 1'')')
    exec master.dbo.sp_TestCreate1
    exec('use master exec(''drop proc sp_TestCreate1'')')

    To be and not to be, that is the answer.

  7. #7
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31

    Talking Belive it or not... IT WORKS!

    Just in the very front my nose... it's a shame!
    BTW, our needs were an already designed backup/restore sp for our PoS system that must be distributed via osql within an initial insert script in more than 2K stores, and I was just about spliting it in two parts, the original for user db sps and the unwanted new one for master, but now I can sleep with a smile on my face, again.
    Thanx

Posting Permissions

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