creating an existing db schema baseline

What is the best method of creating schema creation scripts that can be
stored into a version control system. The process of using em to
generate a script is not an appealing option. I am still learning the
MS Sql sys tables and have not found a useful list of all the codes &
types to join the tables etc.

mike

--
Posted via http://dbforums.comwukie <member30544@.dbforums.com> wrote in message news:<3242331.1060980041@.dbforums.com>...
> What is the best method of creating schema creation scripts that can be
> stored into a version control system. The process of using em to
> generate a script is not an appealing option. I am still learning the
> MS Sql sys tables and have not found a useful list of all the codes &
> types to join the tables etc.
>
> mike

I don't like the fact that all source code versioning systems are
using proprietary files instead of proven relational databases
(SourceSafe is not exception from this). The reasons for this are
probably RDBMS licensing costs in the past.

Database schema can be exported also as XML file, which can be further
manipulated. If you and your team have serious schema versioning needs
I suggest you to evaluate Meta Data Services in SQL Server 2000 and
XML. One article about this has been published in the MSDN Magazine:
http://msdn.microsoft.com/msdnmag/i...es/default.aspx

Metadata Repository can be created not only through Enterprise Manager
but also programmatically using Meta Data API. Further information
with examples can be found in Meta Data Services SDK 3.0, which can be
downloaded for free.

Sinisa Catic|||found what I was looking for...

in EM > Tools > Generate SQL Scripts. THis will create the total schema
of the existing database.

mike

any known issues with this tool??

--
Posted via http://dbforums.com

Creating a Push Subscription on existing database.

My subscriber database has a subset of the tables in the Publisher but,
otherwise the schema is exactly the same.
Using the Push Subscription Wizard and the Initialize Subscription screen,
one is presented with two options: (I am using transactional publication)
-Yes, initialize the schema and data
-No, the Subscriber alreday has the schema and data
If I pick the first option, the initialization will fail because it tries to
drop the tables and views and my tables have relationships and contraints on
them.
If I pick the second option, the stored procedures used to update
(synchronize) the subcriber do not get created in the subcriber data base.
If I create a new database instead, everything works as expected.
How do I create a Push subscription where the table structure is already
there; but I do need to insure the stored procedures required by Replication
get created on the subscriber database?
Bill
William R
if you are running SQL 2k above sp1 do this in your publication database
sp_addpublication 'dummy'
sp_replicationdboption 'pubs','publish','true'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
ePublishingAndWantToGenerateAProcFor'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
rePublishingAndWantToGenerateAProcFor2'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
rePublishingAndWantToGenerateAProcFor3'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
rePublishingAndWantToGenerateAProcFor4'
sp_scriptpublicationcustomprocs 'dummy'
this will generate the procs you need in the results pane, script them out
and then issue a
sp_droppublication 'dummy'
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> My subscriber database has a subset of the tables in the Publisher but,
> otherwise the schema is exactly the same.
> Using the Push Subscription Wizard and the Initialize Subscription screen,
> one is presented with two options: (I am using transactional publication)
> -Yes, initialize the schema and data
> -No, the Subscriber alreday has the schema and data
> If I pick the first option, the initialization will fail because it tries
to
> drop the tables and views and my tables have relationships and contraints
on
> them.
> If I pick the second option, the stored procedures used to update
> (synchronize) the subcriber do not get created in the subcriber data base.
> If I create a new database instead, everything works as expected.
> How do I create a Push subscription where the table structure is already
> there; but I do need to insure the stored procedures required by
Replication
> get created on the subscriber database?
> Bill
> --
> William R
|||Hilary,
I was wondering if something like this would be the solution. Since there
are so many tables, I could use the create database option to create a dummy
subscriber and copy the procedures over to the real subcriber.
It seems rather odd, MS didn't think of such an option for the wizard though.
Thank you for your response.
Bill
"Hilary Cotter" wrote:

> if you are running SQL 2k above sp1 do this in your publication database
> sp_addpublication 'dummy'
> sp_replicationdboption 'pubs','publish','true'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
> ePublishingAndWantToGenerateAProcFor'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
> rePublishingAndWantToGenerateAProcFor2'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
> rePublishingAndWantToGenerateAProcFor3'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
> rePublishingAndWantToGenerateAProcFor4'
> sp_scriptpublicationcustomprocs 'dummy'
> this will generate the procs you need in the results pane, script them out
> and then issue a
> sp_droppublication 'dummy'
>
> "WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
> news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> to
> on
> Replication
>
>
|||That is another way, but it is more work.
I would advise you however to script out the publishing database, create a
database called pub, and a database called sub.
In pub, run the creation script. Then run your publication script (changing
the publication name), and then create and push your subscription to sub.
This way your snapshot generation time will be very very fast and the impact
on your publisher will be low.
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:AB0769C1-646E-4406-8B50-68A60BE62109@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I was wondering if something like this would be the solution. Since there
> are so many tables, I could use the create database option to create a
> dummy
> subscriber and copy the procedures over to the real subcriber.
> It seems rather odd, MS didn't think of such an option for the wizard
> though.
> Thank you for your response.
> Bill
>
> "Hilary Cotter" wrote:

