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]

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]]

> 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.

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]]

> /* ARTICLES */

alter table ARTICLES add column CONTENTTYPE_REF int null
go I got the statements straight out of the Microsoft book "Inside Microsoft
SQL Server 2000"

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]

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
www.SolidQualityLearning.com
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:OQ**************@TK2MSFTNGP09.phx.gbl...

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

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

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.)

[Aaron [SQL Server MVP]]

> My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of
the

book, and then changed ALTER COLUMN to ADD COLUMN.

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

A