[database_name . [schema_name ] . | schema_name . ] table_name

Hi,
I try to create 2 schema as the syntax display's as below, but I get a error
of only use 1. 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> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a
> error
> of only use 1.
Can you show the ACTUAL syntax you tried, and the EXACT error message?
The syntax you show demonstrates two options:
CREATE TABLE master.schema1.table
CREATE TABLE schema1.table
It sounds like you maybe misinterpreted it as:
CREATE TABLE master.schema1.schema2.table
?

> 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|||The schema concept is not hierarchical. An object is contained in a schema,
not a level of schemas.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RBC" <RBC@.discussions.microsoft.com> wrote in message
news:DD3B310F-B41C-45E5-AEC9-C5F2DD1A12C4@.microsoft.com...
> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a err
or
> of only use 1. 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

[database_name . [schema_name ] . | schema_name . ] table_na

Hi,
Create Schema.TableName (No Problem)
Create Schema1.schema2.TableName (Problem SQL see schema1 as the database)
Create Database,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.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you show the ACTUAL syntax you tried, and the EXACT error message?
> The syntax you show demonstrates two options:
> CREATE TABLE master.schema1.table
> CREATE TABLE schema1.table
> It sounds like you maybe misinterpreted it as:
> CREATE TABLE master.schema1.schema2.table
> ?
>
>
>> Create Schema1.schema2.TableName (Problem SQL see schema1 as the database)
Yes, because you can't nest schemas.

> Create Database,schema1.schema2.TableName (Error below)
You still can't nest schemas. A schema can't own a schema, there is only
one "level" for it in the heirarchy.|||You can nest schemas according to the syntax. Do you claim the syntax is wro
ng?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, because you can't nest schemas.
>
> You still can't nest schemas. A schema can't own a schema, there is only
> one "level" for it in the heirarchy.
>
>|||> You can nest schemas according to the syntax. Do you claim the syntax is
> wrong?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
No, the syntax is not wrong. Your interpretation of the syntax is wrong, as
several people have already pointed out.
The syntax says you can do:
CREATE TABLE databasename.schemaname.tablename
or
CREATE TABLE schemaname.tablename
or
CREATE TABLE tablename
[[this.that]|or this but not both.]tablename
Go ahead and file a bug that the documentation is wrong as per your
interpretation. But the following fact remains the same: YOU CANNOT NEST
SCHEMAS, EVEN IF YOU INTERPRET THE DOCUMENTATION INCORRECTLY.|||I think you only have to change the parameter from 2 to 3 (Error message),
and we are fine.
I found 1 person by searching the Internet who had the error 3, who tried to
write 3 schemas by including dbo in his syntax...
Thank you,
Rune
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, the syntax is not wrong. Your interpretation of the syntax is wrong,
as
> several people have already pointed out.
> The syntax says you can do:
> CREATE TABLE databasename.schemaname.tablename
> or
> CREATE TABLE schemaname.tablename
> or
> CREATE TABLE tablename
> [[this.that]|or this but not both.]tablename
> Go ahead and file a bug that the documentation is wrong as per your
> interpretation. But the following fact remains the same: YOU CANNOT NEST
> SCHEMAS, EVEN IF YOU INTERPRET THE DOCUMENTATION INCORRECTLY.
>
>|||>I think you only have to change the parameter from 2 to 3 (Error message),
> and we are fine.
> I found 1 person by searching the Internet who had the error 3, who tried
> to
> write 3 schemas by including dbo in his syntax...
Now I have absolutely no idea what you are talking about.
In summary: YOU CANNOT NEST SCHEMAS.

[database_name . [schema_name ] . | schema_name . ] table_na

The schema is hierachical according to the syntax. Do you claim the syntax i
s
wrong?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
"Tibor Karaszi" wrote:

> The schema concept is not hierarchical. An object is contained in a schema
, not a level of schemas.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RBC" <RBC@.discussions.microsoft.com> wrote in message
> news:DD3B310F-B41C-45E5-AEC9-C5F2DD1A12C4@.microsoft.com...
>Hi RBC
No, this is not hierarchical. You are not reading the syntax correctly.
Please see
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/35fbcf7f-8b55-46cd-a957-
9b8c7b311241.htm
in the SQL Server 2005 Books Online which explains how to read the syntax
specifications.
In particular:
| (vertical bar)
Separates syntax items within brackets or braces. You can choose only
one of the items.
[ ] (brackets)
Optional syntax items. Do not type the brackets.
The syntax you show has a vertical bar inside brackets. That means you
choose either what is before the bar or what is after.
The table name is the only element requirement.
Before the table name you can optionally have EITHER a databasename and an
optional schema name OR a schema name.
HTH
Kalen Delaney, SQL Server MVP
"RBC" <RBC@.discussions.microsoft.com> wrote in message
news:848C392A-6DF4-41B5-A1EB-249945C2EEF5@.microsoft.com...
> The schema is hierachical according to the syntax. Do you claim the syntax
> is
> wrong?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
>
> "Tibor Karaszi" wrote:
>|||Hi Kalen,
You are right...
Thank you
It had been perfect for my development with 2 schema...
Rune
"Kalen Delaney" wrote:

> Hi RBC
> No, this is not hierarchical. You are not reading the syntax correctly.
> Please see
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/35fbcf7f-8b55-46cd-a95
7-9b8c7b311241.htm
> in the SQL Server 2005 Books Online which explains how to read the syntax
> specifications.
> In particular:
> | (vertical bar)
> Separates syntax items within brackets or braces. You can choose only
> one of the items.
>
> [ ] (brackets)
> Optional syntax items. Do not type the brackets.
>
> The syntax you show has a vertical bar inside brackets. That means you
> choose either what is before the bar or what is after.
> The table name is the only element requirement.
> Before the table name you can optionally have EITHER a databasename and an
> optional schema name OR a schema name.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "RBC" <RBC@.discussions.microsoft.com> wrote in message
> news:848C392A-6DF4-41B5-A1EB-249945C2EEF5@.microsoft.com...
>
>