Create tables in SQL2K from XSD schema?

I am in need of some help!
Relatively new to the whole XML thing as far as SQL server goes...
I have an XSD schema that describes a bunch of tables with various
relationships. Is there a way for me to create tables and relationships based
on the XSD file.
I have tried the net but seem to be unable to find anything. Any help would
be greatly appreciated.
Matt
If at first you don't succeed... Hide the evidence that you tried!
In theory yes. Please see the link
http://support.microsoft.com/default...;en-us;316005. You have to
set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
hand is a very daunting task and I never able to make it work for complex
shcemas. Are you using some tool to generate the XSD shcema annotaion?
"Director - Minvent" wrote:

> I am in need of some help!
> Relatively new to the whole XML thing as far as SQL server goes...
> I have an XSD schema that describes a bunch of tables with various
> relationships. Is there a way for me to create tables and relationships based
> on the XSD file.
> I have tried the net but seem to be unable to find anything. Any help would
> be greatly appreciated.
> Matt
> --
> If at first you don't succeed... Hide the evidence that you tried!
|||The schema was written using XMLspy by one of the guys at Agilent
technologies (HP) as part of one of their products... Unfortunately it is not
intended for use and therefore is not supported, but hopefully tested prior
to release!
Basically it is the definition of an xml output file from an analytical
instrument which I want to capture data from. Hence trying to create a
database based on its definition.
I'm using VB.net and desperately trying to find a namespace containing
sqlxml?!? I am probably being really stupid... which wouldn't suprise me!.
Cheers
"Rashid" wrote:
[vbcol=seagreen]
> In theory yes. Please see the link
> http://support.microsoft.com/default...;en-us;316005. You have to
> set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
> hand is a very daunting task and I never able to make it work for complex
> shcemas. Are you using some tool to generate the XSD shcema annotaion?
> "Director - Minvent" wrote:

create table(s) from xml schema

I'm using SQL 2005.

I have an XML schema that is maintained by an outside source and I'd like to use it to create the table structures. Is there a way to do this without manually creating the tables? I will be receiving data files that will need to be validated against the schema and then uploaded into a database.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

sql

create table(s) from xml schema

I'm using SQL 2005.

I have an XML schema that is maintained by an outside source and I'd like to use it to create the table structures. Is there a way to do this without manually creating the tables? I will be receiving data files that will need to be validated against the schema and then uploaded into a database.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

CREATE Table permission

I gave a user explicit permission to create table, but still getting
this error:

Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors

Do I need to create a new schema?

--sharifSharif Islam (mislam@.npspam.uiuc.edu) writes:

Quote:

Originally Posted by

I gave a user explicit permission to create table, but still getting
this error:
>
Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors

Sounds like you are using some graphical tool in SSMS. Those tools are
of poor quality, and I recommend that you try CREATE TABLE instead.

But only CREATE TABLE may not be sufficient. The user may also need ALTER
permission on the schema (for instance dbo).

--
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 table in schema with Enterprise Manager

How can I specify the schema I want the table to be created in with
Enterprise Manager 2005?
It justs asks for a table name and if you put a schema name in it just
treats it as part of the table name
Paul
Paul Hatcher (PaulHatcher@.discussions.microsoft.com) writes:
> How can I specify the schema I want the table to be created in with
> Enterprise Manager 2005?
> It justs asks for a table name and if you put a schema name in it just
> treats it as part of the table name
CREATE TABLE schemaname.tbl
Let the graphic tools be. They are some funny toys that have ended up in
the wrong place. And, whatever, never use them to change your tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Create table in schema in Managment Studio

Hi all, if I create a new schema:
CREATE SCHEMA MySchema
it appears in Sql Server Management Studio's schema, but it's not
obvious to me how to create a new table (or other object) under this
schema, without using the CREATE TABLE statement. How can I create a
new table under this schema using the designed in Managment Studio?
This is Sql Server 2005 (Standard Edition) I'm using.
Thanks!
Richard
Hello, Richard
When you create a new table using Management Studio, choose "View /
Properties Window" (or press F4) and select the desired schema in the
combo for the "Schema" property (under the "(Identity)" category).
Razvan

Create table in schema in Managment Studio

