Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    72

    Unanswered: Unable to execute drop database from the script

    It's fetching the dbname peroperly from the cursor and printing the same, but not able to execute the drop command . can someone please help me on this.
    ---------------------------------------------

    declare db_loop cursor for
    select name from sysdatabases where dbid in(7,10,11,12)
    go

    declare @dbname char(35)
    open db_loop
    fetch db_loop into @dbname
    while (@@sqlstatus = 0)
    begin
    select 'dbname is ' + @dbname
    if exists (select 1 from sysprocesses where dbid = db_id(@dbname))
    begin
    select " Killing users in the database " + rtrim(@dbname)
    declare @sql varchar(50)
    while exists (select 1 from sysprocesses where dbid = db_id(@dbname))
    begin
    select @sql = 'kill ' + convert(varchar,min(spid)) from sysprocesses where dbid = db_id(@dbname)
    print '%1!', @sql
    exec (@sql)
    waitfor delay '00:00:02'
    end
    drop database @dbname
    end
    print 'Droping the database ...'
    fetch db_loop into @dbname
    end
    close db_loop
    deallocate cursor db_loop
    go

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Are you getting an error? If so, which error?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jan 2010
    Posts
    72
    Here is the output :
    Here it is fetching the row from cursor correctly, i.e database name is test.
    but when it comes to drop, it's not doing.
    ---------------------------
    dbname is test
    Droping the database ...
    Msg 2520, Level 16, State 5:
    Server 'INDIA', Line 24:
    Database named test not found; check sysdatabases
    -----------------------------------------

    Thank you.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    try
    exec ('drop database '+@dbname)

  5. #5
    Join Date
    Jan 2010
    Posts
    72
    Thank you. It's working now.

    Though I posted the script which has

    "drop database @db_name"

    I also tried "exec drop database @db_name" but this also didn't work.

    Can you also sugget me one thing like how to know that this command has to be executed like " exec ('drop database' + @db_name)

    Once again thank you.

Posting Permissions

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