Dropping a database

  • rsharma

    SSCarpal Tunnel

    Points: 4865

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rsharma/droppingadatabase.asp

  • pattikay

    Old Hand

    Points: 349

    Hi Rahul,

    I will find the script very useful,tho i have one question,

    what are u executing when u said :

    EXEC SetMessages

    Thanks.

  • rsharma

    SSCarpal Tunnel

    Points: 4865

    Patti:

    The script has an error....I was using SetMessages as my stored proc for having my custom error messages. You can remove that piece of code from the first proc usp_DropDB and you will be fine or alternatively you can make a change to the usp_ForceDropDb stored procedure and replace the statement in the end:

    "EXEC usp_DropDB @DatabaseName"

    with

    SET @DatabaseName = QUOTENAME(@DatabaseName)

    EXEC ('DROP DATABASE ' + @DatabaseName )

    Hth,

    Rahul

    Rahul Sharma

    Senior Database Administrator

    Manhattan Associates

    Atlanta, GA

  • jonkusnir

    Newbie

    Points: 7

    Hi, thanks for this one!

    Mnd goal is to publish a content database from staging to live, safely, from an admin web page. The wrinkle is that the DB must remain alive on the web during the process ... I plan to modify the DSNs on the fly.

    One small point, when I use this in SQL 7 it returns an error "You can't kill your own process."

    This error was causing the calling DTS package to fail on this step.

    I simply added a clause to the SELECT of spids to exclude "@@SPID" which is the current process.

    SELECT spid

    FROM master.dbo.sysprocesses

    WHERE dbid =

    (SELECT dbid FROM master.dbo.sysdatabases

    WHERE name = @DatabaseName)

    AND spid <> @@SPID

    ^^^^^^^^^^^^^

    It worked fine and once again I appreciate the resource, I don't know much T-SQL ... I'm actually a web guy using Cold Fusion with SQL Server.

    - Jon

Viewing 4 posts - 1 through 4 (of 4 total)

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