Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLException : String or binary data would be truncated

Tags:

c#

sql

sql-server

People also ask

How do you fix String or binary data would be truncated?

How to fix “String or binary data would be truncated” The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size.

How do you resolve String or binary data would be truncated The statement has been terminated?

To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level.

How will you avoid String or binary data would be truncated in SQL?

Solution. To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.


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.


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.


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


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


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

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.