?
Solved

using BEGIN END with IF statement..

Posted on 2016-11-02
12
Medium Priority
?
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 1000 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 29

Expert Comment

by:Pawan Kumar
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
 
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 29

Accepted Solution

by:
Pawan Kumar earned 1000 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 29

Expert Comment

by:Pawan Kumar
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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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