Character Conversion

  • govind.sharma

    SSC Journeyman

    Points: 91

    I use a VB6, and SQL Server 2000

    There are Two Database of SQL Server 2000 on Seperate Server.

    First Database have Japanese_CI_AS Collation

    Second Database have Latin1_General_CI_AS.

    Problem is That When I Execute a Query Using Vb6 With ADO On Second Database, its Ok, but When Same Query Running On First Database with Jap_CI_AS, then It give Error:

    String or binary data would be truncated.

    The statement has been terminated.

    The Query Contain Unicode Value for A Column and The All String Column Defined in Table As Char Datatype.

    Query is

    insert into Recv_info_Tmp(prod_section_c, sub_section_c, slip_no, Order_no, product_no, cusdesch_c1, cusdesch_c2 ) values ('235400', '00', '10003', 'TEST0123451', 'ZZZZZ999999999999', '9993', '93')

    The "K" in Between Z for Product_no Column is Unicode character. and when String made the "K" is converted to "‚j". The Length of Product_No col is CHar(18)

    Please Solve My Problem as soon as possible.

    For More Detail send mail to govind.sharma@mind-infotech.com

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    The query should be:

    insert into Recv_info_Tmp(prod_section_c, sub_section_c, slip_no, Order_no, product_no, cusdesch_c1, cusdesch_c2 ) values ('235400', '00', '10003', 'TEST0123451', N'ZZZZKZ999999999999', '9993', '93')

    Note the N prefix before the unicode string (N'ZZZZKZ999999999999')

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • govind.sharma

    SSC Journeyman

    Points: 91

    Problem is still remaining

    it think it should not worked because The Then Column Datatype is Char not NChar

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    govind.sharma (4/18/2008)


    Problem is still remaining

    it think it should not worked because The Then Column Datatype is Char not NChar

    That is the second part of the problem (sorry I missed that :)). You have two choices, either change the table or column collation for the target table to Japanese_CI_AS or change to nchar/nvarchar data type.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • govind.sharma

    SSC Journeyman

    Points: 91

    When I Change the DataType from CHar To Nchar, Problem is Solved, but I want to Know What is the Impact of Jap_CI_AS on The Database

    because it work fine with Second Database have Latin1_General_CI_AS.

    with Char DataType

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    govind.sharma (4/18/2008)


    When I Change the DataType from CHar To Nchar, Problem is Solved, but I want to Know What is the Impact of Jap_CI_AS on The Database

    because it work fine with Second Database have Latin1_General_CI_AS.

    with Char DataType

    It affects string comparison, sorting, and the how it is displayed to the user. When you change to nchar your strings will use twice as much space (nchar is using UCS2 encoding, so it will use two bytes per character, instead of one like char datatypes). You can read more on http://msdn2.microsoft.com/en-us/library/ms187582.aspx

    When changing the collation, problems usually arise when you have indexes on these columns, and you do ordering on them using another collation.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply