"String or binary data would be truncated" and field specifications

Question

Hi all,

 

i have "String or binary data would be truncated" error when i try to execute an insert statment.

 

can i find witch field is affected by this error? (for return it to the user)

 

thank's all

Answer 1

If possible it would be far better to truncate the string to the maximum allowable length within the client application (and warn the user if necessary) before passing it to SQL Server for insertion into the database.

 

For debug purposes you could run SQL Profiler to witness the values of the parameters being passed into the stored procedure then work through the SQL code and locate where the error is being caused.

 

Chris

Answer 2

this is not possible because client and db musn't be linked (db can be modified). i don't know futur size of these fields.

 

it's a feature for my users, indicating whitch field is too long

Answer 3

As far as I am aware, there's no way to determine which column's length has been exceeded.

 

You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

 

Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

 

Chris

 

 

Code Snippet

--This batch will fail with the SQL Server error message

DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @MyParameter = REPLICATE('Z', 52)

INSERT INTO @MyTable(MyValue)

VALUES (@MyParameter)

GO

 

--This batch will fail with a custom error message

DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @MyParameter = REPLICATE('Z', 52)

IF LEN(@MyParameter) > 10

BEGIN

     RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

     INSERT INTO @MyTable(MyValue)

     VALUES (@MyParameter)

END

GO

 

Answer 4

I had this very same problem recently when doing SQL Reporting.  I solved by changing varchar(20) to varchar(50) in my temporary table declarations.  It so happens that some of the older records in the table were bigger than 20 so when selecting into the temporary tables an error would result.

 

- Will

Answer 5

[Will wrote:

 

I had this very same problem recently when doing SQL Reporting.  I solved by changing varchar(20) to varchar(50) in my temporary table declarations.  It so happens that some of the older records in the table were bigger than 20 so when selecting into the temporary tables an error would result.

 

- Will]

 

Your solution took care of my problem. It was small and often small things are hard to detect.. thanx.

Answer 6

Welcome to the Transact SQL Forum, Tins.  I hope you will continue to contribute. 

 

 

Kent

 

EDIT:

 

Also, would you be kind enough to click "Yes" for the "Helpful" button for Will?

Answer 7

Yes, your simple reply cleared my problem. thk u.

Answer 8

I just found a GREAT way around this common problem:

Select *
into TempTable
from SourceTable

Then do a schema comparison in SQL Server (Generate a Create Script) on TempTable and compare it to DestinationTable any differences become visually appearant..

-David S.

Axos Technologies, Inc.

Answer 9

I was facing the same problem, didn’t find any elegant way of finding this error. I grouped all my insert columns and kept on commenting some block, after narrowing down the selection criteria finally I was able to find the culprit.

 

Answer 10

 

Is SQL2008 more verbose about these errors? or any patch or special configuration settings for SQL2005?

Answer 11

To my knowledge major re-work on the error messages is not a trumpeted feature of 2008.  This error will always mean that some column in your statement was not wide enough to hold data you were inserting or updating into it, and your varchar (or similar) columns are going to be the most likely culprits, Varbinary columns are likely too but are less used than varchar.

Answer 12

 

Thanks Will, I had same problem before and solved now.

 

 

Answer 13

 

Hi all,

 

i have a "String or binary data would be truncated" error when i try to insert a date.

 

i have a column "date" and i choose smalldatetime for it. In my application i have  used a textbox a button and a calendar.

the code i wrote to show the calendar is:

 

Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged

TextBox4.Text = Calendar1.SelectedDate.ToShortDateString

Calendar1.Visible = False

End Sub

Protected Sub cal1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cal1.Click

Try

If (TextBox4.Text.Trim IsNot "") Then

Calendar1.SelectedDate = Convert.ToDateTime(TextBox4.Text)

End If

Catch ex As Exception

System.Console.Write(ex.Message.ToString)

End Try

Calendar1.Visible = True

End Sub

 

 

 

it works fine but when i run my program and choose a date to insert in my table i get the error i mention above.

 

i don't know what to do!!

 

thanks

Answer 14

Try using the below in your stored proc.

Set NoCount On;

Answer 15

Hello

This is notorious error in SQL. What's happening, is that we are inserting or updating nchar or varchar columns with values that are larger than the defined size of those columns:

Example : we have table called MomoTable and Column Called MomoColumn with size of 10 nvarchar

we are inserting value of nvarchar 200 then we should increase the size of our column as follows:

Solution in for the exmaple :

 

ALTER TABLE MomoTable  ALTER COLUMN  MomoColumn nvarchar(255)

GO

 

Solution in general Theoric format :

 

ALTER TABLE [TableName]  ALTER COLUMN  [ColumnName] nvarchar(255)

GO

 

Cheers

---

Momo

Answer 16

Msg 8152, Level 16, State 14, Line 7 String or binary data would be truncated.

Answers to several of the posts above in this thread.

You can prevent this error very easily by getting the column size metadata from INFORMATION_SCHEMA views (see T-SQL code below).

SQL Server 2008 error message is not any different than previous versions.

Surely it would be easy to return the offending column on a single line insert!  What if you are inserting 1 million rows with one statement and there are 100,000 offending rows?

Well that is tough....however, incredible it may sound but SSIS can handle it. This is a normal feature of SSIS Data Flow. It filters out the offending rows, and there are several ways to do it.

What can you do in T-SQL?

For one-liner insert you just compare the target length with the intended insert length.

