SQL Server Performance

alter database using variables?

Discussion in 'General Developer Questions' started by biged123456, Apr 1, 2005.

  1. biged123456 New Member


    I am trying to alter large numbers of databases and I wanted to insert a variable into the alter database command. I keep getting errors.

    ---
    declare @dbname nvarchar(100)
    set @dbname = 'TestDB'
    alter database @dbname set offline with rollback immediate

    ---
    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near '@dbname'.
    Server: Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'immediate'.
    ---
    When I substitute the TestDB value for @dbname, it works fine. Any help is appreciated.

  2. derrickleggett New Member

    You would need to use dynamic SQL.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. Madhivanan Moderator


    Try this

    declare @dbname nvarchar(100)
    set @dbname = 'TestDB'
    Exec('alter database '+@dbname+' set offline with rollback immediate')


    Madhivanan

    Failing to plan is Planning to fail
  4. biged123456 New Member

    Many thanks Madhivanan. Guess a good SQL scripting book may a better way to learn than books online...

    Matt
  5. kevwit New Member

    how would you do this:
    --declare variables for first database
    DECLARE @db1 nvarchar(100)
    DECLARE @db1_LOG nvarchar(100)
    DECLARE @db1_dfile nvarchar(512) --destination of file location leave same if not moving
    DECLARE @db1_lfile nvarchar(512) --destination of log location leave same if not moving

    SET @db1 = 'testdb';
    SET @db1_dfile = 'C:program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA estdb.mdf';
    SET @db1_lfile = 'C:program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA estdb_log.ldf';

    SET @db1_log = @db1+'_log';

    --takes database offline and move files
    EXEC('ALTER DATABASE '+@db1+' SET OFFLINE')

    EXEC('ALTER DATABASE '+@db1+' MODIFY FILE (NAME = '+@db1+', FILENAME ='+@db1_dfile+')')
    I can't get the Filename='@db1_dfile'+')' part to work because of the extra ' needed on either side of the file path. Is that possible? I wanted to build a script to easily move serveral databases
  6. Madhivanan Moderator

    use

    EXEC('ALTER DATABASE '+@db1+' MODIFY FILE (NAME = '''+@db1+''', FILENAME ='''+@db1_dfile+''')')
  7. charlii New Member

    I will recommend the variable SQL, it will help you alot.To get much awarness just check w3schools.com.
  8. FrankKalis Moderator

    Ahem, this is a 5-year old thread. [:)]

Share This Page