Can't delete column from table 1

[dobrios]

Hello,
I can't delete columns I created in table(s) and get the following error in Enterprise Manager:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'timPPInvtValLstWrk' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'ItemClassKey'.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I have db_owner and Admin permissions to this table.
I created test table, added new cols and tried deliting them. Same error.
Checked MS knowledge base and it mentions constrains, but this is data dump table and has no indexes or constrains.

Any idea what's wrong and how to fix it? Thanks much
Steve

 

[SQLBill]

Use Query Analyzer.

Run the command:

ALTER TABLE tablename DROP COLUMN columnname

Replace tablename and columnname with your actual names.

Refer to the BOL for information on ALTER TABLE and DROP COLUMN (which is listed under ALTER TABLE).

-SQLBill

Posting advice: FAQ481-4875

 

[dobrios]

Thanks for the reply Bill. I already tried that and I get the following error:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'ItemClassKey'.

 

[SQLBill]

Can you post the command you ran?

-SQLBill

Posting advice: FAQ481-4875

 

[dobrios]

alter table timPPInvtValLstWrk
drop column ItemClassKey

 

[SQLBill]

Might have found it......what SQL Server version are you using? 6.5? 7? 2000?

What is your 'compatibility' level set for?

This is from the BOL:

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed. A column cannot be dropped if it is:

A replicated column.

Used in an index.

Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.

Associated with a default defined with the DEFAULT keyword, or bound to a default object.

Bound to a rule.

Start with checking the compatibility level. Then check the rest of the 'no-nos'.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Okay. I got a star for this, but why? Did it solve your problem? If so, which was it that solved it? Your followup of what did work might help someone else in the future.

And as for the star, thanks - it's greatly appreciated.

-SQLBill

Posting advice: FAQ481-4875