Post reply

Error changing a varchar column to allow nulls

  • 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.

  • December 14, 2011 at 11:03 am

    #1421482

    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/

  • December 14, 2011 at 11:14 am

    #1421490

    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

  • December 14, 2011 at 11:20 am

    #1421495

    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!

  • December 14, 2011 at 12:02 pm

    #1421525

    🙂

    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

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

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