Creating a new database

I am trying to create a new database, say name YYYData. I designed it in Visio and created a .DDL file. When I opened the file in Query Analyzer and ran it, I was informed there was a file already present named, YYYData. I tried DROP database YYYData and sp_dbremove YYYData and both times I was informed that YYYData does not exist.
What is wrong? How do it fix it?
Thanks.You will have to follow the identifier rule.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp
e.g.
create database [YYYData]
-oj
http://www.rac4sql.net
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
> I am trying to create a new database, say name YYYData. I designed it in
Visio and created a .DDL file. When I opened the file in Query Analyzer and
ran it, I was informed there was a file already present named, YYYData. I
tried DROP database YYYData and sp_dbremove YYYData and both times I was
informed that YYYData does not exist.
> What is wrong? How do it fix it?
> Thanks.|||This illustrates the dilemma
-- IN SQL ANALYZER -
use maste
g
create database [YYYData
ON PRIMAR
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB
LOG O
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
g
use [YYYData
g
--THE RESPONSE IN SQL ANALYZER -
Server: Msg 1828, Level 16, State 5, Line
The file named 'YYYData' is already in use. Choose another name
Server: Msg 911, Level 16, State 1, Line
Could not locate entry in sysdatabases for database 'YYYData'. No entry found with that name. Make sure that the name is entered correctly
-- QUESTION -
How can YYYData be already in use in one place and not found immediately after in another
Thanks
EagleRed
-- oj wrote: --
You will have to follow the identifier rule
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.as
e.g
create database [YYYData
--
-o
http://www.rac4sql.ne
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrot
in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com..
> I am trying to create a new database, say name YYYData. I designed it i
Visio and created a .DDL file. When I opened the file in Query Analyzer an
ran it, I was informed there was a file already present named, YYYData.
tried DROP database YYYData and sp_dbremove YYYData and both times I wa
informed that YYYData does not exist
>> What is wrong? How do it fix it
>> Thanks|||> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
Logical file names must be unique within a database. The CREATE DATABASE
statement should be something like:
CREATE DATABASE [YYYData]
ON PRIMARY
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
LOG ON
( NAME = YYYLog, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
--
Hope this helps.
Dan Guzman
SQL Server MVP
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:2A01B39B-8C77-4CFD-B95D-0C0155824361@.microsoft.com...
> This illustrates the dilemma:
> -- IN SQL ANALYZER --
> use master
> go
>
> create database [YYYData]
> ON PRIMARY
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> go
> use [YYYData]
> go
> --THE RESPONSE IN SQL ANALYZER --
> Server: Msg 1828, Level 16, State 5, Line 4
> The file named 'YYYData' is already in use. Choose another name.
> Server: Msg 911, Level 16, State 1, Line 2
> Could not locate entry in sysdatabases for database 'YYYData'. No entry
found with that name. Make sure that the name is entered correctly.
>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp
> e.g.
> create database [YYYData]
>
> --
> -oj
> http://www.rac4sql.net
>
> "EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com>
wrote
> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
> > I am trying to create a new database, say name YYYData. I designed
it in
> Visio and created a .DDL file. When I opened the file in Query
Analyzer and
> ran it, I was informed there was a file already present named,
YYYData. I
> tried DROP database YYYData and sp_dbremove YYYData and both times I
was
> informed that YYYData does not exist.
> >> What is wrong? How do it fix it?
> >> Thanks.
>
>|||The logical names for the data and log are the same - "NAME = YYYYData" is
used for both (but they must be unique)
Incidentally, the online help and BOL (Books On Line) have a complete
description of the syntax. Usually, this is the first place to start when
verifying syntax. In the description of create table, you will find:
logical_file_name
Is the name used to reference the file in any Transact-SQL statements
executed after the database is created. logical_file_name must be unique in
the database and conform to the rules for identifiers. The name can be a
character or Unicode constant, or a regular or delimited identifier.
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:2A01B39B-8C77-4CFD-B95D-0C0155824361@.microsoft.com...
> This illustrates the dilemma:
> -- IN SQL ANALYZER --
> use master
> go
>
> create database [YYYData]
> ON PRIMARY
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> go
> use [YYYData]
> go
> --THE RESPONSE IN SQL ANALYZER --
> Server: Msg 1828, Level 16, State 5, Line 4
> The file named 'YYYData' is already in use. Choose another name.
> Server: Msg 911, Level 16, State 1, Line 2
> Could not locate entry in sysdatabases for database 'YYYData'. No entry
found with that name. Make sure that the name is entered correctly.
>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp
> e.g.
> create database [YYYData]
>
> --
> -oj
> http://www.rac4sql.net
>
> "EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com>
wrote
> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
> > I am trying to create a new database, say name YYYData. I designed
it in
> Visio and created a .DDL file. When I opened the file in Query
Analyzer and
> ran it, I was informed there was a file already present named,
YYYData. I
> tried DROP database YYYData and sp_dbremove YYYData and both times I
was
> informed that YYYData does not exist.
> >> What is wrong? How do it fix it?
> >> Thanks.
>
>|||THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I cannot create a database named YYYData because SQL Server says the name is in use (not necessarily that a DB exists by that name). However, I cannot find it on the list of databases on the server nor can I drop it or delete it with sp_dbremove. It seems I have a "zombie" DB that is dead but not gone. I would like to use the name YYYData, since I have published the name in some documentation but I will change if I must. I would, however, like to understand what is going on so I can at least avoid this problem in the future. I may have created a DB with this name in the past and deleted it. I literally don't remember
Thanks
EagleRed|||Re-read Dan's post. Your problem has NOTHING to do with an existing
database named YYYData - it is a problem with the logical names given to the
primary data file and the log file. Dan has chosen to use the logical name
of YYYData for the primary data file and the name of YYYLog for the log
file.
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:0D2C87AB-2B9D-49A0-A7BA-85E5EE6481F5@.microsoft.com...
> THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I
cannot create a database named YYYData because SQL Server says the name is
in use (not necessarily that a DB exists by that name). However, I cannot
find it on the list of databases on the server nor can I drop it or delete
it with sp_dbremove. It seems I have a "zombie" DB that is dead but not
gone. I would like to use the name YYYData, since I have published the name
in some documentation but I will change if I must. I would, however, like
to understand what is going on so I can at least avoid this problem in the
future. I may have created a DB with this name in the past and deleted it.
I literally don't remember.
> Thanks.
> EagleRed|||create database [YYYData]
ON PRIMARY
( NAME = YYYData, /*first logical name*/
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.mdf',
SIZE = 3 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256 KB )
LOG ON
( NAME = YYYData, /*second logical name*/
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.ldf',
SIZE = 1 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256 KB )
The second logical name is where the error actually is. As stated, you
cannot have a duplicate logical name for a given database. You should change
the second one to something like YYYLog. Note: this has nothing to do with
the database name. It's just a logical name for the data or log file.
--
-oj
http://www.rac4sql.net
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:0D2C87AB-2B9D-49A0-A7BA-85E5EE6481F5@.microsoft.com...
> THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I
cannot create a database named YYYData because SQL Server says the name is
in use (not necessarily that a DB exists by that name). However, I cannot
find it on the list of databases on the server nor can I drop it or delete
it with sp_dbremove. It seems I have a "zombie" DB that is dead but not
gone. I would like to use the name YYYData, since I have published the name
in some documentation but I will change if I must. I would, however, like
to understand what is going on so I can at least avoid this problem in the
future. I may have created a DB with this name in the past and deleted it.
I literally don't remember.
> Thanks.
> EagleRed|||Thanks, guys. I knew I was "holding my mouth wrong on something"! ENJOY!!!!