CODEWITHSUNDEEP
sqlsql-server
karthik
karthik

Reputation: 17832

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

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>2>3>4>5>....<1 row affected>
Msg 8152, Level 16, State 4, Server SP1001, Line 1
string or binary data would be truncated.

<1 row affected>

<1 row affected>

<1 row affected>

<1 row affected>

<1 row affected>

<1 row affected>

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?

Upvotes: 306

Views: 1139927

Answers (18)

b_levitt
b_levitt

Reputation: 7415

A 2016/2017 update will show you the bad value and column

KB4468101 was part of SQL Server 2016 SP2 CU6 and SQL Server 2017 CU12.

Message template:

Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber
String or binary data would be truncated in table '%.ls', column '%.ls'.
Truncated value: '*%.ls'.

Message example: MSSQLSERVER Properties > Startup Parameters > -T460

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: enter image description here

...or with DBCC TRACEON:

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

460

Replaces data truncation message ID 8152 with message ID 2628. For more information, see KB4468101.

Starting with SQL Server 2019 (15.x), to accomplish this at the database level, see the VERBOSE_TRUNCATION_WARNINGS option in ALTER DATABASE SCOPED CONFIGURATION.

Applies to: SQL Server 2016 (13.x) Service Pack 2 CU 6, SQL Server 2017 (14.x) CU 12, and later versions.

Note: Starting with database compatibility level 150, message ID 2628 is the default and this trace flag has no effect. For database compatibility level 140 or lower, message ID 2628 remains an opt-in error message that requires Trace Flag 460 to be enabled, and this database scoped configuration has no effect.

Scope: Global or session.

Upvotes: 3

Esperento57
Esperento57

Reputation: 17462

on sql server you can use SET ANSI_WARNINGS OFF...

When OFF, data is truncated to the size of the column and the statement succeeds.

...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();

}

Upvotes: 8

ramankumarsingla
ramankumarsingla

Reputation: 21

This Error Comes only When any of your field length is greater than the field length specified in sql server database table structure.

To overcome this issue you have to reduce the length of the field Value . Or to increase the length of database table field .

Upvotes: 1

Tomas Kubes
Tomas Kubes

Reputation: 25098

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))
    {
    }

    /// <summary>
    /// PArt of code from following link
    /// http://stackoverflow.com/questions/3666954/string-or-binary-data-would-be-truncated-linq-exception-cant-find-which-fiel
    /// </summary>
    /// <param name="context"></param>
    /// <returns></returns>
    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) && maxLength != 0 && currentValue.Length > 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();

Upvotes: 11

Max Favilli
Max Favilli

Reputation: 6449

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.

Upvotes: 0

subash sapkota
subash sapkota

Reputation: 81

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

Upvotes: 0

Hans Schulze
Hans Schulze

Reputation: 164

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.

Upvotes: 0

karthik
karthik

Reputation: 17832

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.

Upvotes: 742

webMac
webMac

Reputation: 203

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.

Upvotes: 7

bizimunda
bizimunda

Reputation: 1035

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.

Upvotes: 7

Chamila Maddumage
Chamila Maddumage

Reputation: 3866

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.

Upvotes: 3

Sibs
Sibs

Reputation: 91

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.

Upvotes: 0

Marco Roy
Marco Roy

Reputation: 5243

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 & 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! :)

Upvotes: 5

RaRdEvA
RaRdEvA

Reputation: 766

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 :)

Upvotes: 11

Sevast
Sevast

Reputation: 109

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

Upvotes: 1

alterfox
alterfox

Reputation: 1695

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.

Upvotes: 31

hola77
hola77

Reputation: 89

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

Upvotes: 8

Andriy M
Andriy M

Reputation: 77657

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 <1 row affected> 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.

Upvotes: 20

Related Questions