SQLException: String or binary data will be truncated

Question

I have C # code that contains many insert instructions in a package. Following these instructions, I received the message "String or binary data was truncated" and roledback transactions.

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

Is there a smart way to find out which operator and which field caused this problem using exception handling? (SqlException)

Answer 1

In general, there is no way to determine which particular operator caused the error. If you use several, you can look at the profiler and see the last completed statement and see what can happen after that, although I do not know if this approach suits you.

In any case, one of your parameter variables (and the data inside it) is too large for the field in which it is trying to store the data. Check the dimensions of the parameters for the sizes of the columns and the field (s) in question should be obvious pretty quickly.

Answer 2

This type of error occurs when the SQL Server column data type has a length that is less than the length of the data entered in the input form.

Answer 3

this type of error usually occurs when you need to put characters or values ​​larger than indicated in the database table, as in this case: you specify transaction_status varchar (10) but you are actually trying to save _transaction_status which contain 19 characters. why did you encounter this type of error in this code

Answer 4

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 5

Typically, you insert a value that exceeds the maximum allowed value. Ex, the data column can contain up to 200 characters, but you insert a row of 201 characters

Answer 6

It depends on how you create Insert Calls. All as one call, or as individual calls within a transaction? If individual calls, then yes (when you go through the calls, catch the one that fails). If one big bell, then no. SQL processes the entire statement, so it is not part of the code.

Answer 7

• Get the query causing the problem (you can also use SQL Profiler if you don't have a source)
• Remove all WHERE clauses and other non-essential parts until you basically stay with SELECT and FROM elements
• Add WHERE 0 = 1 (this will only select the table structure)
• Add INTO [MyTempTable] immediately before the FROM clause

You should get something like

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

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

Answer 8

With Linq To SQL, I debug by registering the context, for example. Context.Log = Console.Out Then scanned SQL to check for 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 while scanning the table schema for values, the field was nvarchar (20), but the value was 22 characters

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

Answer 9

This may also be due to the fact that you are trying to return null to the database. Thus, one of your transactions can have zero values.

Answer 10

In our case, I am increasing the sql table to a valid character or field size that is smaller than the common characters sent from the front. Therefore, solve this problem.