Hi all, if I create a new schema:
CREATE SCHEMA MySchema
it appears in Sql Server Management Studio's schema, but it's not
obvious to me how to create a new table (or other object) under this
schema, without using the CREATE TABLE statement. How can I create a
new table under this schema using the designed in Managment Studio?
This is Sql Server 2005 (Standard Edition) I'm using.
Thanks!
RichardHello, Richard
When you create a new table using Management Studio, choose "View /
Properties Window" (or press F4) and select the desired schema in the
combo for the "Schema" property (under the "(Identity)" category).
Razvan

Create table in schema in Managment Studio

Hi all, if I create a new schema:
CREATE SCHEMA MySchema
it appears in Sql Server Management Studio's schema, but it's not
obvious to me how to create a new table (or other object) under this
schema, without using the CREATE TABLE statement. How can I create a
new table under this schema using the designed in Managment Studio?
This is Sql Server 2005 (Standard Edition) I'm using.
Thanks!
RichardHello, Richard
When you create a new table using Management Studio, choose "View /
Properties Window" (or press F4) and select the desired schema in the
combo for the "Schema" property (under the "(Identity)" category).
Razvan

Create table in schema

Hi, I'm trying to create a schema, and have the userid assigned to a role
have the ability to create tables just in this schema. I have created a rol
e
A_Role and want to assign all the permissions to the role. So I tried the
commands:
grant alter on schema::dds to A_Role
grant create table to dds_pco_role
Then I've added the userid to this role. Logged in as the user, but when I
try to create a table
create table dds.T1 (col1 int, col2 char(3))
I get the message:
The specified schema name "dds" either does not exist or you do not have
permission to use it.
And yes, the schema does exist. Am I missing another command?
Thanks,
MitcheYou granted the CREATE TABLE to dds_pco_role - not A_role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
Hi, I'm trying to create a schema, and have the userid assigned to a role
have the ability to create tables just in this schema. I have created a
role
A_Role and want to assign all the permissions to the role. So I tried the
commands:
grant alter on schema::dds to A_Role
grant create table to dds_pco_role
Then I've added the userid to this role. Logged in as the user, but when I
try to create a table
create table dds.T1 (col1 int, col2 char(3))
I get the message:
The specified schema name "dds" either does not exist or you do not have
permission to use it.
And yes, the schema does exist. Am I missing another command?
Thanks,
Mitche|||Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role.
"Tom Moreau" wrote:

> You granted the CREATE TABLE to dds_pco_role - not A_role.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
> Hi, I'm trying to create a schema, and have the userid assigned to a role
> have the ability to create tables just in this schema. I have created a
> role
> A_Role and want to assign all the permissions to the role. So I tried the
> commands:
> grant alter on schema::dds to A_Role
> grant create table to dds_pco_role
> Then I've added the userid to this role. Logged in as the user, but when
I
> try to create a table
> create table dds.T1 (col1 int, col2 char(3))
> I get the message:
> The specified schema name "dds" either does not exist or you do not have
> permission to use it.
> And yes, the schema does exist. Am I missing another command?
> Thanks,
> Mitche
>
>|||Just trying to narrow things down. Try:
grant CONTROL on schema::dds to A_Role
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role.
"Tom Moreau" wrote:

> You granted the CREATE TABLE to dds_pco_role - not A_role.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
> Hi, I'm trying to create a schema, and have the userid assigned to a role
> have the ability to create tables just in this schema. I have created a
> role
> A_Role and want to assign all the permissions to the role. So I tried the
> commands:
> grant alter on schema::dds to A_Role
> grant create table to dds_pco_role
> Then I've added the userid to this role. Logged in as the user, but when
> I
> try to create a table
> create table dds.T1 (col1 int, col2 char(3))
> I get the message:
> The specified schema name "dds" either does not exist or you do not have
> permission to use it.
> And yes, the schema does exist. Am I missing another command?
> Thanks,
> Mitche
>
>|||I'm getting:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.
I also got that when I ran grant alter on schema. What does it mean?
"Tom Moreau" wrote:

