error, string or binary data would be truncated wh

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>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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

Also had this problem occuring 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 colum definition in the relating history table(s) did not map the original table column lenght of nvarchar types in some specific cases.

Hope this hint helps someone else too.. ;)

Answer 6

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