Solved

SQL Script Error - DROP and CREATE

Posted on 2011-03-05
2
175 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

18 Experts available now in Live!

Get 1:1 Help Now