Showing posts with label ddl. Show all posts
Showing posts with label ddl. Show all posts

Sunday, March 11, 2012

Creating a new database

I am trying to create a new database, say name YYYData. I designed it in Vi
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:

> What is wrong? How do it fix it?
> Thanks.
|||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\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:

> informed that YYYData does not exist.
>
>
|||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...
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:

> informed that YYYData does not exist.
>
>
|||THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I canno
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:

> 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...
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.
> EagleRed
|||Thanks, guys. I knew I was "holding my mouth wrong on something"! ENJOY!!!
!

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!!!!

Friday, February 17, 2012

create\alter sp in schema privilage

I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?Gal (Gal@.discussions.microsoft.com) writes:
> I need to let my user no ddl changes in dbo schema, only
> creaete(new)/update permision in MySchema schema.
It's a little unclear what exactly what you want to permit in MySchema,
but to give them all, do
GRANT CONTROL ON SCHEMA::MySchema TO youruser
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

CREATE XML SCHEMA COLLECTION ddl statement?

what's the purpose of defining a "relational schema name" in the CREATE XML SCHEMA COLLECTION ddl statement? i don't understand why it's an option.

All objects in SQL Server 2005 database distributed among schemas.

Default schema is dbo. So when you create table "MyTable", its full name dbo.MyTable or Sales.MyTable etc. For more examples, please, see database AdventureWorks.

XML SCHEMA COLLECTION also included into one of database schemas.

|||

Konstantin Kosinsky wrote:

All objects in SQL Server 2005 database distributed among schemas.

Thanks for your reply, but I already knew this. What I previously didn't understand was the purpose of schemas. Anyway, I found the following in the BOL:

"The roles of schemas are enhanced in SQL Server databases. All objects in a database are now located in schemas and are not owned by individual users. Each schema can be owned by roles, allowing multiple users to administer database objects. In earlier versions of SQL Server, users could not be dropped from a database without having to reassign the ownership of each individual object that they owned. Now ownership only has to be adjusted for the schema, not each object." http://msdn2.microsoft.com/en-us/library/ms170800.aspx

Aha! Now it all makes sense to me.