For datasets with multiple rows, you have to do filtering like:

1. WHERE ISNUMERIC (ColumnA) = 1
2. WHERE ISDATE (ColumnX) = 1
3. WHERE LEN (StagingName) <=

(SELECT CHARACTER_MAXIMUM_LENGTH
FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='Production'
  AND TABLE_NAME='Product'
  AND COLUMN_NAME = 'Name' )

Column metadata demo.
 

-- OBTAIN COLUMN SIZE METADATA FROM INFORMATION_SCHEMA VIEW
SELECT ColumnName=COLUMN_NAME, Limit=CHARACTER_MAXIMUM_LENGTH
INTO #MaxLengths
FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='Production'
  AND TABLE_NAME='Product'
  AND DATA_TYPE IN ('varchar','nvarchar','char','nchar')
SELECT * FROM #MaxLengths
GO
/*
ColumnName				Limit
Name					50
ProductNumber			        25
Color					15
Size					5
SizeUnitMeasureCode		       3
WeightUnitMeasureCode	               3
ProductLine				2
Class					2
Style					2
*/
DROP TABLE #MaxLengths

---

Kalman Toth
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 17

I hope you folks know the msg 8152 is simply a warning. If you don't care your data been chopped off, simply run SET ANSI_WARNINGS OFF, you get rid of this error, your program or process will run just fine. This answer may not fit all people but it's one of the option you need to know about it.

Answer 18

Well I believe I have proven a Bug in SQL 2008.  I'm getting this error but none of my columns have oversize data.

I have proven it as follows:

INSERT INTO Table1
Select
<Big list of columns>
From
<big list of tables>

The above fails.  So:

Select
<Big list of columns>
INTO Table2
From
< big list of tables>

This works as columns are created based on the size of the source data.

Now to try and find the data that is too big you run this script, replacing the table names with yours, and paste the results into a new query:

--SELECT CLAUSE followed by MyKey = <Unique column identifier>
select ',['+c.name+'] = case when max(len(['+c.name+'])) > '+ltrim(str(c.max_length))+' then 1 else 0 End'
from sys.columns  c
join sys.objects o
on o.object_id=c.object_id
and o.name = '<table1>'
and system_type_id in (35,99,167,231,175,239) -- this looks at Text,nText, Varchar,nVarchar,char and nchar)
--GROUP BY CLAUSE
select ',['+c.name+']'
from sys.columns  c
join sys.objects o
on o.object_id=c.object_id
and o.name = '<table1>'
and system_type_id in (35,99,167,231,175,239) -- this looks at Text,nText, Varchar,nVarchar,nchar)

--WHERE CLAUSE
select ',['+c.name+'] =1 OR'
from sys.columns  c
join sys.objects o
on o.object_id=c.object_id
and o.name = '<table1>'
and system_type_id in (35,99,167,231,175,239) -- this looks at Text,nText, Varchar,nVarchar,nchar)

You need to type in the the SELECT Command and some Key column to let you retrieve the problem data later and delete the first  comma

Follow the list with an into #mytemp

Type in the FROM <table2> Clause

Type in the GROUP BY Clause and remove the first comma.

Run the resulting SQL

Start a new Query:
Select * from #mytemp
Type in the WHERE clause and remove the last OR

In my case it shows that none of my string data is over size and I don't have any binary data.

My insert script used to work fine in SQL 2005.

You could perhpas avoid the temporary table by using a HAVING Clause but when problem tracking I like to have small steps.

My work around was to take the scheme for Table 2, reduce some of the needless Varchar(255) to somthing more sensible and use it to replace the table1 schema.

Answer 19

I am using SQL Server 2008 Enterprise edition SP2 trial version.

I have a table that have 5 columns all varchar having a total length upto around 40 characters.

Now I am trying to execute an t-sql script that has 3-4 insert statements and its failing with 'String or Binary data would be truncated' and execution is failed.

If I execute these statements one by one they all execute successfully with no error.

I found that if the length of t-sql batch is less than 256 (approximate) character its executing but if more than 256 its failing.

I also found that same batch (even more than 256 charcahter long) is successfully executing in SQL Server 2005.

I tried using the SET ANSI_WARNINGS OFF also but that didn't help.

I haven't found any setting to change the maximum size limit of batch.

Thanks in advance.

Subhash Chandra

Answer 20

Thanks friend...

I got my issue resolved by checking this.....post

Answer 21

This fixed my issue.  Thank you!!!

Answer 22

It is helpful for the forum readersl to describe exactly what fixed what issue. Thanks.

---

Kalman Toth
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 23

Brilliant. This is the kind of answer which I think was begged by the question. It certainly helped me - thanks.

Answer 24

You may find this blog post on this exact problem helpful as well

How to find what column caused the String or binary data would be truncated message

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 25

for the  CHARACTER_MAXIMUM_LENGTH is coming as -1 does it means that it is max ie nvarchar(max)

Answer 26

Yes, it means it's either varchar(max) or nvarchar(max) depending on the type.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 27

thanks Chris

Answer 28

Hi,

Try increasing the datatype length of the table where you want to insert.

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 29

Ya that's a datatype issue. When you try to insert something more than the length of permitted data type

Answer 30

I had same problem and i solved by changing varchar(20) to varchar(50)

Will was correct thanks for saving my time

Answer 31

Try to increase the value of your nvarchar(I used nvarchar in my case) i.e if it's nvarchar(50) make it nvarchar(100). This worked for me.