> Just trying to narrow things down. Try:
> grant CONTROL on schema::dds to A_Role
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
> Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role
.
> "Tom Moreau" wrote:
>
>|||I think you maybe had run EXECUTE AS and didn't run REVERT. Thus, it thinks
you are the user you're pretending to be.
Run:
SELECT CURRENT_USER
and see what it says.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:18DF8BA6-E12C-4363-BEEA-5169633F66D1@.microsoft.com...
I'm getting:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.
I also got that when I ran grant alter on schema. What does it mean?
"Tom Moreau" wrote:

> Just trying to narrow things down. Try:
> grant CONTROL on schema::dds to A_Role
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
> Sorry, that was just a typo in my mail. It's all dds_pco_role, not
> A_role.
> "Tom Moreau" wrote:
>
>|||OK, I'd just log out and back in as sa, just to be sure we're starting
clean. Then, run:
grant CONTROL on schema::dds to A_Role
After that, start a brand new window and log in as dds_user. Try creating
the table then.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
No, there's no EXECUTE AS in my script. And when I run select current_user
in the window that I'm trying to grant the rights (logged in as SA), I get
"dbo."
When I run select current_user in the window I'm trying to create the table,
logged in as dds_user, I get "dds_user."
"Tom Moreau" wrote:

> I think you maybe had run EXECUTE AS and didn't run REVERT. Thus, it
> thinks
> you are the user you're pretending to be.
> Run:
> SELECT CURRENT_USER
> and see what it says.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:18DF8BA6-E12C-4363-BEEA-5169633F66D1@.microsoft.com...
> I'm getting:
> Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
> information_schema, sys, or yourself.
> I also got that when I ran grant alter on schema. What does it mean?
> "Tom Moreau" wrote:
>
>|||Same thing.
Have I set up the role incorrectly? Here's my script
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'dds_user
')
CREATE LOGIN [dds_user] WITH PASSWORD='DDSm@.st3r', CHECK_EXPIRATION=OFF
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name =
'dds_user' and type = 'S')
CREATE USER [dds_user] FOR LOGIN [dds_user]
IF EXISTS(select name from sys.database_principals where name =
'dds_pco_role' and type = 'R')
DROP ROLE dds_pco_role
CREATE ROLE dds_pco_role AUTHORIZATION dds_user
IF NOT EXISTS(select name from sys.schemas where name = 'dds')
EXEC sys.sp_executesql N'CREATE SCHEMA [dds] AUTHORIZATION [dds_pco_
role]'
EXEC sp_addrolemember 'dds_pco_role', 'dds_user'
-- grants for dds schema
grant control on schema::dds to dds_pco_role
grant create table to dds_pco_role
Thanks!
"Tom Moreau" wrote:

> OK, I'd just log out and back in as sa, just to be sure we're starting
> clean. Then, run:
> grant CONTROL on schema::dds to A_Role
> After that, start a brand new window and log in as dds_user. Try creating
> the table then.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
> No, there's no EXECUTE AS in my script. And when I run select current_use
r
> in the window that I'm trying to grant the rights (logged in as SA), I get
> "dbo."
> When I run select current_user in the window I'm trying to create the tabl
e,
> logged in as dds_user, I get "dds_user."
> "Tom Moreau" wrote:
>
>|||This all worked for me. What I did was run the entire script in
AdventureWorks. I then opened a new window and then ran:
execute as user = 'dds_user'
Then, I ran:
create table dds.T1 (col1 int, col2 char(3))
It executed OK.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:BB085D07-78A2-4DB6-A04B-BD56E11FB370@.microsoft.com...
Same thing.
Have I set up the role incorrectly? Here's my script
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name =
'dds_user')
CREATE LOGIN [dds_user] WITH PASSWORD='DDSm@.st3r', CHECK_EXPIRATION=OFF
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name =
'dds_user' and type = 'S')
CREATE USER [dds_user] FOR LOGIN [dds_user]
IF EXISTS(select name from sys.database_principals where name =
'dds_pco_role' and type = 'R')
DROP ROLE dds_pco_role
CREATE ROLE dds_pco_role AUTHORIZATION dds_user
IF NOT EXISTS(select name from sys.schemas where name = 'dds')
EXEC sys.sp_executesql N'CREATE SCHEMA [dds] AUTHORIZATION [dds_pco_
role]'
EXEC sp_addrolemember 'dds_pco_role', 'dds_user'
-- grants for dds schema
grant control on schema::dds to dds_pco_role
grant create table to dds_pco_role
Thanks!
"Tom Moreau" wrote:

> OK, I'd just log out and back in as sa, just to be sure we're starting
> clean. Then, run:
> grant CONTROL on schema::dds to A_Role
> After that, start a brand new window and log in as dds_user. Try creating
> the table then.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
> No, there's no EXECUTE AS in my script. And when I run select
> current_user
> in the window that I'm trying to grant the rights (logged in as SA), I get
> "dbo."
> When I run select current_user in the window I'm trying to create the
> table,
> logged in as dds_user, I get "dds_user."
> "Tom Moreau" wrote:
>
>|||I don't get it. What version of sql are you running?
Every time I try to grant control to the dds_pco_role, I get the message:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.
And then I check the permissions in the sys.database_permissions table, and
that permission is not there. I don't get why it's not working?!?!?!
"Tom Moreau" wrote:

> This all worked for me. What I did was run the entire script in
> AdventureWorks. I then opened a new window and then ran:
> execute as user = 'dds_user'
> Then, I ran:
> create table dds.T1 (col1 int, col2 char(3))
> It executed OK.
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:BB085D07-78A2-4DB6-A04B-BD56E11FB370@.microsoft.com...
> Same thing.
> Have I set up the role incorrectly? Here's my script
> IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name =
> 'dds_user')
> CREATE LOGIN [dds_user] WITH PASSWORD='DDSm@.st3r', CHECK_EXPIRATION=OF
F
> IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name =
> 'dds_user' and type = 'S')
> CREATE USER [dds_user] FOR LOGIN [dds_user]
> IF EXISTS(select name from sys.database_principals where name =
> 'dds_pco_role' and type = 'R')
> DROP ROLE dds_pco_role
> CREATE ROLE dds_pco_role AUTHORIZATION dds_user
> IF NOT EXISTS(select name from sys.schemas where name = 'dds')
> EXEC sys.sp_executesql N'CREATE SCHEMA [dds] AUTHORIZATION [dds_pc
o_role]'
> EXEC sp_addrolemember 'dds_pco_role', 'dds_user'
> -- grants for dds schema
> grant control on schema::dds to dds_pco_role
> grant create table to dds_pco_role
> Thanks!
> "Tom Moreau" wrote:
>
>sql

create table from xml schema

Hi everybody!
I need to create a table in SQL server from the xml schema of a file. Is
it possible?
Thanks a lot.
Anahi
Download SQLXML 3.0 from the MS SQL Server Website.
Check out the SQLXML Bulk Load component.
I think you can write a script in a DTS package and load data.
check out this article:
http://support.microsoft.com/default...b;en-us;316005
until MS Support replies.
"Anahi Luduea" <anahi@.optience.com> wrote in message
news:%23ANr9j0cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi everybody!
> I need to create a table in SQL server from the xml schema of a file. Is
> it possible?
> Thanks a lot.
> Anahi
>

CREATE TABLE [database . [schema1 ] . | schema1. ] tableName

Hi,
I try to create 2 schema as the syntax display's as below, but I get a error
as below. Do I have to change any parameters in SQL 2005?
And will I get any other problems in the future to use 2 schema?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
Create Schema.TableName (No Problem)
Create Schema1.schema2.TableName (Problem SQL see schema1 as the database)
Create Databasename,Schema1.schema2.TableName (Error below)
Msg 117, Level 15, State 1, Line 13
The object name 'DatabasName.Schema1. Schema2.TableName' contains more than
the maximum number of prefixes. The maximum is 2.
Msg 319, Level 15, State 1, Line 84
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.A table can belong to only a single schema. The pipe in the syntax is a
choice, not a concatonation. Things like below are valid ways to specify
a table name
CREATE TABLE database..name
schema is the default schema for the user
CREATE TABLE database.schema.name
database is the database in use
CREATE TABLE schema.name
CREATE TABLE name
database is the database in use and schema is the default schema for the use
r
Dan

> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a
> error
> as below. Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
> Create Schema.TableName (No Problem)
> Create Schema1.schema2.TableName (Problem SQL see schema1 as the
> database)
> Create Databasename,Schema1.schema2.TableName (Error below)
> Msg 117, Level 15, State 1, Line 13
> The object name 'DatabasName.Schema1. Schema2.TableName' contains more
> than
> the maximum number of prefixes. The maximum is 2.
> Msg 319, Level 15, State 1, Line 84
> Incorrect syntax near the keyword 'with'. If this statement is a
> common
> table expression or an xmlnamespaces clause, the previous statement
> must be
> terminated with a semicolon.|||Hi Dan,
I like to use 2 schema names as in the syntax.
Thank you,
Rune
"Dan Sullivan" wrote:

> A table can belong to only a single schema. The pipe in the syntax is a
> choice, not a concatonation. Things like below are valid ways to specify
> a table name
>
> CREATE TABLE database..name
> schema is the default schema for the user
>
> CREATE TABLE database.schema.name
>
> database is the database in use
> CREATE TABLE schema.name
>
> CREATE TABLE name
> database is the database in use and schema is the default schema for the u
ser
>
> Dan
>
>
>|||The syntax does not support two schema names. Look up "Documentation convent
ions"
in the BOL to see how this syntax is defined. If you want to use a "." in
a schema name then the name must be enclosed in [] or ""
CREATE SCHEMA [s1.s2]
CREATE TABLE [s1.s2].table_name
Dan
> Hi Dan,
> I like to use 2 schema names as in the syntax.
> Thank you,
> Rune
> "Dan Sullivan" wrote:
>|||> I like to use 2 schema names as in the syntax.
Yeesh. That is NOT what the syntax describes! | is or, not and! Meaning,
database.schema.table or schema.table. You cannot nest schemas. A schema
cannot own a schema. Period.

Create star schema in SQL Server 2005?

Consider me brand new to data warehousing. I'm looking for a simple,
real-world example taking a simple transactional table, and getting all
"data warehouse" on it.
I know there's a star schema to design, but wouldn't I just do that within a
regular (OLTP) database?
We're looking to get into data warehousing in baby steps. I'm just trying
to be able to put an extremely simple real-world example in front of my boss
to explain what we would do. I.e. not concepts, but 1) start with
tblEmployee in the OLTP database... step 2...
Any help at all would be greatly appreciated.
Thanks,
rscYou might want to read Ralph Kimball's first book; he walks step-by-step
through the process, in detail. See:
http://www.amazon.com/gp/product/04...5964650?ie=UTF8
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ronald S. Cook" <rcook@.westinis.com> wrote in message
news:ual%23jsqrGHA.4252@.TK2MSFTNGP02.phx.gbl...
> Consider me brand new to data warehousing. I'm looking for a simple,
> real-world example taking a simple transactional table, and getting all
> "data warehouse" on it.
> I know there's a star schema to design, but wouldn't I just do that within
> a regular (OLTP) database?
> We're looking to get into data warehousing in baby steps. I'm just trying
> to be able to put an extremely simple real-world example in front of my
> boss to explain what we would do. I.e. not concepts, but 1) start with
> tblEmployee in the OLTP database... step 2...
> Any help at all would be greatly appreciated.
> Thanks,
> rsc
>|||if you are not confortable to transform your data from your OLTP to a DW
schema
and if your OLTP database has a simple schema
and if you don't have to cleanse your data and synchronize with other
sources
then you can try to use view to create a "star schema".
for example, create a view which join your order header and order items
tables (the header contain some important information like the customerid
while the item contain the productid, price etc...)
create views to flatten your tables to create your dimensions (merge the
country, address & customer table; convert null values to unknown or N/A
values to insure a good data integrity)
then create your cubes and dimensions against these views.
another approach is to starts from an empty model and use the AS2005
templates feature.
this will generate the dimensions and cubes you need
and then create the source database and the table required.
after this, you can populate these tables, or replace these table by views,
your job is to found the right column in your source database to fill the
right destination column.
you can also found some samples on the web or in books; I remember books
with sample DW models, but I don't have the name in mind.
if you want to demonstrate this to your boss, create a useable model, not
only a customer analysis but a (at least) customer & product & time
analysis.
good luck.
Jerome.
"Ronald S. Cook" <rcook@.westinis.com> wrote in message
news:ual%23jsqrGHA.4252@.TK2MSFTNGP02.phx.gbl...
> Consider me brand new to data warehousing. I'm looking for a simple,
> real-world example taking a simple transactional table, and getting all
> "data warehouse" on it.
> I know there's a star schema to design, but wouldn't I just do that within
> a regular (OLTP) database?
> We're looking to get into data warehousing in baby steps. I'm just trying
> to be able to put an extremely simple real-world example in front of my
> boss to explain what we would do. I.e. not concepts, but 1) start with
> tblEmployee in the OLTP database... step 2...
> Any help at all would be greatly appreciated.
> Thanks,
> rsc
>|||Hello Ronald,
A good example of Star schemas is the Adventure Works data warehouse
example that comes with SQL Server 2005. The example covers Finance,
Sales (Internet and Reseller), based on fictitious Bicycle Company.
It's a great example to play with.
As for ETL there a few examples included in the samples that come with
SQL Server, but if you are looking for an architecture overview check
out this web cast by Kimball Associates consultant Joy Mundy \Using
SQL Server 2005 Integration Services to Populate a Kimball Method Data
Warehouse (Level 200).
http://msevents.microsoft.com/cui/W...&CountryCode=US
Joy co-wrote The Microsoft Data Warehouse Toolkit, which I can
recommend as a great starting point. The book includes the example of
populating the Adventure Works data warehouse from the Adventure Works
OLTP sample database.
The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the
Microsoft Business Intelligence Tool Set.
http://www.amazon.com/gp/product/04...r.blogspot.com/

