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

User · Answer

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]

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Simply Used this    MessageBox Show cmd4 CommandText ToString     in c  net and this will show you main query   Copy it and run in database

User · Answer

Get the query that is causing the problems  you can also use SQL Profiler if you dont have the source  Remove all WHERE clauses and other unimportant parts until you are basically just left with the SELECT and FROM parts Add WHERE 0   1  this will select only table structure  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

User · Answer

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

User · Answer

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

[c#] SQLException : String or binary data would be truncated

Examples related to c#

Examples related to sql

Examples related to sql-server