By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

ALTER TABLE ... ADD COLUMN with SQL Server

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
"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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.