Solved

SQL Script Error - DROP and CREATE

Posted on 2011-03-05
2
178 Views
Last Modified: 2012-08-14
I am having a problem with a simple SQL Server script.  For some reason, after I drop the PIMTool database it does not get created.  The "IF not EXISTS (Sel....*" on line 5 is not getting executed.  Because the PIMTool DB does not created I get an error message on line 8 on the "Use PIMTool".  ANy suggestions?

use Master;
if exists (SELECT name FROM Master.dbo.sysdatabases WHERE name = N'PIMTool')
drop DATABASE [PIMTool];

IF not EXISTS (SELECT name FROM Master.dbo.sysdatabases WHERE name = N'PIMTool')
CREATE DATABASE [PIMTool];

use PIMTool;

Server: Msg 911, Level 16, State 1, Line 8
Could not locate entry in sysdatabases for database 'PIMTool'. No entry found with that name. Make sure that the name is entered correctly.
0
Comment
Question by:Hojoformo
2 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 35044197
I don't think you can create the database and use it (with a "use" statement) in the same script... You can try running your script with an EXEC instruccion and your statement saved in a variable.

Not 100% sure about this, but I tried it and it didn't work for me.
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35044359
Add GO to your statements


use Master;
if exists (SELECT name FROM Master.dbo.sysdatabases WHERE name = N'PIMTool')
drop DATABASE [PIMTool];
GO

IF not EXISTS (SELECT name FROM Master.dbo.sysdatabases WHERE name = N'PIMTool')
CREATE DATABASE [PIMTool];
GO

use PIMTool;
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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