error string or binary data would be truncated when trying to insert

Question

I am running data bat file with the following lines   Rem Tis batch file will populate tables  cd program files Microsoft SQL Server MSSQL osql -U sa -P Password -d MyBusiness -i c  data sql   The contents of the data sql file is      insert Customers              CustomerID  CompanyName  Phone               Values  101   Southwinds   19126602729     There are 8 more similar lines for adding records   When I run this with start   run   cmd   c  data bat  I get this error message   1 gt 2 gt 3 gt 4 gt 5 gt      lt 1 row affected gt  Msg 8152  Level 16  State 4  Server SP1001  Line 1 string or binary data would be truncated    lt 1 row affected gt    lt 1 row affected gt    lt 1 row affected gt    lt 1 row affected gt    lt 1 row affected gt    lt 1 row affected gt    Also  I am a newbie obviously  but what do Level    and state   mean  and how do I look up error messages such as the one above  8152

User · Answer

You simply can t beat SQL Server on this  You can insert into a new table like this  select foo  bar into tmp new table to dispose later from my table  and compare the table definition with the real table you want to insert the data into  Sometime it s helpful sometimes it s not  If you try inserting in the final real table from that temporary table it may just work  due to data conversion working differently than SSMS for example   Another alternative is to insert the data in chunks  instead of inserting everything immediately you insert with top 1000 and you repeat the process  till you find a chunk with an error  At least you have better visibility on what s not fitting into the table

User · Answer

In one of the INSERT statements you are attempting to insert a too long string into a string  varchar or nvarchar  column   If it s not obvious which INSERT is the offender by a mere look at the script  you could count the  lt 1 row affected gt  lines that occur before the error message  The obtained number plus one gives you the statement number  In your case it seems to be the second INSERT that produces the error

User · Answer

Some of your data cannot fit into your database column  small   It is not easy to find what is wrong  If you use C  and Linq2Sql  you can list the field which would be truncated   First create helper class   public class SqlTruncationExceptionWithDetails   ArgumentOutOfRangeException       public SqlTruncationExceptionWithDetails System Data SqlClient SqlException inner  DataContext context            base inner Message         GetSqlTruncationExceptionWithDetailsString context                         lt summary gt          PArt of code from following link         http   stackoverflow com questions 3666954 string-or-binary-data-would-be-truncated-linq-exception-cant-find-which-fiel          lt  summary gt           lt param name  context  gt  lt  param gt           lt returns gt  lt  returns gt      static string GetSqlTruncationExceptionWithDetailsString DataContext context                StringBuilder sb   new StringBuilder             foreach  object update in context GetChangeSet   Updates                        FindLongStrings update  sb                      foreach  object insert in context GetChangeSet   Inserts                        FindLongStrings insert  sb                     return sb ToString               public static void FindLongStrings object testObject  StringBuilder sb                foreach  var propInfo in testObject GetType   GetProperties                          foreach  System Data Linq Mapping ColumnAttribute attribute in propInfo GetCustomAttributes typeof System Data Linq Mapping ColumnAttribute   true                                 if  attribute DbType ToLower   Contains  varchar                                          string dbType   attribute DbType ToLower                        int numberStartIndex   dbType IndexOf  varchar      8                      int numberEndIndex   dbType IndexOf      numberStartIndex                       string lengthString   dbType Substring numberStartIndex   numberEndIndex - numberStartIndex                        int maxLength   0                      int TryParse lengthString  out maxLength                        string currentValue    string propInfo GetValue testObject  null                        if   string IsNullOrEmpty currentValue   amp  amp  maxLength    0  amp  amp  currentValue Length  gt  maxLength                                                  string is too long                         sb AppendLine testObject GetType   Name         propInfo Name         currentValue     Max      maxLength                                                                              Then prepare the wrapper for SubmitChanges   public static class DataContextExtensions       public static void SubmitChangesWithDetailException this DataContext dataContext                  http   stackoverflow com questions 3666954 string-or-binary-data-would-be-truncated-linq-exception-cant-find-which-fiel         try                         this can failed on data truncation             dataContext SubmitChanges                             catch  SqlException sqlException    when  sqlException Message     String or binary data would be truncated                           if  sqlException Message     String or binary data would be truncated      only for EN windows - if you are running different window language  invoke the sqlException getMessage on thread with EN culture                 throw new SqlTruncationExceptionWithDetails sqlException  dataContext               else                 throw                      Prepare global exception handler and log truncation details   protected void Application Error object sender  EventArgs e        Exception ex   Server GetLastError        string message   ex Message        TODO - log to file     Finally use the code   Datamodel SubmitChangesWithDetailException

User · Answer

Also had this problem occurring on the web application surface   Eventually found out that the same error message comes from the SQL update statement in the specific table    Finally then figured out that the column definition in the relating history table s  did not map the original table column length of nvarchar types in some specific cases

User · Answer

From  gmmastros s answer  Whenever you see the message         string or binary data would be truncated    Think to yourself    The field is NOT big enough to hold my data   Check the table structure for the customers table   I think you ll find that the length of one or more fields is NOT big enough to hold the data you are trying to insert   For example  if the Phone field is a varchar 8  field  and you try to put 11 characters in to it  you will get this error

User · Answer

I had the same problem  even after increasing the size of the problematic columns in the table   tl dr  The length of the matching columns in corresponding Table Types may also need to be increased   In my case  the error was coming from the Data Export service in Microsoft Dynamics CRM  which allows CRM data to be synced to an SQL Server DB or Azure SQL DB   After a lengthy investigation  I concluded that the Data Export service must be using Table-Valued Parameters      You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine  such as a stored procedure or function  without creating a temporary table or many parameters    As you can see in the documentation above  Table Types are used to create the data ingestion procedure   CREATE TYPE LocationTableType AS TABLE        CREATE PROCEDURE dbo usp InsertProductionLocation    TVP LocationTableType READONLY   Unfortunately  there is no way to alter a Table Type  so it has to be dropped  amp  recreated entirely  Since my table has over 300 fields     I created a query to facilitate the creation of the corresponding Table Type based on the table s columns definition  just replace  table name  with your table s name    SELECT  CREATE TYPE  table name Type AS TABLE      STRING AGG CAST field AS VARCHAR max          CHAR 10          AS create type FROM     SELECT TOP 5000 COLUMN NAME         DATA TYPE         IIF CHARACTER MAXIMUM LENGTH IS NULL      CONCAT      IIF CHARACTER MAXIMUM LENGTH   -1   max   CONCAT CHARACTER MAXIMUM LENGTH                     IIF DATA TYPE    decimal   CONCAT      NUMERIC PRECISION       NUMERIC SCALE                  AS field   FROM INFORMATION SCHEMA COLUMNS   WHERE TABLE NAME     table name     ORDER BY ORDINAL POSITION  AS T    After updating the Table Type  the Data Export service started functioning properly once again

User · Answer

This error may be due to less field size than your entered data  For e g  if you have data type nvarchar 7  and if your value is  aaaaddddf  then error is shown as   string or binary data would be truncated

User · Answer

I used a different tactic  fields that are allocated 8K in some places   Here only about 50 100 are used   declare  NVPN list as table  nvpn            varchar 50   nvpn revision  varchar 5   nvpn iteration INT  mpn lifecycle  varchar 30   mfr            varchar 100   mpn            varchar 50   mpn revision   varchar 5   mpn iteration  INT --       INSERT INTO  NVPN LIST  SELECT  left nvpn            50     as nvpn          left nvpn revision  10     as nvpn revision          nvpn iteration          left mpn lifecycle  30           left mfr            100           left mpn            50           left mpn revision   5           mpn iteration          left mfr order num  50  FROM  DASHBOARD   dbo   mpnAttributes   NOLOCK  mpna   I wanted speed  since I have 1M total records  and load 28K of them

User · Answer

on sql server you can use SET ANSI WARNINGS OFF like this           using  SqlConnection conn   new SqlConnection  Data Source XRAYGOAT  SQLEXPRESS Initial Catalog  Healthy Care  Integrated Security True                          conn Open                 using  var trans   conn BeginTransaction                                  try                                       using cmd   new SqlCommand     conn  trans                                               cmd CommandText    SET ANSI WARNINGS OFF                       cmd ExecuteNonQuery                         cmd CommandText    YOUR INSERT HERE                       cmd ExecuteNonQuery                         cmd Parameters Clear                         cmd CommandText    SET ANSI WARNINGS ON                       cmd ExecuteNonQuery                         trans Commit                                                            catch  Exception                                         trans Rollback                                                  conn Close

User · Answer

Just want to contribute with additional information  I had the same issue and it was because of the field wasn t big enough for the incoming data and this thread helped me to solve it  the top answer clarifies it all    BUT it is very important to know what are the possible reasons that may cause it   In my case i was creating the table with a field like this   Select    as  Period    From Transactions Into  NewTable   Therefore the field  Period  had a length of Zero and causing the Insert operations to fail  I changed it to  XXXXXX  that is the length of the incoming data and it now worked properly  because field now had a lentgh of 6    I hope this help anyone with same issue

User · Answer

Another situation in which you can get this error is the following   I had the same error and the reason was that in an INSERT statement that received data from an UNION  the order of the columns was different from the original table  If you change the order in  table3 to a  b  c  you will fix the error   select a  b  c into  table1 from  table0  insert into  table1     select a  b  c from  table2     union     select a  c  b from  table3

User · Answer

Kevin Pope s comment under the accepted answer was what I needed    The problem  in my case  was that I had triggers defined on my table that would insert update insert transactions into an audit table  but the audit table had a data type mismatch where a column with VARCHAR MAX  in the original table was stored as VARCHAR 1  in the audit table  so my triggers were failing when I would insert anything greater than VARCHAR 1  in the original table column and I would get this error message

User · Answer

I had this issue although data length was shorter than the field length  It turned out that the problem was having another log table  for audit trail   filled by a trigger on the main table  where the column size also had to be changed

User · Answer

When I tried to execute my stored procedure I had the same problem because the size of the column that I need to add some data is shorter than the data I want to add   You can increase the size of the column data type or reduce the length of your data

User · Answer

Another situation  in which this error may occur is in  SQL Server Management Studio  If you have  text  or  ntext  fields in your table  no matter what kind of field you are updating  for example bit or integer   Seems that the Studio does not load entire  ntext  fields and also updates ALL fields instead of the modified one  To solve the problem  exclude  text  or  ntext  fields from the query in Management Studio

User · Answer

I had the same issue  The length of my column was too short   What you can do is either increase the length or shorten the text you want to put in the database

User · Answer

A 2016 2017 update will show you the bad value and column   A new trace flag will swap the old error for a new 2628 error and will print out the column and offending value   Traceflag 460 is available in the latest cumulative update for 2016 and 2017  https   support microsoft com en-sg help 4468101 optional-replacement-for-string-or-binary-data-would-be-truncated Just make sure that after you ve installed the CU that you enable the trace flag  either globally permanently on the server      or with DBCC TRACEON  https   docs microsoft com en-us sql t-sql database-console-commands dbcc-traceon-trace-flags-transact-sql view sql-server-ver15

[sql] error, string or binary data would be truncated when trying to insert

Examples related to sql

Examples related to sql-server