String or binary data would be truncated error glared back at me when I tried to alter a column from a VARCHAR(100) to a VARCHAR(50).
The reason was obvious, there was data in the table that was longer that what I was trying to make the new column size.
When you encounter an issue like this you have to ask yourself whether or not that matters. Is it better to not change the column size and keep it the way it is, or does it not matter and you can simply truncate the data?
How to Alter a Column and Allow Truncation
If you decide that it is ok to truncate the data you can simply turn off ANSI_WARNINGS e.g:
SET ANSI_WARNINGS OFF GO
Alternatively you could identify and trim the text yourself by using the following SQL statements.
In this case I will identify any rows > 50 characters, and trim them back a bit.
USE DBAFire_DB CREATE TABLE #truncator (words VARCHAR(100)) INSERT INTO #truncator SELECT 'asdkfj;aksjdf;askdfj;asdkfj;asdfjka;sdfljadfasdfaasdfasdfasdfasd' INSERT INTO #truncator SELECT 'asdkfj;asdfjka;sdfljadfasdfaasdfasdfasdfasd' SELECT words FROM #truncator WHERE LEN(words) > 50 -- if you try to alter now you will get the truncation error ALTER TABLE #truncator ALTER COLUMN words VARCHAR(50) -- Update the word lenght to no greater than 50 characters UPDATE #truncator SET words = LEFT(WORDS, 50) WHERE LEN(words) > 50 -- Now you can alter the column ALTER TABLE #truncator ALTER COLUMN words VARCHAR(50) -- Clean up DROP TABLE #truncator
Now you have two methods that you can use to work around this error.
If you do decide to go ahead and truncate, remember to take a backup first as you don’t want to go removing something that may have been required.
