January 30, 2012 at 7:23 am
Hello friends,
I am trying to create one database and tables through DDL, I’ve everything in one window and when I run the following query I get the error message that the database does not exist. Not sure why this is happening since in the query the first statement is to create database after that I used the Use command to use the newly created database so that table DDL can create the tables in the newly created database but I get an error below
Msg 911, Level 16, State 1, Line 1
Database 'test' does not exist. Make sure that the name is entered correctly.
My Script
USE [master]
GO
/****** Object: Database [test] Script Date: 01/30/2012 08:46:45 ******/
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'D:\DBDATA\DATA1\DATA\test.mdf' , SIZE = 133120KB , MAXSIZE = 4194304KB , FILEGROWTH = 131072KB )
LOG ON
( NAME = N'test_log', FILENAME = N'D:\DBLOGS\LOG1\LOGS\test_log.ldf' , SIZE = 132096KB , MAXSIZE = 1048576KB , FILEGROWTH = 131072KB )
GO
ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [test].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [test] SET ANSI_NULLS OFF
GO
ALTER DATABASE [test] SET ANSI_PADDING OFF
GO
ALTER DATABASE [test] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [test] SET ARITHABORT OFF
GO
ALTER DATABASE [test] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [test] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [test] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [test] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [test] SET DISABLE_BROKER
GO
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [test] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [test] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [test] SET READ_WRITE
GO
ALTER DATABASE [test] SET RECOVERY FULL
GO
ALTER DATABASE [test] SET MULTI_USER
GO
ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [test] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'test', N'ON'
GO
USE [test]
GO
/****** Object: Table [dbo].[test2] Script Date: 01/30/2012 08:46:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test2](
[CompanyID] [int] NULL,
[Location] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[test] Script Date: 01/30/2012 08:46:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[ID] [int] NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
January 30, 2012 at 7:27 am
Any error in creating the database?
I've seen errors on Use commands before, like what you're getting, when there was a problem with the database being created.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2012 at 7:50 am
The SQL query is first compiled and then executed. Some SQL statements will allow you to use optimistic name resolution to refer to objects that have not yet been created, but USE is not one of them. The USE statement won't compile, because the object it refers to has not been created yet. Since the statement won't compile, it can't be executed.
Separate your database creation script from the rest of your scripts somehow. I would recommend having two separate files, but you could use dynamic SQL if they absolutely have to be in one file.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2012 at 8:18 am
drew.allen (1/30/2012)
Separate your database creation script from the rest of your scripts somehow.
It's already separate. There's a GO after the create database, so any statements after that will parse and compile after the database has been created (assuming the create doesn't fail)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2012 at 8:19 am
sqlquest2575 (1/30/2012)
Msg 911, Level 16, State 1, Line 1Database 'test' does not exist. Make sure that the name is entered correctly.
Did the create database throw any error? Which line does that refer to? (double-click the error to go to the line)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2012 at 8:25 am
You will see that error if you parse the code in SSMS, running it should work though. Odd.
January 30, 2012 at 9:41 am
Thank you guys for your quick reply if i run my script (Db creation and Table Creation) together, I get an error but if I run DB creation and Table creating scripts individually they work fine but again if i use the script the way i posted above ...i get an error...
January 30, 2012 at 9:49 am
I just copy-and-pasted your script into SSMS and ran it. I had to remove the drive/file specification for the database, but otherwise left it as-is.
I got the same error when I clicked on the Parse button in SSMS, but did *not* get an error when I just executed the script. Does that match what you were doing, or did you get the error when you tried to execute the script?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2012 at 10:06 am
i got the error even while execution and also parsing the query..not sure how it worked for you
January 31, 2012 at 6:44 am
What version and edition of SQL Server did you run it on? I'm on 2008 R2 SP1 Dev Edition.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 6:51 am
Runs fine on SQL 2008 SP3 too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply