Solved

SQL Error "String or binary data would be truncated"

Posted on 2001-06-20
5
445 Views
Last Modified: 2008-03-10
I was inserting some stuff into a database today and got the following error "String or binary data would be truncated."  I hadn't seen it before.  I looked at my code and coudln't find anything wrong.  I then retried the INSERT with the EXACT same data, and it worked fine.  Have any idea what the error is or where it comes from?
0
Comment
Question by:NJordan72
5 Comments
 
LVL 7

Expert Comment

by:daniel_c
Comment Utility
Yupe, the length of the data you insert (usually string) exceeds the length of the field.

For example:
tabel: PRODUCT
field: productname varchar(10)


You do this:
INSERT INTO Product('1234567890123')

It will raise the error.
0
 
LVL 18

Expert Comment

by:nigelrivett
Comment Utility
If you tried the insert with the same data then it implies that it it is not just the values that are too long.

How were you doing the insert that fails.
I suspect your test was via query analyser and there is something in the interface that failed previously.
0
 

Author Comment

by:NJordan72
Comment Utility
I was doing the insert via an Activer Server Page.
0
 
LVL 7

Expert Comment

by:daniel_c
Comment Utility
Okay, post your ASP code, then let's see it.
0
 
LVL 3

Accepted Solution

by:
ibro earned 15 total points
Comment Utility
error "String or binary data
would be truncated."  is showed only if the SET ANSI_WARNINGS is ON. If you tried to insert the same data in the table with ANSI_WARNINGS set OFF it will not raise any error and it will automaticly truncate your data to max size of the field.
Probably you got the error in enviroment with set ANSI_WARNINGS ON, and normaly it is set OFF.
Check your ODBC settings if your application uses ODBC, or if you use Query Analizer, just type:
SET ANSI_WARNINGS OFF
  hope this will help!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 39
MIcrosoft SQL, query with ANY joins 13 28
Managing SQL log files, SQL Server 2014 6 53
Calculating Business Hours 18 58
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now