[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

String or binary data would be truncated.

Posted on 2003-12-01
13
Medium Priority
?
199,415 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 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