473,837 Members | 1,657 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 49702
"Mike L" <ml******@arms. co.za> wrote in message
news:c3******** *************** ***@posting.goo gle.com...
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******@hotma il.com> wrote in message news:<2h******* *****@uni-berlin.de>...
"Mike L" <ml******@arms. co.za> wrote in message
news:c3******** *************** ***@posting.goo gle.com...
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.c o.za (Mike L) wrote in
news:c3******** *************** ***@posting.goo gle.com:
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*****@cloud9 9.net> wrote in message news:<Xn******* *************** ******@127.0.0. 1>...
On May 24 2004, 11:09 am, ml******@arms.c o.za (Mike L) wrote in
news:c3******** *************** ***@posting.goo gle.com:
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*****@cloud9 9.net> wrote in message news:<Xn******* *************** ******@127.0.0. 1>...
On May 24 2004, 11:09 am, ml******@arms.c o.za (Mike L) wrote in
news:c3******** *************** ***@posting.goo gle.com:
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
9841
by: Dylan Nicholson | last post by:
Seems that Oracle 9.2 (using MS ODBC driver) requires extra parentheses when adding multiple columns to a table: ALTER TABLE MyTable ADD (MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)) vs ALTER TABLE MyTable ADD MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)
2
44305
by: Heist | last post by:
Hi, I just want to know to turn this: CREATE TABLE . ( NOT NULL , (50) COLLATE French_CI_AS NULL , NOT NULL , (50) COLLATE French_CI_AS NOT NULL , NULL , NULL ) ON into this:
7
5413
by: Jon Combe | last post by:
I have created the following test SQL code to illustrate a real problem I have with some SQL code. CREATE TABLE JCTable ( CustomerName varchar(50) ) ALTER TABLE JCTable ADD CustomerNo int INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe' , 1 ) INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Bill Gates' , 1 ) UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
1
3307
by: Danny | last post by:
I'm trying to simply change a column definition from Null to Not Null. It's a multi million row table. I've already checked to make sure there are no nulls for any rows and a default has been created for the column. My log is set to autogrow and as the alter column colname char(6) Not Null runs the log begins to grow. If I use no check BOL say the optimizer won't consider the change. How can I change the nullability of a column that...
3
4091
by: Darin | last post by:
I have many columns in different tables set to money. I want to change those to be decimal(18,2). I do the following: ALTER TABLE AROpenItem ALTER COLUMN copn_taxamt decimal(19,2) I get an error: The object 'DF__AROpenIte__copn___4D94879B' is dependent on column
4
3915
by: Jeff Kish | last post by:
Hi. I have a database I need to supply something (I'm assuming a t-sql script.. maybe something else is better) to update customer tables with. The operations include mostly changing varchar lengths, though a couple of columns were renamed. I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer to generate the scripts.
2
31519
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1, column2), );
2
12871
by: Jeff_in_MD | last post by:
Hi, I'm trying to add a column to a table, then update that column with a query. This is all within a single batch. Sqlcmd gives me an error on the update, saying "invalid column xxx", because it doesn't know the column got added. We used to get around this in "osql" by using the EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255) not null default ' '") However, it looks like sqlcmd actually checks each query...
7
2800
by: quincy451 | last post by:
drop table . CREATE TABLE . ( NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , NULL , NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , (16) COLLATE
0
9828
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10263
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7801
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6988
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5663
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5840
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4469
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4034
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.