Solved

String or binary data would be truncated.

Posted on 2003-12-01
13
199,354 Views
Last Modified: 2011-08-18
Hi,

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

This error happen when I tried to insert a value too long in a varchar column:
(insert "123456" in a column varchar(3))

Is there a way to tell the server to truncate the value and insert it ?
any Server Option ?
I don't mind if the value is truncate, but I have to make sure the value is inserted...

Thanks

Mary
0
Comment
Question by:msl22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 9851392
How about:

INSERT LEFT("123456",3) INTO .....

This will truncate before attempting to insert.
0
 

Author Comment

by:msl22
ID: 9851406

The problem is that I need to use a generic method which do not know the field length...

0
 
LVL 4

Expert Comment

by:BulZeyE
ID: 9851603
Are you talking for a particular table or for any table that you are insertin into??
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 9851771
Why wouldn't you edit it on the frontend and stop it from even making it through to the server?

If you really want to (not a good idea) you can disable ANSI_WARNINGS and it will insert any way....
0
 
LVL 6

Expert Comment

by:lausz
ID: 9851781
TRY ADDING THIS LINE AT THE BEGINNING OF YOUR QUERY

SET ANSI_WARNINGS OFF
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9851811
why generic?

if its generic then you should either perform validation
to ensure that the input parameters are correct (within the dbms environment)

or conform the data to the required internal db structure by querying the relevant
information schema views column/table information...

0
 
LVL 34

Expert Comment

by:arbert
ID: 9851889
like I said above, you can change the ansi warnings, but this turns off a lot of other errors that you shouldn't overlook...
0
 
LVL 6

Expert Comment

by:lausz
ID: 9851934
As Albert and Lowfatspread said you have to turn on this setting when you finish the query..

SET ANSI_WARNINGS OFF


...

Your query

..

SET ANSI_WARNINGS ON

0
 

Expert Comment

by:mike95
ID: 12293894
You can also configure warnings off for all the connections by default by using this stored procedure:
sp_dboption 'DatabaseName', 'ANSI warnings', 'FALSE'
0
 
LVL 5

Expert Comment

by:BryanMI
ID: 20284817
I'm not sure where you are going with that, but another idea would be to query the field length from syscolumns and store the length in a variable.  You can use the length variable with the LEFT as stated above to determine where to chop the value.

I would write a function for this as it would be the easiest.
0
 
LVL 6

Expert Comment

by:Jerryuk007
ID: 20563683
Alternative to the LEFT Command could be the command <b>substring(@yourstring,1,3)</b>...

Jerry
0
 

Expert Comment

by:gerhardpretorius
ID: 21717541
What would be the SET ANSI_WARNINGS OFF equivalent when using LINQ to SQL?
0
 

Expert Comment

by:VivekrkKhare
ID: 24288432
The best way to do this you have to use import-export wizard or DTS. it will automatically manage the length given for column.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how the fundamental information of how to create a table.

737 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