SQLException : String or binary data would be truncated

Question

I have a C# code which does lot of insert statements in a batch. While executing these statements, I got "String or binary data would be truncated" error and transaction roledback.

To find out the which insert statement caused this, I need to insert one by one in the SQLServer until I hit the error.

Is there clever way to findout which statement and which field caused this issue using exception handling? (SqlException)

Answer 1

In general, there isn't a way to determine which particular statement caused the error. If you're running several, you could watch profiler and look at the last completed statement and see what the statement after that might be, though I have no idea if that approach is feasible for you.

In any event, one of your parameter variables (and the data inside it) is too large for the field it's trying to store data in. Check your parameter sizes against column sizes and the field(s) in question should be evident pretty quickly.

Answer 2

This type of error occurs when the datatype of the SQL Server column has a length which is less than the length of the data entered into the entry form.

Answer 3

this type of error generally occurs when you have to put characters or values more than that you have specified in Database table like in that case: you specify transaction_status varchar(10) but you actually trying to store _transaction_status which contain 19 characters. that's why you faced this type of error in this code

Answer 4

Generally it is that you are inserting a value that is greater than the maximum allowed value. Ex, data column can only hold up to 200 characters, but you are inserting 201-character string

Answer 5

BEGIN TRY
    INSERT INTO YourTable (col1, col2) VALUES (@val1, @val2)
END TRY
BEGIN CATCH
    --print or insert into error log or return param or etc...
    PRINT '@val1='+ISNULL(CONVERT(varchar,@val1),'')
    PRINT '@val2='+ISNULL(CONVERT(varchar,@val2),'')
END CATCH

Answer 6

For SQL 2016 SP2 or higher follow this link

For older versions of SQL do this:

1. Get the query that is causing the problems (you can also use SQL Profiler if you dont have the source)
2. Remove all WHERE clauses and other unimportant parts until you are basically just left with the SELECT and FROM parts
3. Add WHERE 0 = 1 (this will select only table structure)
4. Add INTO [MyTempTable] just before the FROM clause

You should end up with something like

SELECT
 Col1, Col2, ..., [ColN]
INTO [MyTempTable]
FROM
  [Tables etc.]
WHERE 0 = 1

This will create a table called MyTempTable in your DB that you can compare to your target table structure i.e. you can compare the columns on both tables to see where they differ. It is a bit of a workaround but it is the quickest method I have found.

Answer 7

It depends on how you are making the Insert Calls. All as one call, or as individual calls within a transaction? If individual calls, then yes (as you iterate through the calls, catch the one that fails). If one large call, then no. SQL is processing the whole statement, so it's out of the hands of the code.

Answer 8

It could also be because you're trying to put in a null value back into the database. So one of your transactions could have nulls in them.

Answer 9

With Linq To SQL I debugged by logging the context, eg. Context.Log = Console.Out Then scanned the SQL to check for any obvious errors, there were two:

-- @p46: Input Char (Size = -1; Prec = 0; Scale = 0) [some long text value1]
-- @p8: Input Char (Size = -1; Prec = 0; Scale = 0) [some long text value2]

the last one I found by scanning the table schema against the values, the field was nvarchar(20) but the value was 22 chars

-- @p41: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [1234567890123456789012]

Answer 10

In our own case I increase the sql table allowable character or field size which is less than the total characters posted from the front end. Hence that resolve the issue.

Answer 11

Most of the answers here are to do the obvious check, that the length of the column as defined in the database isn't smaller than the data you are trying to pass into it.

Several times I have been bitten by going to SQL Management Studio, doing a quick:

sp_help 'mytable'

and be confused for a few minutes until I realize the column in question is an nvarchar, which means the length reported by sp_help is really double the real length supported because it's a double byte (unicode) datatype.

i.e. if sp_help reports nvarchar Length 40, you can store 20 characters max.

Answer 12

Simply Used this: MessageBox.Show(cmd4.CommandText.ToString()); in c#.net and this will show you main query , Copy it and run in database .

Answer 13

Checkout this gist. https://gist.github.com/mrameezraja/9f15ad624e2cba8ac24066cdf271453b.

public Dictionary<string, string> GetEvilFields(string tableName, object instance)
    {
        Dictionary<string, string> result = new Dictionary<string, string>();

        var tableType = this.Model.GetEntityTypes().First(c => c.GetTableName().Contains(tableName));

        if (tableType != null)
        {
           int i = 0;

           foreach (var property in tableType.GetProperties())
           {
               var maxlength = property.GetMaxLength();
               var prop = instance.GetType().GetProperties().FirstOrDefault(_ => _.Name == property.Name);

               if (prop != null)
               {
                   var length = prop.GetValue(instance)?.ToString()?.Length;

                   if (length > maxlength)
                   {
                        result.Add($"{i}.Evil.Property", prop.Name);
                        result.Add($"{i}.Evil.Value", prop.GetValue(instance)?.ToString());
                        result.Add($"{i}.Evil.Value.Length", length?.ToString());
                        result.Add($"{i}.Evil.Db.MaxLength", maxlength?.ToString());
                        i++;
                    }
               }
            }
       }

       return result;
    }