sio and created a .DDL file. When I opened the file in Query Analyzer and r
an it, I was informed there was a file already present named, YYYData. I tr
ied DROP database YYYData a
nd sp_dbremove YYYData and both times I was informed that YYYData does not e
xist.
What is wrong? How do it fix it?
Thanks.You will have to follow the identifier rule.
http://msdn.microsoft.com/library/d... />
3_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...
quote:
> 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.
quote:|||This illustrates the dilemma:
> What is wrong? How do it fix it?
> Thanks.
-- IN SQL ANALYZER --
use master
go
create database [YYYData]
ON PRIMARY
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Da
ta\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
LOG ON
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Da
ta\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 foun
d 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 aft
er in another?
Thanks,
EagleRed
-- oj wrote: --
You will have to follow the identifier rule.
http://msdn.microsoft.com/library/d... />
3_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...
quote:
> 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.[QUOTE]|||> -- QUESTION --
quote:
> 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...
quote:
> 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 )
quote:
> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
quote:
> 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.
quote:
>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
quote:
> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>
http://msdn.microsoft.com/library/d...con_03_6e9e.asp[QUOTE
]
> e.g.
> create database [YYYData]
>
> --
> -oj
> http://www.rac4sql.net
>
> "EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com>[/QUOTE]
wrote
quote:
> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
it in[QUOTE]
> visio and created a .DDL file. When I opened the file in Query
Analyzer and
quote:
> ran it, I was informed there was a file already present named,
YYYData. I
quote:
> tried DROP database YYYData and sp_dbremove YYYData and both times I
was
quote:|||The logical names for the data and log are the same - "NAME = YYYYData" is
> informed that YYYData does not exist.
>
>
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...
quote:
> 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 )
quote:
> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
quote:
> 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.
quote:
>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
quote:
> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>
http://msdn.microsoft.com/library/d...con_03_6e9e.asp[QUOTE
]
> e.g.
> create database [YYYData]
>
> --
> -oj
> http://www.rac4sql.net
>
> "EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com>[/QUOTE]
wrote
quote:
> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
it in[QUOTE]
> visio and created a .DDL file. When I opened the file in Query
Analyzer and
quote:
> ran it, I was informed there was a file already present named,
YYYData. I
quote:
> tried DROP database YYYData and sp_dbremove YYYData and both times I
was
quote:|||THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I canno
> informed that YYYData does not exist.
>
>
t 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 o
n the server nor can I drop it or delete it with sp_dbremove. It seems I ha
ve a "zombie" DB that is dead but not gone. I would like to use the name YY
YData, since I have published the name in some documentation but I will chan
ge if I must. I would, how
ever, like to understand what is going on so I can at least avoid this probl
em in the future. I may have created a DB with this name in the past and de
leted it. I literally don't remember.
Thanks.
EagleRed|||I have consulted the docs and tried what was recommended there. But it isn't
working with a DB named YYYData. See my response to Dan Guzman for a bette
r description of my problem.
I appreciate your help!|||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...
quote:
> 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.
quote:|||create database [YYYData]
> Thanks.
> EagleRed
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...
quote:
> 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.
quote:|||Thanks, guys. I knew I was "holding my mouth wrong on something"! ENJOY!!!
> Thanks.
> EagleRed
!
No comments:
Post a Comment