Creating a database schema from a DTD?

Assuming a DTD would lead to a well defined schema, isn't
there a tool to map DTDs to relational schemas?
SQL Server does not support DTD since it gives not enough information about
types and cannot be easily annotated as the XML-based schema formats XDR and
W3C's XML Schema.
You can either use a tool to translate your DTD into one of the XML-based
schema formats or look for a third-party tool (I think Ron Bourret had one).
Best regards
Michael
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:167901c4e55b$3336f1c0$a601280a@.phx.gbl...
> Assuming a DTD would lead to a well defined schema, isn't
> there a tool to map DTDs to relational schemas?

Creating a database schema from a DTD?

Assuming a DTD would lead to a well defined schema, isn't
there a tool to map DTDs to relational schemas?SQL Server does not support DTD since it gives not enough information about
types and cannot be easily annotated as the XML-based schema formats XDR and
W3C's XML Schema.
You can either use a tool to translate your DTD into one of the XML-based
schema formats or look for a third-party tool (I think Ron Bourret had one).
Best regards
Michael
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:167901c4e55b$3336f1c0$a601280a@.phx.gbl...
> Assuming a DTD would lead to a well defined schema, isn't
> there a tool to map DTDs to relational schemas?

creating a database

Hi;
If I have a schema for creating a database, is there a command line
application I can run and pass it the filename of the schema and it will then
create that database? If so, can someone point me to a url showing the
command line syntax for it?
And, what registry entry can I use to determine where this program is - as
some people do not install in C:\Program Files\...
thanks - dave
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Maybe this helps. http://support.microsoft.com/kb/325003
Use the variable %programfiles%
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| Hi;
|
| If I have a schema for creating a database, is there a command line
| application I can run and pass it the filename of the schema and it will
then
| create that database? If so, can someone point me to a url showing the
| command line syntax for it?
|
| And, what registry entry can I use to determine where this program is - as
| some people do not install in C:\Program Files\...
|
| thanks - dave
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|
|
|||osql is the tool I need.
I am retecient to use %programfiles% because we used to put Sql Server on d:
while programfiles was on c: - so on those systems at least, that would not
find it.
Is there anything Sql Server writes to the registry with it's location? I
think there was and I used it in a program where I used to work - but I don't
have access to that code.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> Maybe this helps. http://support.microsoft.com/kb/325003
> Use the variable %programfiles%
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | Hi;
> |
> | If I have a schema for creating a database, is there a command line
> | application I can run and pass it the filename of the schema and it will
> then
> | create that database? If so, can someone point me to a url showing the
> | command line syntax for it?
> |
> | And, what registry entry can I use to determine where this program is - as
> | some people do not install in C:\Program Files\...
> |
> | thanks - dave
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
> |
> |
>
>
|||You could look at 'Path' and or 'SQLPath' found below but you really
shouldn't need the fully qualified path;
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
since the installation of client tools appends to the system path variable.
C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
Files\Microsoft SQL Server\80\Tools\Binn\

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| osql is the tool I need.
|
| I am retecient to use %programfiles% because we used to put Sql Server on
d:
| while programfiles was on c: - so on those systems at least, that would
not
| find it.
|
| Is there anything Sql Server writes to the registry with it's location? I
| think there was and I used it in a program where I used to work - but I
don't
| have access to that code.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|||thank you - that looks perfect.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> You could look at 'Path' and or 'SQLPath' found below but you really
> shouldn't need the fully qualified path;
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
> since the installation of client tools appends to the system path variable.
> C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
> Files\Microsoft SQL Server\80\Tools\Binn\
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | osql is the tool I need.
> |
> | I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> | while programfiles was on c: - so on those systems at least, that would
> not
> | find it.
> |
> | Is there anything Sql Server writes to the registry with it's location? I
> | think there was and I used it in a program where I used to work - but I
> don't
> | have access to that code.
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
|||You can use sp_helpfile to return the file locations.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:FC83AF0A-B244-4B64-A83F-36A21A6ADECA@.microsoft.com...[vbcol=seagreen]
> osql is the tool I need.
> I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> while programfiles was on c: - so on those systems at least, that would
> not
> find it.
> Is there anything Sql Server writes to the registry with it's location? I
> think there was and I used it in a program where I used to work - but I
> don't
> have access to that code.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
> "Dave Patrick" wrote:
|||You're welcome.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| thank you - that looks perfect.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Create a backup and restore that on to the machine.
Simple and fast.
Mark Baekdal
www.dbghost.com
DB Ghost rocks!
>--Original Message--
>Hi, how to create a single script file that contains db
schema and data?
>(Moving db to another machine with sql script file) . I
have tried data
>export wizard, but it does not export data, but only
schema of db.
>
>.
>|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

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 Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

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.