Error changing a varchar column to allow nulls

Question

Post reply

Error changing a varchar column to allow nulls

clbast

Newbie

Points: 6
More actions
December 14, 2011 at 10:58 am

#251973

I have a table called Assessment that has a varchar column called AssessedWithParcel. I want to change this field to allow nulls.
Here is what the field currently looks like when I scripted out the table:
[AssessedWithParcel] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
After dropping Default Value and Check Constraints on that field, I tried changing it to allow NULLs by running this statement:
ALTER TABLE dbo.Assessment ALTER COLUMN AssessedWithParcel int NULL
After the query runs a while I get an error:
The conversion of the varchar value '026003930010' overflowed an int column. Maximum integer value exceeded.
I don't understand why I get an error about overflowing an 'int' column when it is a varchar column.
Is this the wrong way to make a varchar field allow nulls?
Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

You altered the column from varchar to int.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

clbast (12/14/2011)
After dropping Default Value and Check Constraints on that field, I tried changing it to allow NULLs by running this statement:
ALTER TABLE dbo.Assessment ALTER COLUMN AssessedWithParcel int NULL

I think you meant

ALTER TABLE dbo.Assessment ALTER COLUMN AssessedWithParcel varchar(26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

clbast Newbie Points: 6 More actions · Answer 3

GilaMonster (12/14/2011)
clbast (12/14/2011)
After dropping Default Value and Check Constraints on that field, I tried changing it to allow NULLs by running this statement:
ALTER TABLE dbo.Assessment ALTER COLUMN AssessedWithParcel int NULL
I think you meant
ALTER TABLE dbo.Assessment ALTER COLUMN AssessedWithParcel varchar(26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Oh, I actually changed it to an int! Oops. Well I'm glad it was just a stupid mistake I made by copying and pasting code.

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I'm sure we've all done that a few times.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Error changing a varchar column to allow nulls

Cookies on SQLServerCentral