Is it possible to use t-sql to Create a table without columns?
I keep getting syntax errors and can't figure out the correct syntax to do
this.
Thanks,
RSHWhat exactly is a table without columns? By definition, a table has at
least one column. What is your actual goal / business requirement?
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uI0m1mWEGHA.3820@.TK2MSFTNGP12.phx.gbl...
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>|||"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uI0m1mWEGHA.3820@.TK2MSFTNGP12.phx.gbl...
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>
No. A table has to have at least one column. Maybe if you explain what you
want to achieve we could help you with an alternative.
David Portas
SQL Server MVP
--|||No -- why would you want a table with no columns? That would also mean it
could not have any rows. What would you do with such a table?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uI0m1mWEGHA.3820@.TK2MSFTNGP12.phx.gbl...
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>|||a table by definition has columns
what would be the purpose of one without columns?
RSH wrote:
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>
Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts
Thursday, March 29, 2012
Create table with PK on two columns
Hi,
Could you tell me the syntax to set primary key on two columns when I create
a table? I can't find it in the books. The syntax I found is
CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
How can put c2 as part of PRIMARY KEY?
I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
Thanks.Chrissi wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I
> create a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls
> tell me.
> Thanks.
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL
PRIMARY KEY (c1, c2) )
or
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL )
Alter Table MyTable
ADD PRIMARY KEY (c1, c2)
David Gugick
Imceda Software
www.imceda.com|||Create Table MyTable
(c1 INT Not Null,
c2 INT Not Null,
Primary Key (C1, c2))
"§Chrissi§" wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I crea
te
> a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me
.
> Thanks.
>
>|||CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
CONSTRAINT pk_MyTable PRIMARY KEY(c1 ,c2)
)
or
CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY(c1 ,c2)
)
It's technically a constraint in both cases, but you aren't
required to give it a name.
Steve Kass
Drew University
Chrissi wrote:
>Hi,
>Could you tell me the syntax to set primary key on two columns when I creat
e
>a table? I can't find it in the books. The syntax I found is
>CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
>How can put c2 as part of PRIMARY KEY?
>I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
>Thanks.
>
>|||Server: Msg 1911, Level 16, State 1, Line 1
Column name 'C1' does not exist in the target table.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Watch your spelling! Some of us choose a case-sensitive
collation now and then. ;)
SK
CBretana wrote:
>Create Table MyTable
> (c1 INT Not Null,
> c2 INT Not Null,
> Primary Key (C1, c2))
>"§Chrissi§" wrote:
>
>|||Oops! My typing is never good (two finger hint n pec) but I noticed the
upper case C and left it that way anyway... Out of curiousity, why are you
using case-sensitive collation?
"Steve Kass" wrote:
> Server: Msg 1911, Level 16, State 1, Line 1
> Column name 'C1' does not exist in the target table.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> Watch your spelling! Some of us choose a case-sensitive
> collation now and then. ;)
> SK
> CBretana wrote:
>
>|||
CBretana wrote:
> Oops! My typing is never good (two finger hint n pec) but I noticed the
> upper case C and left it that way anyway... Out of curiousity, why are you
> using case-sensitive collation?
Mostly so I can generate the appropriate error messages to include in posts
like this one. ;)
I didn't used to pay attention to this, and it didn't matter as much when
keypunch machines were uppercase-only, or with case-insensitive languages
like Pascal. I had to break sloppy habits when C came along, and though I
slipped into old habits when I started using SQL, I've found more and more
reasons not to be sloppy lately, such as keeping Erland from bugging me
if I put "northwind"."orders" in examples I post. ;)
There are plenty of things you can write that will behave differently
according to collation and language settings, and forcing myself to
be careful about case helps me see and avoid them.
SK
> "Steve Kass" wrote:
>|||>> Out of curiousity, why are you using case-sensitive collation? <<
Because Standard SQL is case-sensitive.
Could you tell me the syntax to set primary key on two columns when I create
a table? I can't find it in the books. The syntax I found is
CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
How can put c2 as part of PRIMARY KEY?
I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
Thanks.Chrissi wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I
> create a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls
> tell me.
> Thanks.
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL
PRIMARY KEY (c1, c2) )
or
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL )
Alter Table MyTable
ADD PRIMARY KEY (c1, c2)
David Gugick
Imceda Software
www.imceda.com|||Create Table MyTable
(c1 INT Not Null,
c2 INT Not Null,
Primary Key (C1, c2))
"§Chrissi§" wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I crea
te
> a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me
.
> Thanks.
>
>|||CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
CONSTRAINT pk_MyTable PRIMARY KEY(c1 ,c2)
)
or
CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY(c1 ,c2)
)
It's technically a constraint in both cases, but you aren't
required to give it a name.
Steve Kass
Drew University
Chrissi wrote:
>Hi,
>Could you tell me the syntax to set primary key on two columns when I creat
e
>a table? I can't find it in the books. The syntax I found is
>CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
>How can put c2 as part of PRIMARY KEY?
>I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
>Thanks.
>
>|||Server: Msg 1911, Level 16, State 1, Line 1
Column name 'C1' does not exist in the target table.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Watch your spelling! Some of us choose a case-sensitive
collation now and then. ;)
SK
CBretana wrote:
>Create Table MyTable
> (c1 INT Not Null,
> c2 INT Not Null,
> Primary Key (C1, c2))
>"§Chrissi§" wrote:
>
>|||Oops! My typing is never good (two finger hint n pec) but I noticed the
upper case C and left it that way anyway... Out of curiousity, why are you
using case-sensitive collation?
"Steve Kass" wrote:
> Server: Msg 1911, Level 16, State 1, Line 1
> Column name 'C1' does not exist in the target table.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> Watch your spelling! Some of us choose a case-sensitive
> collation now and then. ;)
> SK
> CBretana wrote:
>
>|||
CBretana wrote:
> Oops! My typing is never good (two finger hint n pec) but I noticed the
> upper case C and left it that way anyway... Out of curiousity, why are you
> using case-sensitive collation?
Mostly so I can generate the appropriate error messages to include in posts
like this one. ;)
I didn't used to pay attention to this, and it didn't matter as much when
keypunch machines were uppercase-only, or with case-insensitive languages
like Pascal. I had to break sloppy habits when C came along, and though I
slipped into old habits when I started using SQL, I've found more and more
reasons not to be sloppy lately, such as keeping Erland from bugging me
if I put "northwind"."orders" in examples I post. ;)
There are plenty of things you can write that will behave differently
according to collation and language settings, and forcing myself to
be careful about case helps me see and avoid them.
SK
> "Steve Kass" wrote:
>|||>> Out of curiousity, why are you using case-sensitive collation? <<
Because Standard SQL is case-sensitive.
CREATE TABLE with multiple-column primary key?
Is it possible to issue the CREATE TABLE command and specify a multiple-colu
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:
> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:
> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...
CREATE TABLE with a DEFAULT for Microsoft Access
I'm missing SQL Server already. :(
This doesn't work with a Microsoft Access database. The DEFAULT is causing a syntax exception. Trying to find any help with Google has prooved very frustrating and given me no leads, so do any of you know how it is done in a CREATE TABLE statement? (i.e. not seperately).
CREATE TABLE [MyTable] (
[MyField] VARCHAR(50) DEFAULT ""
)
Thanks for reading,
- David
(btw, I posted this in the general SQL forum as there didn't seem to be one for non-application-type MS Access questions. Hope that was right.)When you use the Table Design within Access there is Default Value property for a column.
By the way there is a Microsoft Access section with dbforums.|||I know about that, I want to set it using an SQL statement though. I am creating the database tables through script not using Access itself.
I mentioned why I didn't use the Microsoft Access forum in my edit. I looked at the messages that were on the first few pages and they seemed to all be application-orientated.
Thanks for your reply,
- David|||Ok, but it is just a suggestion to maybe have your question duplicated in the MS Access (you never know who might be popping in there to view stuff).
Also, have you looked at the Access documentation there is a section about Jet SQL Reference (not sure if that is what you need to reference)...although it looks as though there isn't a mention of DEFAULT. I agree with you when you 'downgrade' from a DB engine that has everything to something that lacks, it is frustrating.
Good luck....|||CREATE TABLE [MyTable] (
MyField Text(50) DEFAULT Hello World,
MyID Integer NOT NULL DEFAULT 1
)
Sorry for pulling a Hello world stuff on ya but that should work.|||I tried running that SQL in MS Access itself and got the same error I have been seeing with other attempts:
---------------
Microsoft Access
---------------
Syntax error in CREATE TABLE statement.
---------------
OK Help
---------------
It then selects the CREATE keyword in the SQL window.
I am using Access 2002/XP for this, should I be using something else?|||Just an observation, but MS-Access is a client side program. By default, it ships with the Microsoft-Jet database engine. If you have MS-Access 2002, you have MSDE on the CD, which is a slightly scaled down version of MS-SQL.
It might be worthwhile for you to install MSDE and use that as your database engine. It would put you on much more familiar ground!
-PatP|||Thanks Pat, but it's not for that sort of use. The product gets installed on web servers that don't have SQL Server or MSDE available to them. (if it does, it would use them anyway). :(
This doesn't work with a Microsoft Access database. The DEFAULT is causing a syntax exception. Trying to find any help with Google has prooved very frustrating and given me no leads, so do any of you know how it is done in a CREATE TABLE statement? (i.e. not seperately).
CREATE TABLE [MyTable] (
[MyField] VARCHAR(50) DEFAULT ""
)
Thanks for reading,
- David
(btw, I posted this in the general SQL forum as there didn't seem to be one for non-application-type MS Access questions. Hope that was right.)When you use the Table Design within Access there is Default Value property for a column.
By the way there is a Microsoft Access section with dbforums.|||I know about that, I want to set it using an SQL statement though. I am creating the database tables through script not using Access itself.
I mentioned why I didn't use the Microsoft Access forum in my edit. I looked at the messages that were on the first few pages and they seemed to all be application-orientated.
Thanks for your reply,
- David|||Ok, but it is just a suggestion to maybe have your question duplicated in the MS Access (you never know who might be popping in there to view stuff).
Also, have you looked at the Access documentation there is a section about Jet SQL Reference (not sure if that is what you need to reference)...although it looks as though there isn't a mention of DEFAULT. I agree with you when you 'downgrade' from a DB engine that has everything to something that lacks, it is frustrating.
Good luck....|||CREATE TABLE [MyTable] (
MyField Text(50) DEFAULT Hello World,
MyID Integer NOT NULL DEFAULT 1
)
Sorry for pulling a Hello world stuff on ya but that should work.|||I tried running that SQL in MS Access itself and got the same error I have been seeing with other attempts:
---------------
Microsoft Access
---------------
Syntax error in CREATE TABLE statement.
---------------
OK Help
---------------
It then selects the CREATE keyword in the SQL window.
I am using Access 2002/XP for this, should I be using something else?|||Just an observation, but MS-Access is a client side program. By default, it ships with the Microsoft-Jet database engine. If you have MS-Access 2002, you have MSDE on the CD, which is a slightly scaled down version of MS-SQL.
It might be worthwhile for you to install MSDE and use that as your database engine. It would put you on much more familiar ground!
-PatP|||Thanks Pat, but it's not for that sort of use. The product gets installed on web servers that don't have SQL Server or MSDE available to them. (if it does, it would use them anyway). :(
create table test
hi,
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
Krish
SELECT * INTO NewTable FROM OldTable
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish
|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
>
|||That's right. However, IDENTITY property will be copied to new table.
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
>
|||Hi,
Yes, That is correct.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
Indexes
> ,
>
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
Krish
SELECT * INTO NewTable FROM OldTable
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish
|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
>
|||That's right. However, IDENTITY property will be copied to new table.
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
>
|||Hi,
Yes, That is correct.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
Indexes
> ,
>
create table test
hi,
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
KrishSELECT * INTO NewTable FROM OldTable
--
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > hi,
> >
> > What is the syntax for creating a new table as that of
> > existing one with data..
> >
> > create table test1 as select * from test is not working.
> >
> >
> > Regards
> > Krish
>|||That's right. However, IDENTITY property will be copied to new table.
--
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > SELECT * INTO NewTable FROM OldTable
> >
> > --
> > Rohtash Kapoor
> > http://www.sqlmantra.com
> >
> >
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > hi,
> > >
> > > What is the syntax for creating a new table as that of
> > > existing one with data..
> > >
> > > create table test1 as select * from test is not working.
> > >
> > >
> > > Regards
> > > Krish
> >
> >
>|||Hi,
Yes, That is correct.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > To add on, this command just copies the table structure and data.
Indexes
> ,
> > Constraints and Identity property
> > needs to be created manually.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> > news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > > SELECT * INTO NewTable FROM OldTable
> > >
> > > --
> > > Rohtash Kapoor
> > > http://www.sqlmantra.com
> > >
> > >
> > >
> > > <anonymous@.discussions.microsoft.com> wrote in message
> > > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > > hi,
> > > >
> > > > What is the syntax for creating a new table as that of
> > > > existing one with data..
> > > >
> > > > create table test1 as select * from test is not working.
> > > >
> > > >
> > > > Regards
> > > > Krish
> > >
> > >
> >
> >
>
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
KrishSELECT * INTO NewTable FROM OldTable
--
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > hi,
> >
> > What is the syntax for creating a new table as that of
> > existing one with data..
> >
> > create table test1 as select * from test is not working.
> >
> >
> > Regards
> > Krish
>|||That's right. However, IDENTITY property will be copied to new table.
--
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > SELECT * INTO NewTable FROM OldTable
> >
> > --
> > Rohtash Kapoor
> > http://www.sqlmantra.com
> >
> >
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > hi,
> > >
> > > What is the syntax for creating a new table as that of
> > > existing one with data..
> > >
> > > create table test1 as select * from test is not working.
> > >
> > >
> > > Regards
> > > Krish
> >
> >
>|||Hi,
Yes, That is correct.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > To add on, this command just copies the table structure and data.
Indexes
> ,
> > Constraints and Identity property
> > needs to be created manually.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> > news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > > SELECT * INTO NewTable FROM OldTable
> > >
> > > --
> > > Rohtash Kapoor
> > > http://www.sqlmantra.com
> > >
> > >
> > >
> > > <anonymous@.discussions.microsoft.com> wrote in message
> > > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > > hi,
> > > >
> > > > What is the syntax for creating a new table as that of
> > > > existing one with data..
> > > >
> > > > create table test1 as select * from test is not working.
> > > >
> > > >
> > > > Regards
> > > > Krish
> > >
> > >
> >
> >
>
create table test
hi,
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
KrishSELECT * INTO NewTable FROM OldTable
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx
.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx
.gbl...
>|||That's right. However, IDENTITY property will be copied to new table.
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
Yes, That is correct.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
Indexes[vbcol=seagreen]
> ,
>
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
KrishSELECT * INTO NewTable FROM OldTable
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx
.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx
.gbl...
>|||That's right. However, IDENTITY property will be copied to new table.
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
Yes, That is correct.
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
Indexes[vbcol=seagreen]
> ,
>
Create Table syntax SQL in SQL Server 7
Hi guys,I need to pass some SQL to someone else who will run it on their database. I have got the SQL for SQL Server 2000 but they are running SQL Server 7. Apparently the below MSSQL 2000 script doesn't work;SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tableName]( [id] [int] IDENTITY(1,1) NOT NULL, [ArticleID] [int] NULL, [Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WrittenDate] [datetime] NULL,CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC ))What is the equivalent of the above in for SQL Server 7? I don't have access to it via SQL Server Manager so have to run the script.
Looks like the forum didn't like Safari. I'll try that againwith Firefox and see if that comes out a bit better. SO to summarise,how do I do the following in SQL Server 7, as this is for SQL Server2000 and I don't know what the syntax difference is:
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tableName]
( [id] [int] IDENTITY(1,1) NOT NULL,
[ArticleID] [int] NULL,
[Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WrittenDate] [datetime] NULL,
CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC ))
I've written this in what should be OK in SQL Server 7. Idon't have anyway of testing this, and would prefer that it is correctbefore sending it. So, is the below equivalent to the SQL Server 2000code above:
CREATE TABLE tableName
(
id int IDENTITY NOT NULL,
ArticleID text,
Heading text,
BodyContent text,
WrittenDate datetime
)
This doesn't seem to specify any primary key, how do I do that if indeed I need to?
Slowly realising how much I rely on SQL Server Managment studio...!
Create Table Syntax
Hi Guys
Really need your help I donno what I am doing wrong in here
Want to create a table with another existing table
Here is the syntax I am using
Really need your help I donno what I am doing wrong in here
Want to create a table with another existing table
Here is the syntax I am using
create table pctemp1
As
(SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date]
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null )
and it says Syntax error with AS clause, tried removing AS clause but no go , can anybody help me please ...
thanksselect * into NEW TABLE NAME from OLD TABLE NAME--
THIS IS THE SYTAX..YOU CAN USE THIS FOR UR NEED|||SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date] INTO NEW_TABLE_NAME
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null
TRY THIS...
Tuesday, March 27, 2012
Create Table Script Incorrect syntax near 'Collate'
HI.
I am using SQL Server 2000. I generate a script of some table from EmpDB database when I run script in query analyzer it return error "Incorrect syntax near 'COLLATE'."
Scripts is
************************************************** ****
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Emp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Emp]
GO
CREATE TABLE [dbo].[Emp] (
[EmpID] [int] NOT NULL ,
[EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
************************************************** ****
When I remove " COLLATE SQL_Latin1_General_CP1_CI_AS" from script manually then it run successfully.
Please guide why it is happening and how to escape from error.
Thanx in advance.I have tried your code and it works on my PC.
Try creating the table without the Collation and then use entreprice manager to assign the spicfic collation to the field. Does the specific calation exist?sql
create table script
I am creating table in sql server 2005, by using the below script,but it
showing "syntax error near (" The below script is created from generate
script option of sql) ,I ll apprecite the solution asap,Thanx guys
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL,
[EffectiveDate] [datetime] NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I am not using any kind of tool, i am executing it from query analyzer of sql
server 2005 standard ediion
"Tibor Karaszi" wrote:
> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
> some tool you get that error message? If so, can you specify what tool you use to execute the script
> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
> it, or that you do mark only the text you want to be executed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>
>
|||Another posibility is that the server/instance you are running this against
is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
2005. Try running
Select ServerProperty('ProductVersion')
If it returns a value where the first digit is 8, like
8.00.2039
then it is SQL 2000 and that is your problem.
But if it returns a value where the first digit is 8, like
9.00.3042.00
then it is SQL2005 and my guess is incorrect.
But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
and should be removed.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> Then that tool would be query analyzer (or perhaps you meant SQL Server
> Management Studio).
> Anyhow, I executed the code you posted and it worked just fine. I'm also
> on SQL Server 2005. My guess is that you by mistake maked a part of the
> text so that only that text were submitted to sQL Server.-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>
|||Thanks, u r correct,but is there any other way, means if i can change any
configuration in sql server 2005 so that it works, or any other way or should
i uninstall sql 2000
Thank u
"Tom Cooper" wrote:
> Another posibility is that the server/instance you are running this against
> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
> 2005. Try running
> Select ServerProperty('ProductVersion')
> If it returns a value where the first digit is 8, like
> 8.00.2039
> then it is SQL 2000 and that is your problem.
> But if it returns a value where the first digit is 8, like
> 9.00.3042.00
> then it is SQL2005 and my guess is incorrect.
> But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
> and should be removed.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
>
>
|||If you use the SSMS scripts wizard to generate the script for the table, you
can tell it to generate a script that is compatable with SQL 2000. On the
Choose Scripts Options page there is an option named Script for Server
Version. Set that to SQL 2000 and it will generate a script that will run
on SQL 2000. Of course, if you are using features that are new for SQL
2005, those features won't be included.
Tom
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:8642EF7D-6437-4C7E-8B3F-29A22B330F7A@.microsoft.com...[vbcol=seagreen]
> Thanks, u r correct,but is there any other way, means if i can change any
> configuration in sql server 2005 so that it works, or any other way or
> should
> i uninstall sql 2000
> Thank u
> "Tom Cooper" wrote:
showing "syntax error near (" The below script is created from generate
script option of sql) ,I ll apprecite the solution asap,Thanx guys
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL,
[EffectiveDate] [datetime] NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I am not using any kind of tool, i am executing it from query analyzer of sql
server 2005 standard ediion
"Tibor Karaszi" wrote:
> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
> some tool you get that error message? If so, can you specify what tool you use to execute the script
> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
> it, or that you do mark only the text you want to be executed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>
>
|||Another posibility is that the server/instance you are running this against
is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
2005. Try running
Select ServerProperty('ProductVersion')
If it returns a value where the first digit is 8, like
8.00.2039
then it is SQL 2000 and that is your problem.
But if it returns a value where the first digit is 8, like
9.00.3042.00
then it is SQL2005 and my guess is incorrect.
But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
and should be removed.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> Then that tool would be query analyzer (or perhaps you meant SQL Server
> Management Studio).
> Anyhow, I executed the code you posted and it worked just fine. I'm also
> on SQL Server 2005. My guess is that you by mistake maked a part of the
> text so that only that text were submitted to sQL Server.-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>
|||Thanks, u r correct,but is there any other way, means if i can change any
configuration in sql server 2005 so that it works, or any other way or should
i uninstall sql 2000
Thank u
"Tom Cooper" wrote:
> Another posibility is that the server/instance you are running this against
> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
> 2005. Try running
> Select ServerProperty('ProductVersion')
> If it returns a value where the first digit is 8, like
> 8.00.2039
> then it is SQL 2000 and that is your problem.
> But if it returns a value where the first digit is 8, like
> 9.00.3042.00
> then it is SQL2005 and my guess is incorrect.
> But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
> and should be removed.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
>
>
|||If you use the SSMS scripts wizard to generate the script for the table, you
can tell it to generate a script that is compatable with SQL 2000. On the
Choose Scripts Options page there is an option named Script for Server
Version. Set that to SQL 2000 and it will generate a script that will run
on SQL 2000. Of course, if you are using features that are new for SQL
2005, those features won't be included.
Tom
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:8642EF7D-6437-4C7E-8B3F-29A22B330F7A@.microsoft.com...[vbcol=seagreen]
> Thanks, u r correct,but is there any other way, means if i can change any
> configuration in sql server 2005 so that it works, or any other way or
> should
> i uninstall sql 2000
> Thank u
> "Tom Cooper" wrote:
create table script
I am creating table in sql server 2005, by using the below script,but it
showing "syntax error near (" The below script is created from generate
script option of sql) ,I ll apprecite the solution asap,Thanx guys
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL,
[EffectiveDate] [datetime] NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
some tool you get that error message? If so, can you specify what tool you use to execute the script
and against what version of SQL Server. Also, make sure you do not mark any text when you execute
it, or that you do mark only the text you want to be executed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>I am creating table in sql server 2005, by using the below script,but it
> showing "syntax error near (" The below script is created from generate
> script option of sql) ,I ll apprecite the solution asap,Thanx guys
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[UserRoles](
> [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> [UserID] [int] NOT NULL,
> [RoleID] [int] NOT NULL,
> [ExpiryDate] [datetime] NULL,
> [IsTrialUsed] [bit] NULL,
> [EffectiveDate] [datetime] NULL,
> CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> (
> [UserRoleID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]|||I am not using any kind of tool, i am executing it from query analyzer of sql
server 2005 standard ediion
"Tibor Karaszi" wrote:
> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
> some tool you get that error message? If so, can you specify what tool you use to execute the script
> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
> it, or that you do mark only the text you want to be executed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >I am creating table in sql server 2005, by using the below script,but it
> > showing "syntax error near (" The below script is created from generate
> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > CREATE TABLE [dbo].[UserRoles](
> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> > [UserID] [int] NOT NULL,
> > [RoleID] [int] NOT NULL,
> > [ExpiryDate] [datetime] NULL,
> > [IsTrialUsed] [bit] NULL,
> > [EffectiveDate] [datetime] NULL,
> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> > (
> > [UserRoleID] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> > ) ON [PRIMARY]
>
>|||>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
Then that tool would be query analyzer (or perhaps you meant SQL Server Management Studio).
Anyhow, I executed the code you posted and it worked just fine. I'm also on SQL Server 2005. My
guess is that you by mistake maked a part of the text so that only that text were submitted to sQL
Server.-
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script
>> from
>> some tool you get that error message? If so, can you specify what tool you use to execute the
>> script
>> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but it
>> > showing "syntax error near (" The below script is created from generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>|||Another posibility is that the server/instance you are running this against
is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
2005. Try running
Select ServerProperty('ProductVersion')
If it returns a value where the first digit is 8, like
8.00.2039
then it is SQL 2000 and that is your problem.
But if it returns a value where the first digit is 8, like
9.00.3042.00
then it is SQL2005 and my guess is incorrect.
But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
and should be removed.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> >I am not using any kind of tool, i am executing it from query analyzer of
> >sql
>> server 2005 standard ediion
> Then that tool would be query analyzer (or perhaps you meant SQL Server
> Management Studio).
> Anyhow, I executed the code you posted and it worked just fine. I'm also
> on SQL Server 2005. My guess is that you by mistake maked a part of the
> text so that only that text were submitted to sQL Server.-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>>I am not using any kind of tool, i am executing it from query analyzer of
>>sql
>> server 2005 standard ediion
>> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when
>> you execute the script from
>> some tool you get that error message? If so, can you specify what tool
>> you use to execute the script
>> and against what version of SQL Server. Also, make sure you do not mark
>> any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but
>> >it
>> > showing "syntax error near (" The below script is created from
>> > generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
>> > =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>
>|||Thanks, u r correct,but is there any other way, means if i can change any
configuration in sql server 2005 so that it works, or any other way or should
i uninstall sql 2000
Thank u
"Tom Cooper" wrote:
> Another posibility is that the server/instance you are running this against
> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
> 2005. Try running
> Select ServerProperty('ProductVersion')
> If it returns a value where the first digit is 8, like
> 8.00.2039
> then it is SQL 2000 and that is your problem.
> But if it returns a value where the first digit is 8, like
> 9.00.3042.00
> then it is SQL2005 and my guess is incorrect.
> But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
> and should be removed.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> > >I am not using any kind of tool, i am executing it from query analyzer of
> > >sql
> >> server 2005 standard ediion
> >
> > Then that tool would be query analyzer (or perhaps you meant SQL Server
> > Management Studio).
> >
> > Anyhow, I executed the code you posted and it worked just fine. I'm also
> > on SQL Server 2005. My guess is that you by mistake maked a part of the
> > text so that only that text were submitted to sQL Server.-
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
> >>I am not using any kind of tool, i am executing it from query analyzer of
> >>sql
> >> server 2005 standard ediion
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> I assume that you by "it showing "syntax error near ("" Mean that when
> >> you execute the script from
> >> some tool you get that error message? If so, can you specify what tool
> >> you use to execute the script
> >> and against what version of SQL Server. Also, make sure you do not mark
> >> any text when you execute
> >> it, or that you do mark only the text you want to be executed.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
> >> message
> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >> >I am creating table in sql server 2005, by using the below script,but
> >> >it
> >> > showing "syntax error near (" The below script is created from
> >> > generate
> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >> >
> >> > SET ANSI_NULLS ON
> >> > GO
> >> > SET QUOTED_IDENTIFIER ON
> >> > GO
> >> > CREATE TABLE [dbo].[UserRoles](
> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> >> > [UserID] [int] NOT NULL,
> >> > [RoleID] [int] NOT NULL,
> >> > [ExpiryDate] [datetime] NULL,
> >> > [IsTrialUsed] [bit] NULL,
> >> > [EffectiveDate] [datetime] NULL,
> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> >> > (
> >> > [UserRoleID] ASC
> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> >> > => >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >> > ) ON [PRIMARY]
> >>
> >>
> >>
> >
> >
>
>|||If you use the SSMS scripts wizard to generate the script for the table, you
can tell it to generate a script that is compatable with SQL 2000. On the
Choose Scripts Options page there is an option named Script for Server
Version. Set that to SQL 2000 and it will generate a script that will run
on SQL 2000. Of course, if you are using features that are new for SQL
2005, those features won't be included.
Tom
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:8642EF7D-6437-4C7E-8B3F-29A22B330F7A@.microsoft.com...
> Thanks, u r correct,but is there any other way, means if i can change any
> configuration in sql server 2005 so that it works, or any other way or
> should
> i uninstall sql 2000
> Thank u
> "Tom Cooper" wrote:
>> Another posibility is that the server/instance you are running this
>> against
>> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
>> 2005. Try running
>> Select ServerProperty('ProductVersion')
>> If it returns a value where the first digit is 8, like
>> 8.00.2039
>> then it is SQL 2000 and that is your problem.
>> But if it returns a value where the first digit is 8, like
>> 9.00.3042.00
>> then it is SQL2005 and my guess is incorrect.
>> But if it is SQL 2000, then the entire clause WITH (...) is not valid
>> syntax
>> and should be removed.
>> Tom
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
>> > >I am not using any kind of tool, i am executing it from query analyzer
>> > >of
>> > >sql
>> >> server 2005 standard ediion
>> >
>> > Then that tool would be query analyzer (or perhaps you meant SQL Server
>> > Management Studio).
>> >
>> > Anyhow, I executed the code you posted and it worked just fine. I'm
>> > also
>> > on SQL Server 2005. My guess is that you by mistake maked a part of the
>> > text so that only that text were submitted to sQL Server.-
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> > message
>> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>> >>I am not using any kind of tool, i am executing it from query analyzer
>> >>of
>> >>sql
>> >> server 2005 standard ediion
>> >>
>> >> "Tibor Karaszi" wrote:
>> >>
>> >> I assume that you by "it showing "syntax error near ("" Mean that
>> >> when
>> >> you execute the script from
>> >> some tool you get that error message? If so, can you specify what
>> >> tool
>> >> you use to execute the script
>> >> and against what version of SQL Server. Also, make sure you do not
>> >> mark
>> >> any text when you execute
>> >> it, or that you do mark only the text you want to be executed.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >> >I am creating table in sql server 2005, by using the below
>> >> >script,but
>> >> >it
>> >> > showing "syntax error near (" The below script is created from
>> >> > generate
>> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >> >
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> > SET QUOTED_IDENTIFIER ON
>> >> > GO
>> >> > CREATE TABLE [dbo].[UserRoles](
>> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> >> > [UserID] [int] NOT NULL,
>> >> > [RoleID] [int] NOT NULL,
>> >> > [ExpiryDate] [datetime] NULL,
>> >> > [IsTrialUsed] [bit] NULL,
>> >> > [EffectiveDate] [datetime] NULL,
>> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [UserRoleID] ASC
>> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
>> >> > IGNORE_DUP_KEY
>> >> > =>> >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> >> > ) ON [PRIMARY]
>> >>
>> >>
>> >>
>> >
>> >
>>
showing "syntax error near (" The below script is created from generate
script option of sql) ,I ll apprecite the solution asap,Thanx guys
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL,
[EffectiveDate] [datetime] NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
some tool you get that error message? If so, can you specify what tool you use to execute the script
and against what version of SQL Server. Also, make sure you do not mark any text when you execute
it, or that you do mark only the text you want to be executed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>I am creating table in sql server 2005, by using the below script,but it
> showing "syntax error near (" The below script is created from generate
> script option of sql) ,I ll apprecite the solution asap,Thanx guys
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[UserRoles](
> [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> [UserID] [int] NOT NULL,
> [RoleID] [int] NOT NULL,
> [ExpiryDate] [datetime] NULL,
> [IsTrialUsed] [bit] NULL,
> [EffectiveDate] [datetime] NULL,
> CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> (
> [UserRoleID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]|||I am not using any kind of tool, i am executing it from query analyzer of sql
server 2005 standard ediion
"Tibor Karaszi" wrote:
> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
> some tool you get that error message? If so, can you specify what tool you use to execute the script
> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
> it, or that you do mark only the text you want to be executed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >I am creating table in sql server 2005, by using the below script,but it
> > showing "syntax error near (" The below script is created from generate
> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > CREATE TABLE [dbo].[UserRoles](
> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> > [UserID] [int] NOT NULL,
> > [RoleID] [int] NOT NULL,
> > [ExpiryDate] [datetime] NULL,
> > [IsTrialUsed] [bit] NULL,
> > [EffectiveDate] [datetime] NULL,
> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> > (
> > [UserRoleID] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> > ) ON [PRIMARY]
>
>|||>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
Then that tool would be query analyzer (or perhaps you meant SQL Server Management Studio).
Anyhow, I executed the code you posted and it worked just fine. I'm also on SQL Server 2005. My
guess is that you by mistake maked a part of the text so that only that text were submitted to sQL
Server.-
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script
>> from
>> some tool you get that error message? If so, can you specify what tool you use to execute the
>> script
>> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but it
>> > showing "syntax error near (" The below script is created from generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>|||Another posibility is that the server/instance you are running this against
is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
2005. Try running
Select ServerProperty('ProductVersion')
If it returns a value where the first digit is 8, like
8.00.2039
then it is SQL 2000 and that is your problem.
But if it returns a value where the first digit is 8, like
9.00.3042.00
then it is SQL2005 and my guess is incorrect.
But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
and should be removed.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> >I am not using any kind of tool, i am executing it from query analyzer of
> >sql
>> server 2005 standard ediion
> Then that tool would be query analyzer (or perhaps you meant SQL Server
> Management Studio).
> Anyhow, I executed the code you posted and it worked just fine. I'm also
> on SQL Server 2005. My guess is that you by mistake maked a part of the
> text so that only that text were submitted to sQL Server.-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>>I am not using any kind of tool, i am executing it from query analyzer of
>>sql
>> server 2005 standard ediion
>> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when
>> you execute the script from
>> some tool you get that error message? If so, can you specify what tool
>> you use to execute the script
>> and against what version of SQL Server. Also, make sure you do not mark
>> any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but
>> >it
>> > showing "syntax error near (" The below script is created from
>> > generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
>> > =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>
>|||Thanks, u r correct,but is there any other way, means if i can change any
configuration in sql server 2005 so that it works, or any other way or should
i uninstall sql 2000
Thank u
"Tom Cooper" wrote:
> Another posibility is that the server/instance you are running this against
> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
> 2005. Try running
> Select ServerProperty('ProductVersion')
> If it returns a value where the first digit is 8, like
> 8.00.2039
> then it is SQL 2000 and that is your problem.
> But if it returns a value where the first digit is 8, like
> 9.00.3042.00
> then it is SQL2005 and my guess is incorrect.
> But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
> and should be removed.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> > >I am not using any kind of tool, i am executing it from query analyzer of
> > >sql
> >> server 2005 standard ediion
> >
> > Then that tool would be query analyzer (or perhaps you meant SQL Server
> > Management Studio).
> >
> > Anyhow, I executed the code you posted and it worked just fine. I'm also
> > on SQL Server 2005. My guess is that you by mistake maked a part of the
> > text so that only that text were submitted to sQL Server.-
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
> >>I am not using any kind of tool, i am executing it from query analyzer of
> >>sql
> >> server 2005 standard ediion
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> I assume that you by "it showing "syntax error near ("" Mean that when
> >> you execute the script from
> >> some tool you get that error message? If so, can you specify what tool
> >> you use to execute the script
> >> and against what version of SQL Server. Also, make sure you do not mark
> >> any text when you execute
> >> it, or that you do mark only the text you want to be executed.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
> >> message
> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >> >I am creating table in sql server 2005, by using the below script,but
> >> >it
> >> > showing "syntax error near (" The below script is created from
> >> > generate
> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >> >
> >> > SET ANSI_NULLS ON
> >> > GO
> >> > SET QUOTED_IDENTIFIER ON
> >> > GO
> >> > CREATE TABLE [dbo].[UserRoles](
> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> >> > [UserID] [int] NOT NULL,
> >> > [RoleID] [int] NOT NULL,
> >> > [ExpiryDate] [datetime] NULL,
> >> > [IsTrialUsed] [bit] NULL,
> >> > [EffectiveDate] [datetime] NULL,
> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> >> > (
> >> > [UserRoleID] ASC
> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> >> > => >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >> > ) ON [PRIMARY]
> >>
> >>
> >>
> >
> >
>
>|||If you use the SSMS scripts wizard to generate the script for the table, you
can tell it to generate a script that is compatable with SQL 2000. On the
Choose Scripts Options page there is an option named Script for Server
Version. Set that to SQL 2000 and it will generate a script that will run
on SQL 2000. Of course, if you are using features that are new for SQL
2005, those features won't be included.
Tom
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:8642EF7D-6437-4C7E-8B3F-29A22B330F7A@.microsoft.com...
> Thanks, u r correct,but is there any other way, means if i can change any
> configuration in sql server 2005 so that it works, or any other way or
> should
> i uninstall sql 2000
> Thank u
> "Tom Cooper" wrote:
>> Another posibility is that the server/instance you are running this
>> against
>> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
>> 2005. Try running
>> Select ServerProperty('ProductVersion')
>> If it returns a value where the first digit is 8, like
>> 8.00.2039
>> then it is SQL 2000 and that is your problem.
>> But if it returns a value where the first digit is 8, like
>> 9.00.3042.00
>> then it is SQL2005 and my guess is incorrect.
>> But if it is SQL 2000, then the entire clause WITH (...) is not valid
>> syntax
>> and should be removed.
>> Tom
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
>> > >I am not using any kind of tool, i am executing it from query analyzer
>> > >of
>> > >sql
>> >> server 2005 standard ediion
>> >
>> > Then that tool would be query analyzer (or perhaps you meant SQL Server
>> > Management Studio).
>> >
>> > Anyhow, I executed the code you posted and it worked just fine. I'm
>> > also
>> > on SQL Server 2005. My guess is that you by mistake maked a part of the
>> > text so that only that text were submitted to sQL Server.-
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> > message
>> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>> >>I am not using any kind of tool, i am executing it from query analyzer
>> >>of
>> >>sql
>> >> server 2005 standard ediion
>> >>
>> >> "Tibor Karaszi" wrote:
>> >>
>> >> I assume that you by "it showing "syntax error near ("" Mean that
>> >> when
>> >> you execute the script from
>> >> some tool you get that error message? If so, can you specify what
>> >> tool
>> >> you use to execute the script
>> >> and against what version of SQL Server. Also, make sure you do not
>> >> mark
>> >> any text when you execute
>> >> it, or that you do mark only the text you want to be executed.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >> >I am creating table in sql server 2005, by using the below
>> >> >script,but
>> >> >it
>> >> > showing "syntax error near (" The below script is created from
>> >> > generate
>> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >> >
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> > SET QUOTED_IDENTIFIER ON
>> >> > GO
>> >> > CREATE TABLE [dbo].[UserRoles](
>> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> >> > [UserID] [int] NOT NULL,
>> >> > [RoleID] [int] NOT NULL,
>> >> > [ExpiryDate] [datetime] NULL,
>> >> > [IsTrialUsed] [bit] NULL,
>> >> > [EffectiveDate] [datetime] NULL,
>> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [UserRoleID] ASC
>> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
>> >> > IGNORE_DUP_KEY
>> >> > =>> >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> >> > ) ON [PRIMARY]
>> >>
>> >>
>> >>
>> >
>> >
>>
create table if not exists syntax error
CREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);
For some reason the above statements are giving me syntax errors?
I want to create table only if it does not already exist.
Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?
thx in advance .
IF NOT EXISTS
( SELECT [name]
FROM sys.tables
WHERE [name] = MyTable
)
CREATE TABLE MyTable (Col1 int IDENITY, etc. )
Use 'IF EXISTS' test to TRUNCATE or DROP.
create table from output of select sql
I want to create a table with the output of select statement. i.e. create
table tmpk as select *...
this gives an error
Incorrect syntax near the keyword 'as'.
when i am running query alone this gives the result fine.try
SELECT
field1, field 2 etc...
INTO tmpk
FROM
wherever
"Karn Tanwar" <karntanwer@.hotmail.com> wrote in message
news:OtJceVhVFHA.544@.TK2MSFTNGP15.phx.gbl...
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>|||On Wed, 11 May 2005 16:21:36 +0530, Karn Tanwar wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
You have to use EXEC, like this:
DECLARE @.var VARCHAR(8000)
SELECT @.var = 'CREATE TABLE ' + something
FROM somewhere
WHERE somecol = @.somevar
EXEC (@.var)
This is a crude example, but you get the point.
/Andrs Taylor|||Hi Karan
Try this way
SELECT * INTO tmpk FROM ...
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Unfortunately in the SQL Server enviornment you cannot select into a table
like you can in other legacy enviornments like Foxpro but you can declare a
local temp table with the same field makeup as the select statement outputs
and then use and insert to get the data into a table. You might have to mes
s
with it for a little while before you get it right, but it is possible to ge
t
somewhat the same results.
Declare @.v_sql NVARCHAR(4000)
SELECT @.v_sql =
INSERT #test
select hard_id,
Type_id,
description,
speed,
amount,
cache,
brand
from TU_Hardware
EXEC sp_executesql @.v_sql
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Correction.. you can do a select into statement but you cant do it the way
you were trying it.
If you go to masters table on any db and type the following:
select * from abc
It will give you an error because no table exist then type
select top 10 *
into abc
from sysobjects
You will have a new table in master db. You can do this with any table in
the from part of the statement.
Hope this helps..
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>
table tmpk as select *...
this gives an error
Incorrect syntax near the keyword 'as'.
when i am running query alone this gives the result fine.try
SELECT
field1, field 2 etc...
INTO tmpk
FROM
wherever
"Karn Tanwar" <karntanwer@.hotmail.com> wrote in message
news:OtJceVhVFHA.544@.TK2MSFTNGP15.phx.gbl...
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>|||On Wed, 11 May 2005 16:21:36 +0530, Karn Tanwar wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
You have to use EXEC, like this:
DECLARE @.var VARCHAR(8000)
SELECT @.var = 'CREATE TABLE ' + something
FROM somewhere
WHERE somecol = @.somevar
EXEC (@.var)
This is a crude example, but you get the point.
/Andrs Taylor|||Hi Karan
Try this way
SELECT * INTO tmpk FROM ...
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Unfortunately in the SQL Server enviornment you cannot select into a table
like you can in other legacy enviornments like Foxpro but you can declare a
local temp table with the same field makeup as the select statement outputs
and then use and insert to get the data into a table. You might have to mes
s
with it for a little while before you get it right, but it is possible to ge
t
somewhat the same results.
Declare @.v_sql NVARCHAR(4000)
SELECT @.v_sql =
INSERT #test
select hard_id,
Type_id,
description,
speed,
amount,
cache,
brand
from TU_Hardware
EXEC sp_executesql @.v_sql
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Correction.. you can do a select into statement but you cant do it the way
you were trying it.
If you go to masters table on any db and type the following:
select * from abc
It will give you an error because no table exist then type
select top 10 *
into abc
from sysobjects
You will have a new table in master db. You can do this with any table in
the from part of the statement.
Hope this helps..
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>
Sunday, March 25, 2012
create table from another table
Hello,
under MSSQL8 doing this "create table tblA as ( select Name from tblB );"
results in error 156 (wrong syntax near AS keyword). What is my mistake?
thanks and regards
MarkSee SELECT in Books Online:
select Name
into dbo.tblA
from dbo.tblB
Simon
Create Table Error
I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.
That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>
|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.com
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.
That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>
|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.com
Create Table Error
I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.comsql
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.comsql
Create Table Error
I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
--
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
>> following syntax.
>[quoted text clipped - 8 lines]
>> Server: Msg 2715, Level 16, State 7, Line 1
>> Column or parameter #1: Cannot find data type date.
--
Message posted via http://www.sqlmonster.com
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
--
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
>> following syntax.
>[quoted text clipped - 8 lines]
>> Server: Msg 2715, Level 16, State 7, Line 1
>> Column or parameter #1: Cannot find data type date.
--
Message posted via http://www.sqlmonster.com
Create Table but how?
Hi!
I've searched the tutorial for the create table syntax in asp.net. How to handle the SQL-string. How is it going? Do I have to use a SQL-Adapter or just the Connection? I can't access the Database with the Enterprise-Manager, so I have to do it in the good old way by using SQL. Can somebody give me the statements or a short codesample?
Thanks a lot
tobi.Net Data Access Technology (ADO.NET) is'nt different in windows applications and web applications.
You can do it either using a Data Adapter or just a connection and sqlCommands
If you can't access your database in Enterprise Manager , perhaps your database is'nt installed in SQL Server.|||Well, my SQL Server at home for tests is installed and running. But I still got some problems with connecting to it. Now I'm using a providers SQL-Database I can't connect with the Ent.-Manager to.
Can you give me a codesample for a create table execution? I havent' worked with .net so close...sql
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.
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.
Subscribe to:
Posts (Atom)