Alter Column Syntax

Question

Alter Column Syntax

Stewart Joslyn

SSCertifiable

Points: 6131
More actions
April 21, 2006 at 1:57 am

#112769

On SQL Server 7 :
ALTER TABLE dbo.EstDealDetail ALTER COLUMN FundNo SmallInt
gives
Incorrect syntax near 'COLUMN'
The table and column exist and FundNo currently is TinyInt
Help !!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

This seems strange. It works perfectly fine for me. Has to be something else than the syntax, it's just fine. Have you tried that exact statement in QA?

/Kenneth

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

Thanks for the response.

Yes. Copied directly from QA. Neither I nor a couple of colleagues could see anything wrong, hence the posting.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 3

Just in case of a simple typo, I edited the posted code to :

SELECT FundNo FROM dbo.EstDealDetail

That runs fine.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Would this work or produce the same error? (alter is copied from first post)

use tempdb

go

create table dbo.EstDealDetail ( FundNo tinyint not null )

go

insert dbo.EstDealDetail select 1

go

ALTER TABLE dbo.EstDealDetail ALTER COLUMN FundNo SmallInt

go

/Kenneth

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 5

Gives the same error.

I just tried it on a different server though and it worked fine!

Both are Windows Authentication and Ive ensured that Ive got every permission in sight on the database and the table.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Then check the db compat level. If it's < 70 you'll see this error.

/Kenneth

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 7

Brilliant !!!

Its 6.5

Not 'my' database so I didnt think to check.

Many thanks for your help.