469,366 Members | 2,205 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

ALTER TABLE ... ADD COLUMN with SQL Server

Hello all

I'm trying to migrate a BE from Access to SQL Server.

I've been making changes to the Access BE from the FE with SQL
statements, and want to do the same with the BE in SQL Server.

When running the statement "ALTER TABLE tblTest DROP COLUMN
TestColumn3" from the FE with a SQL Server BE, it works OK.
Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
DATE" however gives an error "Incorrect syntax near the keyword
‘COLUMN'"

I've been trying to find help on syntax in the help file, and some
Access-to-SQL Server books, but can't find anything on this specific
problem.

Many thanks in advance for any suggestions and help.

Regards
Mike L
Nov 13 '05 #1
5 49193
"Mike L" <ml******@arms.co.za> wrote in message
news:c3**************************@posting.google.c om...
Hello all

I'm trying to migrate a BE from Access to SQL Server.

I've been making changes to the Access BE from the FE with SQL
statements, and want to do the same with the BE in SQL Server.

When running the statement "ALTER TABLE tblTest DROP COLUMN
TestColumn3" from the FE with a SQL Server BE, it works OK.
Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
DATE" however gives an error "Incorrect syntax near the keyword
'COLUMN'"

DATE is not a dataytpe. Use DATETIME instead.

I've been trying to find help on syntax in the help file, and some
Access-to-SQL Server books, but can't find anything on this specific
problem.


Books Online (BOL) which comes with sql server is a model for what a help
system should look like IMO. Look up Data Types and Alter Table statement.
Nov 13 '05 #2
I eventually did find info on data types in the meantime, and tried
the DATETIME data type, but with the same result.

Maybe I'm missing something more fundamental...

(My SQL Server came with Office XP Dev Ed, and limited help)

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2h************@uni-berlin.de>...
"Mike L" <ml******@arms.co.za> wrote in message
news:c3**************************@posting.google.c om...
Hello all

I'm trying to migrate a BE from Access to SQL Server.

I've been making changes to the Access BE from the FE with SQL
statements, and want to do the same with the BE in SQL Server.

When running the statement "ALTER TABLE tblTest DROP COLUMN
TestColumn3" from the FE with a SQL Server BE, it works OK.
Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
DATE" however gives an error "Incorrect syntax near the keyword
'COLUMN'"

DATE is not a dataytpe. Use DATETIME instead.

I've been trying to find help on syntax in the help file, and some
Access-to-SQL Server books, but can't find anything on this specific
problem.


Books Online (BOL) which comes with sql server is a model for what a help
system should look like IMO. Look up Data Types and Alter Table statement.

Nov 13 '05 #3
On May 24 2004, 11:09 am, ml******@arms.co.za (Mike L) wrote in
news:c3**************************@posting.google.c om:
When running the statement "ALTER TABLE tblTest DROP COLUMN
TestColumn3" from the FE with a SQL Server BE, it works OK.
Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
DATE" however gives an error "Incorrect syntax near the keyword
‘COLUMN'"


The proper syntax is ALTER TABLE tblTest ADD TestColumn3 ...
(not ... ADD COLUMN ...)

As John said, this is very well described in Books Online under ALTER TABLE
topic.

--
remove a 9 to reply by email
Nov 13 '05 #4
Thanks Dimitri

Dimitri Furman <df*****@cloud99.net> wrote in message news:<Xn****************************@127.0.0.1>...
On May 24 2004, 11:09 am, ml******@arms.co.za (Mike L) wrote in
news:c3**************************@posting.google.c om:
When running the statement "ALTER TABLE tblTest DROP COLUMN
TestColumn3" from the FE with a SQL Server BE, it works OK.
Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
DATE" however gives an error "Incorrect syntax near the keyword
?COLUMN'"


The proper syntax is ALTER TABLE tblTest ADD TestColumn3 ...
(not ... ADD COLUMN ...)

As John said, this is very well described in Books Online under ALTER TABLE
topic.

Nov 13 '05 #5
Thanks Dimitri

Dimitri Furman <df*****@cloud99.net> wrote in message news:<Xn****************************@127.0.0.1>...
On May 24 2004, 11:09 am, ml******@arms.co.za (Mike L) wrote in
news:c3**************************@posting.google.c om:
When running the statement "ALTER TABLE tblTest DROP COLUMN
TestColumn3" from the FE with a SQL Server BE, it works OK.
Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
DATE" however gives an error "Incorrect syntax near the keyword
?COLUMN'"


The proper syntax is ALTER TABLE tblTest ADD TestColumn3 ...
(not ... ADD COLUMN ...)

As John said, this is very well described in Books Online under ALTER TABLE
topic.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dylan Nicholson | last post: by
2 posts views Thread by Heist | last post: by
7 posts views Thread by Jon Combe | last post: by
1 post views Thread by Danny | last post: by
3 posts views Thread by Darin | last post: by
2 posts views Thread by RamaKrishna Narla | last post: by
2 posts views Thread by Jeff_in_MD | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.