Solved

String or binary data would be truncated.

Posted on 2003-12-01
13
199,322 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
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

867 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

12 Experts available now in Live!

Get 1:1 Help Now