Create Schema in Sql Server 2000

Hi everybody, I like to asked anyone who is very good at T-Sql to help me with Sql Server 2000. Thanks in advance. In Sql Server 2000 I have the statement use DBTEST CREATE SCHEMA AUTHORIZATION Ross CREATE VIEW v1(c1) AS SELECT c1 from t1 CREATE TABLE t1(c1 int) GO It work correctly, but Ross don't belong to DBTEST; also use DBTEST CREATE SCHEMA AUTHORIZATION atrandom CREATE VIEW v1(c1) AS SELECT c1 from t1 CREATE TABLE t1(c1 int) GO work with correctness. Why ? The owner can be every words ?

SQL Server 2000 does not have schema concept like SQL Server 2005 so the authorization syntax doesn't really do anything. User-schema separation is available completely in SQL Server 2005 where the authorization clause makes sense.

CREATE SCHEMA in db A from a stored procedure in db B

Hi All

I have a SP that i create tables and other objects on another database.

Creating table work well.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE TABLE ABC (recid int)
exec (@.s)

But if i try to create a schema it gives error :

'CREATE SCHEMA' must be the first statement in a query batch.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE SCHEMA AAA
exec (@.s)

How can i solve it?

Thanks.

declare @.s nvarchar(2000)
set @.s = 'use db01'

exec (@.s)
set @.s = 'CREATE SCHEMA AAA
exec (@.s)

|||

Hi Asvin

If i use "use db01" in exec, when exec completes, it gives up using db01, so if i'm running script in db02, 'CREATE SCHEMA AAA' works on db02.

Best regards.

|||

Standard disclaimer. Generally not a good idea to be creating tables and schemas on the fly.

That out of the way, this method should work:

exec('use tempdb; exec sp_executesql N''create schema test''')

|||

You can do the following in SQL Server 2000/2005 (similar approach can be used in SQL70 also):

declare @.sp nvarchar(500)

set @.sp = quotename(N'db01') + N'sys.sp_executesql'

exec @.sp 'CREATE SCHEMA AAA....'

-- or

exec @.sp @.s

Create schema if not exists - problems

