title: SQL Server String Binary Data Would be Truncated Catch
body: <p>When updating a series of tables with many VarChar(n) columns, some updates where I perform a Replace operation kill my transaction with "string or binary data would be truncated." I am trying to come up with a repeatable way to handle this.</p>
  
  <p>One option is to first select the row ID and length of the would-be-transformed column if its length exceeds the size of the column. I am not a fan of this because it seems like the I will get no query plan re-use and it will perform slowly.</p>
  
  <p>Another option would be to figure out how to catch the rows (first row?) causing the fail and report that to the caller. I don't think this will work because the error level is too high to let me pinpoint the problem.</p>
  
  <p>I could also ignore the whole thing by taking a Substring/Left of the new value and putting that in, but that will mask it and not let me know where actual problems are.</p>
  
  <p>I'm using MSSQL 2008.</p>
  
  <p>Edit - an idea here looks kind of promising: 
  <a href="http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx" rel="nofollow">http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx</a>:</p>
  
  <pre><code>    1. Take a copy of the destination table:
      SELECT * INTO #Destination FROM DestinationTable WHERE 1=2
      GO
      2. Set ANSI_WARNINGS OFF and perform the insert into the copy of the destination table, then set ANSI_WARNINGS ON again:
      SET ANSI_WARNINGS OFF
      GO
      INSERT INTO #Destination
      SELECT * FROM SourceTable
      GO
      SET ANSI_WARNINGS ON
      GO
      As ANSI_WARNINGS is off SQL Server truncates the fields rather than produces the warning.
      3. Next compare what you would like to insert against what was inserted with the ANSI_WARNINGS OFF truncating. By using EXCEPT you only select the rows that don't match, and have therefore been truncated:
      SELECT * FROM SourceTable
      EXCEPT
      SELECT * FROM #Destination
      GO
  </code></pre>