ALTER TABLE ... ADD COLUMN with SQL Server

[Mike L]

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

[John Winterbottom]

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

[Mike L]

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.

[Dimitri Furman]

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

[Mike L]

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.

[Mike L]

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.