Truncation-don't know whats wrong

  • 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.".

  • 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 (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?

  • 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.

  • 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/

  • 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 (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.

  • 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