|
|
You're in the
sql server programming
group:[ database_name . [ schema_name ] . | schema_name . ] table_name
sql server programming:
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
[quoted text, click to view]
> 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 ? [quoted text, click to view] > 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/ [quoted text, click to view] "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 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,
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. [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" wrote:
> > 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 > >
[quoted text, click to view]
> Create Schema1.schema2.TableName (Problem SQL see schema1 as the database) Yes, because you can't nest schemas. [quoted text, click to view] > 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 wrong?
Syntax CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" wrote:
> > 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. > >
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 [quoted text, click to view] "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, > > > > 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 >
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 [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" wrote:
> > 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. > >
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 [quoted text, click to view] "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: > >> 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 >> > 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 Kalen,
You are right... Thank you It had been perfect for my development with 2 schema... Rune [quoted text, click to view] "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-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: > > > >> 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 > >> > 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 > >> > >> > >
[quoted text, click to view]
> 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.
[quoted text, click to view]
>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.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |