Post reply

Trying to change column collation throws string truncation error

  • October 14, 2022 at 6:07 pm

    #4099737

    Hello experts,

    I tried running the following T-SQL to change the collation of a table column from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CS_AS.

    ALTER TABLE mytable ALTER COLUMN mycol varchar(4000) COLLATE SQL_Latin1_General_CP1_CS_AS

    I got the following errors (the second one more detailed after I turned on the trace flag DBCC TRACEON(460, -1)).

    Msg 8152, Level 16, State 30, Line 29
    String or binary data would be truncated.

    Msg 2628, Level 16, State 1, Line 42
    String or binary data would be truncated in table 'mydb.dbo.mytable', column 'mycol'. Truncated value: ''.

    Does anyone know why trying to change the collation causes this error, and how I can fix it? I have no idea why case-sensitivity would have anything to do with column data length.

    Thanks for any help.

    -- webrunner

     

    • This topic was modified 2 years, 1 month ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • October 14, 2022 at 8:02 pm

    #4099777

    Are you sure you matched the original data type and length?  For example, if the original columns was varchar(8000) and you specified varchar(4000), obviously data might get truncated.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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