October 14, 2003 at 5:58 am
When I am trying to run given T-SQL on SQL2000 with database compatible-level 65, getting error near 'COLUMN' keyword.
T-SQL: ALTER TABLE Stock_Master ALTER COLUMN width VARCHAR(25) NOT NULL;
Where, original datatype of column 'width' in table 'Stock_Master' is INTEGER.
Kindly HELP.
Regards,
Indra
Regards,
Indra
October 14, 2003 at 6:31 am
Hi,
I am sure, 'width' column would have allowed null values when it's datatype is INT. So, if you remove NOT NULL from your ALTER statement. It works.
Just check it once.
Madhu
Madhu
October 14, 2003 at 3:37 pm
My guess is that your table has null values as Madhu indicated and you either need to update the null values to a specific value or drop off the NOT NULL. Please look at Books Online for Alter Table.
FROM BOL:
NULL can be specified in ALTER COLUMN to make a NOT NULL column allow null values, except for columns in PRIMARY KEY constraints. NOT NULL can be specified in ALTER COLUMN only if the column contains no null values. The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed, such as:
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
If NULL or NOT NULL is specified with ALTER COLUMN, new_data_type [(precision [, scale ])] must also be specified. If the data type, precision, and scale are not changed, specify the current column values.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 15, 2003 at 9:17 am
ALTER COLUMN is not allowed in an ALTER TABLE statement if compatibility level is 65 or lower. See the same page in BOL referenced by Gary.
Greg
October 15, 2003 at 11:15 am
Good point Greg. I totally missed the 6.5 compatibility. I'm so used to working with a SQL 2K database I never even looked at that.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply