SQL Generate Script Not Creating Database

Question

I created a script of my database.

But When I run it, the script does not create the database. It skips the "Create db" statement, and only creates the tables (on the database I have selected at the moment, so not ideal....)

(query executes with no errors by the way.)

Why is this happening? why cant you create a database and edit the content in it in one go?

(I know you can check if the db exist first, but this shouldn't be happening from the start)

--My Script--

CREATE DATABASE [EthicsDB] 

USE [EthicsDB]
go

CREATE TABLE [dbo].[TempEmployee](
    [PersonnelNumber] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](80) NULL,
    [SurName] [varchar](80) NULL,
    [ManagerEmail] [varchar](80) NULL,
    [ManagerName] [varchar](80) NULL,
 CONSTRAINT [PK_TempEmployee] PRIMARY KEY CLUSTERED 
(
    [PersonnelNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

tezzo · Accepted Answer · 2013-06-27 09:10:39Z

5

You must use GO after CREATE DATABASE [EthicsDB].

answered Jun 27, 2013 at 9:10

tezzo

11.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

armitage · Accepted Answer · 2013-06-27 09:21:50Z

If you run the SQL as provided you get an error message on the line

USE [EthicsDB]

This occurs as when SQL Servers runs SQL commands via SQL CMD it process the SQL in batches.

As you have no GO statement after the Create database statement it maybe that SQL Server does not yet recognise that a new database Ethics has been created and thus when you attempt to use the database via USE [EthicsDB] the statement fails.

As your SQL Statements are not wrapped in a transaction and as you are not checking for errors then if SQL Server encounters an error it will raise the error but also continue to process the rest of the query.

In the query provided this leads to the new tables being created in the current database.

To correct the problem modify your query to

CREATE DATABASE [EthicsDB] 
go
USE [EthicsDB]
go

Devart · Accepted Answer · 2013-06-27 09:10:03Z

1

Try this one -

USE [master]
GO

IF  EXISTS (
    SELECT 1 FROM sys.databases WHERE name = N'EthicsDB'
)
DROP DATABASE [EthicsDB]
GO

CREATE DATABASE [EthicsDB]
GO --<----

USE [EthicsDB]
GO

CREATE TABLE [dbo].[TempEmployee](
    [PersonnelNumber] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](80) NULL,
    [SurName] [varchar](80) NULL,
    [ManagerEmail] [varchar](80) NULL,
    [ManagerName] [varchar](80) NULL,
 CONSTRAINT [PK_TempEmployee] PRIMARY KEY CLUSTERED 
(
    [PersonnelNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

answered Jun 27, 2013 at 9:10

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Mark · Accepted Answer · 2013-06-27 09:08:49Z

0

You should probably wrap each action in a transaction block.

Also, when you are creating the table I generally do a check to see if it already exists first.

If you run only the create database, what happens?

answered Jun 27, 2013 at 9:08

Mark

1

Collectives™ on Stack Overflow

SQL Generate Script Not Creating Database

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related