Post reply

sp_dboption

  • December 18, 2003 at 8:50 am

    #107033

    Hi all,

    A newbie question for you all.

    I'm trying to use sp_dboption to make the same change on multiple user databases at the same time. I think I can do this using nested sprocs but it's pretty complicated for a newbie.

    I figured I'd create sproc declare the dboption variables and then set those variables. I can set the @optname and @optvalue very easily, but I'm having trouble figuring out how pass multiple database names into sp_dboption. My thought was to use a select query from the master database name column where dbid > 4 to make sure I don't affect any system databases, but NAME is a reserved word.

    Am I going at this in way to complicated a manner? Am I all wet in my approach? If I'm not, or even if I am, just how would I accomplish this?

    Any and all help would be appreciated. My main goal in this exercise is to see if I'm thinking through the problem correctly as t-sql is my first coding experience and I'm teaching myself, but I'd also like to solve the problem too.

    TIA

    Gary

  • December 18, 2003 at 8:58 am

    #486969

    quote:

    My thought was to use a select query from the master database name column where dbid > 4 to make sure I don't affect any system databases, but NAME is a reserved word.

    I presume you mean that you're using a cursor. You should be able to select name from master.dbo.sysdatabases even with name being a reserved words. I do it myself. But if you do run into a conflict with any reserved words, you can simply enclose the word in square brackets, like this:

    Select [name]

    from master.dbo.sysdatabases

  • December 18, 2003 at 9:21 am

    #486980

    jxflagg,

    I just had to drive my wife to work and on my way there I realized that a cursor would be the tool to use. Thanks for confirming this. I appreciate it.

  • December 18, 2003 at 11:15 am

    #487019

    Hi once again.

    I've put something together but I'm misunderstanding a part of the process of taking the cursor output and placing it into sp_dboption. I can create the sproc and the cursor but when I run it I get an error stating that a cursor name "next" doesn't exist.

    Could I get you to take a look at my code and tell me how I'm screwing this up. Once you get up off the floor from laughing at my newbie mistakes I'd really appreciate some more help. 🙂

    ALTER proc sptakedbsoffline

    as

    Declare @dbname varchar(20)

    Declare @optname varchar(10)

    Declare @optvalue varchar(6)

    set @optname = 'offline'

    set @optvalue = 'true'

    declare DbName Cursor

    Global

    for

    select [name] from master.dbo.sysdatabases where dbid > 4

    Open DbName

    Fetch next from DbName into @dbname --populate variable from cursor

    While @@Fetch_status=0

    Begin

    exec sp_dboption @dbname, @optname, @optvalue

    Fetch next into @dbname

    end

    close dbname

    deallocate dbname

  • December 18, 2003 at 11:47 am

    #487025

    I just figured it out. I'd given it up for a while and had an inspiration and it worked.

  • December 22, 2003 at 10:50 am

    #487461

    EXEC sp_MSForeachdb 'if "?" IN ("db1", "db2", "db2") EXEC sp_dboption ?, "dbo only", true'

    Edited by - jdausubel on 12/22/2003 10:51:35 AM

  • December 22, 2003 at 11:22 am

    #487470

    quote:

    EXEC sp_MSForeachdb 'if "?" IN ("db1", "db2", "db2") EXEC sp_dboption ?, "dbo only", true'

    Edited by - jdausubel on 12/22/2003 10:51:35 AM

    OR:

    

    EXEC sp_MSForeachdb 'if db_id(''?'') > 4 EXEC sp_dboption ?, ''dbo only'', true'


    * Noel

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

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