Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Globalization » Character Conversion
Add to briefcase

Character Conversion Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
govind.sharma
Posted Friday, April 18, 2008 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 26, 2009 3:51 AM
Points: 7, Visits: 33
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', 'ZZZZＫZ999999999999', '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
Post #487100
Andras Belokosztolszki
Posted Friday, April 18, 2008 6:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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'ZZZZＫZ999999999999', '9993', '93')

Note the N prefix before the unicode string (N'ZZZZＫZ999999999999')

Regards,
Andras


Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #487106
govind.sharma
Posted Friday, April 18, 2008 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 26, 2009 3:51 AM
Points: 7, Visits: 33
Problem is still remaining
it think it should not worked because The Then Column Datatype is Char not NChar
Post #487114
Andras Belokosztolszki
Posted Friday, April 18, 2008 6:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #487121
govind.sharma
Posted Friday, April 18, 2008 6:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 26, 2009 3:51 AM
Points: 7, Visits: 33
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
Post #487127
Andras Belokosztolszki
Posted Friday, April 18, 2008 6:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #487138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.