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!

Talking TypeScript on the .NET Rocks! Podcast

I appeared on the .NET Rocks podcast show #1149 this week.  I had a blast talking about TypeScript, the new ES6 features coming in TypeScript 1.5, and grunt-ts, our TypeScript compiler task for Grunt.

I wanted to add a few things.

Firstly, here are a number of additional links that are relevant to our discussion:

My Pluralsight Course: Practical TypeScript Migration
Great review of what it's been like working with TypeScript over 24 months:  http://tedpatrick.com/2014/10/16/24-months-with-typescript/
Destructuring in ES6: https://hacks.mozilla.org/2015/05/es6-in-depth-destructuring/
Get started with grunt-ts if you've never used Grunt: https://github.com/TypeStrong/grunt-ts/blob/master/docs/DetailedGettingStartedInstructions.md
Node Inspector - debug Node.js code in the Chrome Dev Tools - works great with TypeScript also: https://github.com/node-inspector/node-inspector
Download TypeScript 1.5 beta for Visual Studio 2013 (transpiles much of ES6): https://visualstudiogallery.msdn.microsoft.com/107f89a0-a542-4264-b0a9-eb91037cf7af
Download TypeScript 1.4 for Visual Studio 2013 (current official release with minimal ES6 transpilation support): https://visualstudiogallery.msdn.microsoft.com/2d42d8dc-e085-45eb-a30b-3f7d50d55304

Around the 10 minute mark, I said that you should put type annotations on function calls.  That would be awful – I meant to say you should put type annotations on function declarations.

Around the 20 minute mark, I said that you can use ES6 features today with TypeScript 1.5 because it offers some transpilation features.  Richard then said transpilation was interesting because (paraphrasing) "when browsers come out with finished ES6 implementations, your page should go faster", and I sort of disagreed.  The reason is that transpiling actually changes your code from using one pattern to code using a different pattern.  Richard's statement was correct with regards to polyfills which is something that a dynamic language like JavaScript makes possible.  Developers who create polyfills attempt to implement future features in legacy environments.  For example, in ES6, there is a new `hypot` method on the JavaScript Math object, so Math.hypot(3,4) === 5.  A polyfill could be created for that feature to work in ES5 fairly easily.  A well-written polyfill would check to see if there was already a hypot property on the Math object; if so, it would just fall back to the native implementation which would (theoretically) be faster (though for something simple like a hypotenuse calculation it'd likely be no different).

Features that there's no way to polyfill require transpilation.  For example: class MyClass { }.  There is no (usable) class keyword in ES5, and so there is no way to create something in ES5 at runtime that would allow that code to run and do something reasonable; the syntax just doesn't exist.  That's why the transpilers have to change that code into a function declaration.  Since it becomes a function declaration, there's no way to natively optimize it as a class (beyond general optimizations for constructor function patterns that the engines would already have).  So for transpiled features, if you want to let the engine act natively, you'd have to dynamically serve the right code to the right engines - or (more practically) just serve the lowest common denominator like ES5 to all, which generally works great in today's browsers.

// Here's a cool destructuring example:
// This is valid TypeScript (in 1.5) and JavaScript (ES6)
// TypeScript will compile it down to work correctly with ES5
//  by creating a "normal" parameter in the signature of doSomething
//  and add code in the body to pull out its baz property.

var myObject = {
  foo: 1,
  bar: false,
  baz: "hello"
};

function doSomething({ baz }) {
  console.log(baz);
}

doSomething(myObject); // hello

I said something silly around the 36 minute mark: "convert the inside of a tight loop first"; that's a very bad idea and doesn't even really make sense. :-(  What I should have said is "convert something central to your application first - like a common utilities library or a configuration file".  The reason for converting "from the inside out" as I recommend is that each new thing you convert will infer many types from the things that have already been typed.  As you grow the scope of TypeScript in your application, you will begin to find that you need to annotate less and less (other than function signatures) - and the things you do need to annotate may even be errors.  I feel that annotating function signatures is something that ultimately helps you write better code since it leads to compiler-enforced documentation, and it's usually necessary if you've activated --noImplicitAny.

Around the 51 minute mark, I was talking about how TypeScript doesn't help people enough with regards to componentizing projects of medium complexity and I misspoke a few times by using the word Solution (meaning a Visual Studio Solution) when I meant to say Project (meaning a Visual Studio Project).  TypeScript works great within a single Visual Studio project - the trouble comes when you want to use TypeScript across projects (like in a large/complex solution).  There are many hacky workarounds to this, but I still don't know of a great "this is the obviously right way to do it" story; I hope that the TypeScript team and the community will eventually put together a great solution for this.

I had a great time appearing on the podcast – thanks for the opportunity!

Excel 2013 Conditional Formatting for Columns

If you have two columns in an Excel sheet, and you want to conditionally format fields in the second column when the values are not equal, do this:

Our starting point – we want Grape to be highlighted because it is not the same as Cherry.
Select all of column B by clicking the header.  On the Home tab, click Conditional Formatting… New Rule.
Choose “Format only cells that contain”, and “Cell Value” “not equal to” “=A1”.  Note that putting the “=” is very important because otherwise Excel will think you’re comparing it to the string literal “A1”.

Then click “Format…” and select the appropriate formatting (such as setting a fill color).
Then click OK and OK.

Note: If you don’t want the header to be highlighted, you can apply the conditional formatting to only the cells with actual data, but you will have to adjust the corresponding comparison row in the formula.  In our example, we might apply the conditional formatting to cells B$2$:B$5$ (which starts at row 2), but we’d have to set the formula to “not equal to” “=A2” (also starts at row 2).  If you want to select down to the end of the sheet, your selection formula would be something like =$B$2:$B$1048576 on the Excel 2007+ “big grid”.

Enabling Portable Git in Node.js command prompt on Windows

GitHub for Windows doesn't put Git in the PATH by default.  If you'd like your Node.js command prompt to have the git command available by default, simply edit your nodevars.bat file.  By default, this is in C:\Program Files\nodejs\.  You will have to run your text editor in an administrative context for this to work.

Replace this line in your nodevars.bat file:
set PATH=%APPDATA%\npm;%~dp0;%PATH%

With these two lines:

for /F %%A in ('"dir /s /b /OD %userprofile%\appdata\local\github\portableGit_*"') do set gitPath=%%A\bin
set PATH=%APPDATA%\npm;%~dp0;%PATH%;%gitPath%

See this gist for an easier-to-copy version:

https://gist.github.com/nycdotnet/f7d7b8de0c55b7081cb0#file-new-code

That will set a variable called %gitPath% with the location of git.exe, and then append it at the end of your path in the Node.js command prompt.  Because of the /OD switch, it will use the version of Portable Git whose folder has the latest modified date.  If Portable Git is not found, you will get a harmless extra ; in the PATH.

Using an alternate TypeScript compiler inside Visual Studio

TypeScript team member Daniel Rosenwasser provided instructions for replacing the TypeScript compiler and language service used by Visual Studio here:

https://github.com/Microsoft/TypeScript/issues/1110#issuecomment-62451204

He also described how to update the lib.d.ts file later in the same issue:

https://github.com/Microsoft/TypeScript/issues/1110#issuecomment-65865932

These instructions are not guaranteed to work, so be sure to back up your original files.