Drop Database problem

[Saama5]

create Procedure DropDatabase
@a int = 0
as
Declare @SqlStmt nvarchar(125)
declare @dbName nvarchar(25)
if @a>0
begin
select @dbName = DatabaseName from xx.dbo.xyz where RecId = @a
if @dbName is not NULL
begin
set @sqlStmt = 'Use Master;Drop Database '+ @dbName
exec sp_executeSql @sqlStmt
end
end

My procedure is not dropping database. I am getting databasename from another database

 

[Gundapaneni]

Hi,
Sql server will not allow you to use the USE statement inside a Stored Procedure. So it might be the problem. Check once again your stored procedure and modify it as what you want.

Madhu.

 

[tlbroadbent]

The Use statement in the dynamic execute temporarily changes the database context. That is perfectly acceptable in SQL Server.

Databases cannot be dropped if in use. "DROP DATABASE permissions default to the database owner, members of the sysadmin and dbcreator fixed server roles, and are not transferable." (See SQL BOL)

Do you receive an error message when the SP runs? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Saama5]

Yes I received message that it is open by another user. Can I disconnect that user and then drop that database.

 

[tlbroadbent]

A connection can be killed if the login has System Administration privileges. Use the Kill <spid> command.

There is a script available at SWYNK.COM that will create a stored procedure for killing connections to a specified database.

http://www.swynk.com/friends/knight/killusers.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.