Solved

using BEGIN END with IF statement..

Posted on 2016-11-02
12
40 Views
Last Modified: 2016-11-02
how can the below logic be made to work? (the goal is to delete a database only if it exists.. then this will be part of automated code.. the need for SINGLE_USER logic is to make sure the database is not in use when tried to be deleted..)

USE MASTER

IF
(
SELECT COUNT(*) FROM sys.databases WHERE name = 'db1'
)
=1
BEGIN
USE db1
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
USE master
DROP  DATABASE db1  
END

GO
0
Comment
Question by:25112
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 41870394
Below code can be used to drop the database.
USE MASTER

IF  EXISTS 
(SELECT name FROM master.dbo.sysdatabases WHERE name = 'Sample')
BEGIN
USE Sample
ALTER DATABASE Sample SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
USE master
DROP  DATABASE Sample 
END
ELSE
BEGIN
	SELECT 'database does not exist'
END 

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 41870460
anil, your code produces this error:

Msg 911, Level 16, State 1, Line 6
Database 'Sample' does not exist. Make sure that the name is entered correctly.
0
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 41870468
I have created a database 'Sample' on my local and then used above code to delete the Sample database. Now if I run above code on my machine I get same error message. So please try to replace the database name 'Sample' with you test database name.
0
 
LVL 5

Author Comment

by:25112
ID: 41870488
if database does not exist, should not this line be run:
SELECT 'database does not exist' (line #13)
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 250 total points
ID: 41870567
You don't need those nested USE statements - you don't need to switch context to run the ALTER DATABASE statement:
USE MASTER

IF  EXISTS 
(SELECT name FROM master.dbo.sysdatabases WHERE name = 'Sample')
BEGIN
        ALTER DATABASE Sample SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
        DROP  DATABASE Sample 
END
ELSE
BEGIN
	SELECT 'database does not exist'
END 

Open in new window

0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41870598
Try..

USE MASTER
GO

IF  EXISTS ( SELECT TOP 1 1 FROM MASTER.DBO.SYSDATABASES WHERE name = 'db1')
BEGIN
               
		ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE		
		DROP  DATABASE db1 
END

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Author Comment

by:25112
ID: 41870611
>>
        ALTER DATABASE Sample SET SINGLE_USER WITH  ROLLBACK IMMEDIATE            
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE      
>>

if you do SINGLE_USER without using
[USE SAMPLE]
prior,
another process could take hold of database, right? then the drop wont work??
0
 
LVL 18

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points
ID: 41870624
Are you on a prod server ?

Note - Please make sure that if you are on prod server then alter database should be used very very carefully.

No I think  If you SINGLE_USER  then other user cannot take hold of the DB.
0
 
LVL 5

Author Comment

by:25112
ID: 41870768
this is test server..

---
USE db1
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
---
will make sure you are the only user In the database..
then if you have the next line as
USE MASTER,
the database is likely to be ready to be dropped, if that is the process..

but, the below
---
USE master
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
---
how can that guarantee that all process has been kicked out of the database.. only one user can be in the database and it may not be you, because you are in master database, in this code segment.. not db1?
0
 
LVL 5

Author Comment

by:25112
ID: 41870770
>> you don't need to switch context to run the ALTER DATABASE statement:
carl, the context is not on ALTER, as much as keep the database free of all processes to be able to be dropped..
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41870772
Hi 25112,
Whats the update?

Is it done?
0
 
LVL 5

Author Comment

by:25112
ID: 41870818
confirmed. thanks.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Read about the 3 stages of the buyer's journey: awareness, consideration, and decision.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now