CODEWITHSUNDEEP
sqlanywhere
Vitoc
Vitoc

Reputation: 185

Sybase - Changing size of "Name" column - "Illegal Column Definition" error

We have a Companies table in our database with a "Name" varchar column and its size is currently 30 characters. Our seemingly simple task of changing its size to 50 characters has turned into a bit of an issue. When trying to change it and save the changes through Sybase Central we get the following error:

[Sybase][ODBC Driver][SQL Anywhere]Illegal column definition: Name
SQLCODE: -1046
SQLSTATE: 42000
SQL Statement: ALTER TABLE "DBA"."Companies" ALTER "Name" VARCHAR(50)

We've tried various escaping characters around the column thinking it might have something to do with the word "Name" being treated differently internally by Sybase. We have no indexes or constraints on this column and have removed the single trigger that did exist just trying to isolate any potential factors. Further perplexing us, we have a Companies_a table that keeps track of all changes to the Companies table that has nearly the exact same schema including the Name column. We are able to change that column's size without issue which seems to indicate it's not necessarily an issue with the word "Name". I've gone through all the tabs in Sybase Central for this table, and don't see anything special/different about this table or this column.

Googling this issue is difficult as the word "Name" is extremely common. We have workarounds we can do (ie. creating temp column, copying, dropping & recreating the column, copying back) but if possible I'd like to understand exactly what's happening here and why.

Upvotes: 4

Views: 4456

Answers (2)

Qasim Mirza
Qasim Mirza

Reputation: 71

ALTER TABLE [tableName]
ALTER  [ColumnName] varchar(4000) NULL

Upvotes: 0

Vitoc
Vitoc

Reputation: 185

I believe our Companies table and this column were likely created in a previous version of ASA. The inline_max column in sys.systabcol was set to null and we could not change it.

Running the following statement set the defaults:

ALTER TABLE "DBA"."Companies" ALTER "Name" inline use default prefix use default;

I was then able to run this statement without error:

ALTER TABLE "DBA"."Companies" ALTER "Name" VARCHAR(50);

Here's where I found the general concept for the fix: Sybase SqlAnywhere forum thread

Upvotes: 3

Related Questions