Incorrect Syntax

[GitarJake]

Hello all,

Newbie here.
SQL 2000, Windows 2000

I'm trying to alter tables in my SQL DB using statements like the following:

/* AD_GROUPS */
alter table AD_GROUPS alter column AD_GROUP_NAME nvarchar(64)not null
go

/* ARTICLES */
alter table ARTICLES add column CONTENTTYPE_REF int null
go

I get error messages like:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'column'

I got the statements straight out of the Microsoft book "Inside Microsoft
SQL Server 2000"

Thanks in advance for helping to train this raw recruit!

Jake

[David Portas]

Re: Incorrect Syntax

Just grop the "COLUMN" keyword from the ADD statement:

ALTER TABLE Articles ADD contenttype_ref INT NULL

Your ALTER COLUMN statement is correct. It's just a peculiarity of the
syntax that the word "COLUMN" isn't required after ADD.

--
David Portas
SQL Server MVP
--

[Aaron [SQL Server MVP]]

Re: Incorrect Syntax

> Just grop the "COLUMN" keyword from the ADD statement:[color=blue]
>
> ALTER TABLE Articles ADD contenttype_ref INT NULL
>
> Your ALTER COLUMN statement is correct. It's just a peculiarity of the
> syntax that the word "COLUMN" isn't required after ADD.[/color]

And pardon the pun, but this missing part of the syntax won't be added
anytime soon, either. ;-)

--

http://www.aspfaq.com/

(Reverse address to reply.)

[Aaron [SQL Server MVP]]

Re: Incorrect Syntax

> /* ARTICLES */[color=blue]
> alter table ARTICLES add column CONTENTTYPE_REF int null
> go[/color]
[color=blue]
> I got the statements straight out of the Microsoft book "Inside Microsoft
> SQL Server 2000"[/color]

What page? I'd be interested to see a line like that, with the incorrect
column keyword where it is in your statement.

--

http://www.aspfaq.com/

(Reverse address to reply.)

[Kalen Delaney]

Re: Incorrect Syntax

I'd be interested to see that also. :-)

Not that there are absolutely no mistakes in the book, but I just did a
search of the electronic version of the book, and did not find this error.
In fact, I found this note, basically warning about the word 'column' not
being used when adding a new column:


NOTE

----------------------------------------------------------------------------
----

Notice the syntax difference between dropping a column and adding a new
column: the word COLUMN is required when dropping a column, but not when
adding a new column to a table.



My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of the
book, and then changed ALTER COLUMN to ADD COLUMN.

--
HTH
----------------
Kalen Delaney
SQL Server MVP

https://www.SolidQualityLearning.com

"Aaron [SQL Server MVP]" <ten.xoc@dnartr eb.noraa> wrote in message
news:OQBNC0cbEH A.3792@TK2MSFTN GP09.phx.gbl...[color=blue][color=green]
> > /* ARTICLES */
> > alter table ARTICLES add column CONTENTTYPE_REF int null
> > go[/color]
>[color=green]
> > I got the statements straight out of the Microsoft book "Inside[/color][/color]
Microsoft[color=blue][color=green]
> > SQL Server 2000"[/color]
>
> What page? I'd be interested to see a line like that, with the incorrect
> column keyword where it is in your statement.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>[/color]

[Aaron [SQL Server MVP]]

Re: Incorrect Syntax

> My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of
the[color=blue]
> book, and then changed ALTER COLUMN to ADD COLUMN.[/color]

That was my guess too, but wanted to prod a bit more; maybe he found
something the rest of us missed. ;-)

A