April 4, 2012 at 1:32 am
I am using Data Conversion to convert Unicode data (which actually has strange texts from different countries) to NonUnicode (ASCII). My Source is Nvarchar(400) and my destination is varchar(400). I am using string[DT_STR] in data conversion to convert to NonUnicode. However its giving me truncation error all this while. I have tried from increasing the destination field, tried with Advanced Editor in the source, tried redirecting errors, still the truncation error persists. Please advise.
The error reads :::::::::::>>>
[Data Conversion [420]] Error: Data conversion failed while converting column "Description" (412) to column "Copy of Description" (427). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
April 4, 2012 at 9:55 am
The error is telling you that you have unicode characters in the source and you are losing them when converting to non-unicode. It isn't really truncating because of length, it is the OR part of the error or one or more characters had no match in the target code page".
If you really don't care about losing the data then you need to set the data conversion to ignore the truncation error. Personally I'd change the target to nvarchar so I don't lose the data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 10:00 am
Jack Corbett (4/4/2012)
The error is telling you that you have unicode characters in the source and you are losing them when converting to non-unicode. It isn't really truncating because of length, it is the OR part of the error or one or more characters had no match in the target code page".If you really don't care about losing the data then you need to set the data conversion to ignore the truncation error. Personally I'd change the target to nvarchar so I don't lose the data.
Thanks Jack. If I set the destination, isn't it going to be Unicode data again? Is there a way we can covert to NonUnicode (ASCII) without losing data?
April 4, 2012 at 10:03 am
If you are receiving unicode data, I would be concerned about losing data. I have to agree with Jack and vote for storing the information in nvarchar columns and retaining the unicode information.
April 4, 2012 at 10:07 am
Murphy'sLaw (4/4/2012)
Jack Corbett (4/4/2012)
The error is telling you that you have unicode characters in the source and you are losing them when converting to non-unicode. It isn't really truncating because of length, it is the OR part of the error or one or more characters had no match in the target code page".If you really don't care about losing the data then you need to set the data conversion to ignore the truncation error. Personally I'd change the target to nvarchar so I don't lose the data.
Thanks Jack. If I set the destination, isn't it going to be Unicode data again? Is there a way we can covert to NonUnicode (ASCII) without losing data?
As I said in your other thread, not all UNICODE characters have a translation in ASCII. This is the reason I pointed you to the (n)char datatype in BOL. Make your destination nvarchar and it will retain all the characters.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2012 at 10:13 am
Murphy'sLaw (4/4/2012)
Jack Corbett (4/4/2012)
The error is telling you that you have unicode characters in the source and you are losing them when converting to non-unicode. It isn't really truncating because of length, it is the OR part of the error or one or more characters had no match in the target code page".If you really don't care about losing the data then you need to set the data conversion to ignore the truncation error. Personally I'd change the target to nvarchar so I don't lose the data.
Thanks Jack. If I set the destination, isn't it going to be Unicode data again? Is there a way we can covert to NonUnicode (ASCII) without losing data?
No. As Sean has said, there are UNICODE characters that don't translate to ASCII, which is why you are getting the error. You either need to use nchar/nvarchar in the destination or lose those characters. I'd chose nchar/nvarchar in the destination.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 5:45 pm
Jack Corbett (4/4/2012)
Murphy'sLaw (4/4/2012)
Jack Corbett (4/4/2012)
The error is telling you that you have unicode characters in the source and you are losing them when converting to non-unicode. It isn't really truncating because of length, it is the OR part of the error or one or more characters had no match in the target code page".If you really don't care about losing the data then you need to set the data conversion to ignore the truncation error. Personally I'd change the target to nvarchar so I don't lose the data.
Thanks Jack. If I set the destination, isn't it going to be Unicode data again? Is there a way we can covert to NonUnicode (ASCII) without losing data?
No. As Sean has said, there are UNICODE characters that don't translate to ASCII, which is why you are getting the error. You either need to use nchar/nvarchar in the destination or lose those characters. I'd chose nchar/nvarchar in the destination.
Looks like Even if i risk to lose characters, it still won't let me do the conversion. Instead of conversion, is it possible to create a View that reads Unicode data and displays in ASCII without losing data? Thank you all for your input so far.
April 5, 2012 at 7:07 am
NO, there are not translations for all characters.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply