Fixing Truncation Issues with T-SQL Flex

If you’ve worked with SQL Server for any length of time, you’ve run into this error:

Msg 8152, Level 16, State 14, Procedure MyProcedure, Line 162
String or binary data would be truncated.

While the problem is simple enough - you’re trying to put a big value in a small field - it’s a maddening error because SQL Server doesn’t give you a hint as to which field or value is causing the issue.

It’s not even so bad when you’re copying data from a well-known source (like many fields from a single table), but sometmes you’re copying data from an arbitrary query.  Having to look up the data sizes from across your database (or worse - figuring out the data types from calculated fields or functions/views/etc.) can be terribly time-consuming.

I wrote a tool called T-SQL Flex that can help.  It’s a plugin for SQL Server Management Studio that generates scripts for any arbitrary SQL query.  It has many uses, but one I like is helping to fix “String or binary data would be truncated” problems.

Assumptions:

1. You’re working in a dev environment and have good + tested backups
2. You understand the risks of “following instructions on some guy’s blog”.
3. You have T-SQL Flex installed in your SSMS.
4. You have a query that is giving “String or binary data would be truncated” errors.

General process for fixing a truncation issue with T-SQL Flex:

1. Find the statement in your query that is causing the error.  The easiest way is to comment out everything but the first statement (Highlight, then CTRL+K, then CTRL+C).  Then run and see if it bombs.  If not, uncomment the next statement (Highlight, then CTRL+K, then CTRL+U) and repeat.  Eventually you should get the error.  Please note that you will have to be smart about things like branching logic (IF/ELSE), changes being made to permanent tables, and any sort of transaction work (BEGIN TRAN/COMMIT/ROLLBACK etc.) – that’s all out of scope for this blog post. If your procedure is really big, you may wish to search by halves.
2. Once you’ve found the offending statement, do two things:
   • Add a new SELECT TOP 0 statement to query the schema of the destination table.  If your INSERT statement had field names, use them in the SELECT statement.  If not, it should be SELECT TOP 0 * FROM (whatever the destination is).  This should work for regular tables as well as #temp tables and @table variables.
   • Comment out the INSERT INTO part of the offending statement, and leave the SELECT part there.

   For example, if the offending statement that you found in step 1 was:

   INSERT INTO #MyTempTable (field1, field2, field3)
   SELECT productId, price, [description]
   FROM #MyOtherTempTable
   WHERE productId = @productId;
   --rest of query is still commented out.

   You should convert it to this:

   SELECT TOP 0 field1, field2, field3 FROM #MyTempTable;
   --INSERT INTO #MyTempTable (field1, field2, field3)
   SELECT productId, price, [description]
   FROM #MyOtherTempTable
   WHERE productId = @productId;
   --rest of query is still commented out.

3. Now you should be ready to run your statement in T-SQL Flex.  With the above modifications, the output from T-SQL Flex after clicking Run ‘n’ Rollback should be something like this:

   CREATE TABLE #Result1 (
     field1 INT NOT NULL,
     field2 DECIMAL(18,5) NOT NULL,
     field3 NVARCHAR(100) NOT NULL
   );
   
   CREATE TABLE #Result2 (
     productId INT NOT NULL,
     price DECIMAL(18,5) NOT NULL,
     [description] NVARCHAR(200) NOT NULL
   );

   Notice how in the above results, field3 in the first table (our destination) has a NVARCHAR(100) column, but the corresponding field in the second table (our data source) is NVARCHAR(200).  In this scenario, that is likely our problem – a value that fits in a NVARCHAR(200) field may not fit in a NVARCHAR(100).  You still have to compare the type of each column in the source to each column in the destination, but T-SQL Flex does all the leg work for you.  If there are a lot of fields, it shouldn’t be too bad to paste the temp table definitions side-by-side in Excel.

4. Once you’ve identified the mismatched fields, you should either fix the capacity of the too-small fields in the destination, or else explicitly truncate the source data (such as specifying LEFT([description],100)).  Put the statement back to how it was, and now it should no longer raise the truncation error.
5. Once that statement is fixed, you may wish to try the whole query again to see if the main issue is resolved (uncomment the rest of the query), or else continue “stepping through” by going back to step 1.

Good luck!