Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

using BEGIN END with IF statement..

Posted on 2016-11-02
12
Medium Priority
?
71 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public 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 32

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 32

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 32

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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