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
Solved

String or binary data would be truncated.

Posted on 2003-12-01
13
199,338 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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