Hi,
I have a problem executing this statement on sqlserver2005:[vbcol=seagreen]
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
create schema TestTest AUTHORIZATION testuser;
go
<<<
I get a "Incorrect syntax near the keyword 'schema'", so what am i missing?!
TIA, Troy
Troy,
CREATE SCHEMA must be the first statement in a batch, but the IF is the
first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
are thinking about that.)
Here is a workaround, which is a simple bit of dynamic SQL:
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
EXEC ('create schema TestTest AUTHORIZATION testuser');
go
RLF
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> Hi,
> I have a problem executing this statement on sqlserver2005:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> create schema TestTest AUTHORIZATION testuser;
> go
> <<<
> I get a "Incorrect syntax near the keyword 'schema'", so what am i
> missing?!
> TIA, Troy
|||Worked like a charm - Thanks
"Russell Fields" wrote:

> Troy,
> CREATE SCHEMA must be the first statement in a batch, but the IF is the
> first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
> are thinking about that.)
> Here is a workaround, which is a simple bit of dynamic SQL:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> EXEC ('create schema TestTest AUTHORIZATION testuser');
> go
> RLF
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
>
>

Create schema if not exists - problems

Hi,
I have a problem executing this statement on sqlserver2005:
>>
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
create schema TestTest AUTHORIZATION testuser;
go
<<<
I get a "Incorrect syntax near the keyword 'schema'", so what am i missing?!
TIA, TroyTroy,
CREATE SCHEMA must be the first statement in a batch, but the IF is the
first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
are thinking about that.)
Here is a workaround, which is a simple bit of dynamic SQL:
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
EXEC ('create schema TestTest AUTHORIZATION testuser');
go
RLF
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> Hi,
> I have a problem executing this statement on sqlserver2005:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> create schema TestTest AUTHORIZATION testuser;
> go
> <<<
> I get a "Incorrect syntax near the keyword 'schema'", so what am i
> missing?!
> TIA, Troy|||Worked like a charm - Thanks :)
"Russell Fields" wrote:
> Troy,
> CREATE SCHEMA must be the first statement in a batch, but the IF is the
> first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
> are thinking about that.)
> Here is a workaround, which is a simple bit of dynamic SQL:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> EXEC ('create schema TestTest AUTHORIZATION testuser');
> go
> RLF
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> > Hi,
> >
> > I have a problem executing this statement on sqlserver2005:
> >>
> > if not exists(select 1 from information_schema.schemata where
> > schema_name='testtest')
> > create schema TestTest AUTHORIZATION testuser;
> > go
> > <<<
> >
> > I get a "Incorrect syntax near the keyword 'schema'", so what am i
> > missing?!
> >
> > TIA, Troy
>
>sql

CREATE SCHEMA fails Inside an If Block

Hello All,

The below "CREATE SCHEMA" sql statement fails if it is inside an IF block. It runs fine if i run it without the IF block...

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')

BEGIN

CREATE SCHEMA Customer AUTHORIZATION [sys]

END

-

Did anyone encountered this issue before....

Thanks..

Make this as dynamic SQL.|||

Thanks, Bushan.

As the DDL scripts can grow bigger, I feel that it is hard to maintain dynamic sql. But, I was just trying to figure out why this is not possible in this "create schema" scenario alone. It even works for "drop schema".

|||

Like the error message you get back says, CREATE SCHEMA must be the first command in the batch. So to do a CREATE SCHEMA in a script like this, that one statement has to be done dynamically.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')
BEGIN
exec ('CREATE SCHEMA Customer AUTHORIZATION [sys]')
END
Annoying? Yes? But it is not that much more work than doing it in the way that you (and I) originally expected :)

|||This is such an OBVIOUS shortcoming of TransactSQL. Why hasn't Microsoft corrected this flaw. As far as I know (with the exception of entering the exec ('sneak the CREATE in as a text string') there is no way to conditionally create a SCHEMA or a FUNCTION for that matter.

If it's disallowed for security reasons then why can we sneak it in with an EXEC?

The problem this causes for me (over and over again) is I write a sample function for the user and include it in my upgrade script. If they've already run the script for an earlier version, they already have my example and may have customized it to their specific application. If they have, I don't really want to replace it with my example again. So I'm stuck sneaking it in through the string route. This has been a flaw in T-SQL for a long time....

Sorry for the rant... Sure wish the T-SQL gods were listening |||
Thanks for the clarification..

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
Ganesh
Hi
If I understand
http://msdn.microsoft.com/library/de.../sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:
> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/d...r />
k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefiniti
on. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements o
f
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh