error, string or binary data would be truncated when trying to insert
Solution 1
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.
Solution 2
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.
Solution 3
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.
Solution 4
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();
Solution 5
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 :)
Related videos on Youtube
karthik
.NET Developer having experience in MVC, Web Development, WCF Services.
Updated on March 24, 2022Comments
-
karthik about 2 years
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 #
, andstate #
mean, and how do I look up error messages such as the one above: 8152?-
b_levitt over 2 yearsIt's worth noting that there is now an option to replace this overly generic error with a new error that includes the actual column name. See stackoverflow.com/a/63474873/852208
-
-
Kevin Pope over 8 yearsAlso note that the impacted fields could be in a trigger. Hopefully I remember this the next time this happens...
-
Hnin Htet Htet Aung almost 7 yearsThanks. Mine was because the sql column in tableA is varchar(100). It also insert to another table, in which, the column is varchar(50).
-
DailyFrankPeter over 6 yearsIs there any way to see in debug which field would be truncated?
-
Cátia Matos about 6 yearsI'm having the same issue, how to find which column is causing the error?
-
Andriy M about 6 years@AndréBastos: Perhaps you could submit that as a question (unless someone else has already done that, in which case there may be a ready answer to it somewhere).
-
George Pamfilis almost 6 yearsPlease consider rephrasing your answer by adding commas, periods and fixing your grammar errors.
-
autopilot almost 6 yearsThe same problem happened in my case too. A trigger operation was the culprit.
-
Prakash over 5 yearsThis error is because your column cannot hold data after the length you fixed. Eg;
Firstname nvarchar(5)
If you insert more than 5 characters you will get the error -
Sha over 5 yearsThis answer helped me - my nvarchar fields are large enough but I have an ntext field. Seems to be some error in Management Studio / SMSS.
-
jaycer over 3 yearsSome versions of SQL Server can tell you WHICH data would have been truncated. Use
DBCC TRACEON(460);
to enable this feature. See Aaron Bertrand's answer -
Sxc about 3 yearsI knew it was a real SQL Exception. However, the exception message "String or binary data would be truncated" kind of misleading ? it actually terminated the execution instead of Truncating the string.
-
aboy021 almost 3 yearsUsing
SET ANSI_WARNINGS OFF
turned out to be exactly the diagnostic tool that I needed. Not something I plan to use in production though. -
Ash K about 2 yearsYour solution requires the field width information to be defined in the entity model (for eg:
[Column("SomefieldName", TypeName = "varchar(10)")]
annotation inSomefieldName
property), so sadly this didn't work out for me. So I went ahead and created methods to get the column width information directly from the database table. Checkout the answer here: stackoverflow.com/a/71329931/8644294