Question about Data Truncation 1

[LonnieJohnson]

You know when you get those messages that say...


String or binary data would be truncated.

...and the whole process stops and nothing gets appended because a field is small than the amount of data to go in?

Is ther any way to bypass this and have it add the data anyway and just let it get truncated?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[SQLSister]

You could write an insert statment that casts the data into the smaller size.

 

[jhall01]

if you only want the first 50 characters and you don't care about the rest of the data you could right a query that does a LEFT(yourfield, 50), then inserts the data into the table, or you could increase the size of "yourfield" in the table.

I prefer the use of substring(yourfield,1,50) instead of left(yourfield,50) only because left() is a proprietary function to Microsoft.

This will avoid those truncated errors.

If you are concerned about losing the data you could create another field for overrun but you might as well just increase the field size. unless you are trying to maintain a searchable field and you are already maxing out at 8000 characters.

Jon

 

[bowline]

Their suggestions to use substring are definitely ideal, but if you just want a quick fix, run this before your script:
set ansi_warnings off
Then this afterwards:
set ansi_warnings on
These warnings include a lot more than just data truncation, so you would want to check them out in the BOL before going with it. (just look up "SET" and browse around)

It is always better to prep the data correctly though...I only resort to this on ODBC connections with apps that I cannot change, or if I am too lazy to figure out which field I messed up.

 

[LonnieJohnson]

Thanks bowline,

This CAN work as a quick fix.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!