?
Solved

String or binary data would be truncated.

Posted on 2003-12-01
13
Medium Priority
?
199,391 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 34

Accepted Solution

by:
arbert earned 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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