June 21, 2011 at 10:18 pm
Hi All,
We have a table that pulls data from a non SQL Source.
The data comprises of different character sets ranging from Chinese, Japanese and Latin.
The table is pulling Chinese characters for few rows in TAB4 column, which in of NVARCHAR datatype.
We found that we are getting junk characters for some of the rows in Chinese but many other rows with Chinese characters are pulled in successfully.
Even when inserting data in the table manually without 'N" as prefix to the data, the data inserted is junk character. (It is not possible to use N as prefix as the data is inserted using data manager)
The script for the table is as follows:
CREATE TABLE [TBL_DATA] (
[TAB1] [numeric](8, 0) NULL ,
[TAB2] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[TAB3] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[TAB4] [nvarchar] (60) COLLATE Latin1_General_CI_AS NULL ,
[TAB5] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[TAB6] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[TAB7] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[TAB8] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TAB9] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TAB10] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TAB11] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TAB12] [varchar] (3) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Can any body please help me?
June 22, 2011 at 6:38 am
Knowledge Hunter (6/21/2011)
Even when inserting data in the table manually without 'N" as prefix to the data, the data inserted is junk character. (It is not possible to use N as prefix as the data is inserted using data manager)
How do you know it's junk? It may be a display issue or the "data manager" may have munged your data.
What do you get from this query?
SELECT UNICODE(SUBSTRING(TAB4,<junk character start position>,1))
FROM TBL_DATA
WHERE TAB1 = <id to get to proper row>
Can you post an INSERT with the data in question so we can test it on our side?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2011 at 9:25 am
Thanks for you help.
The rows are returning ASCI values when I am executing normal Select query.
Where as the data in source database is in Chinese
I will check the given query tomorrow and let you.
June 23, 2011 at 7:40 am
This is what I am inserting using the following query:
INSERT INTO Test..Test_table VALUES (75968,'???F')
and this is the output when I use a select query: ???F
table script is as follows:
CREATE TABLE [dbo].[Test_table](
[Mat_id] [int] NULL,
[Mat_desc] [nvarchar](60) NULL
) ON [PRIMARY]
June 23, 2011 at 9:28 am
Knowledge Hunter (6/23/2011)
This is what I am inserting using the following query:INSERT INTO Test..Test_table VALUES (75968,'???F')
and this is the output when I use a select query: ???F
table script is as follows:
CREATE TABLE [dbo].[Test_table](
[Mat_id] [int] NULL,
[Mat_desc] [nvarchar](60) NULL
) ON [PRIMARY]
When it comes to SQL Server there is a difference between this:
INSERT INTO Test_table VALUES (75968, '???F');
and this:
INSERT INTO Test_table VALUES (75968, N'???F');
Run the second insert into your test table and then run this SELECT query to see what I mean:
SELECT Mat_desc,
UNICODE(SUBSTRING(Mat_desc, 1, 1)) AS Mat_desc_pos1,
UNICODE(N'?') AS pos1,
UNICODE(SUBSTRING(Mat_desc, 2, 1)) AS Mat_desc_pos2,
UNICODE(N'?') AS pos2,
UNICODE(SUBSTRING(Mat_desc, 3, 1)) AS Mat_desc_pos3,
UNICODE(N'?') AS pos3,
UNICODE(SUBSTRING(Mat_desc, 4, 1)) AS Mat_desc_pos4,
UNICODE(N'F') AS pos4
FROM dbo.Test_table ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 23, 2011 at 10:21 am
The data is inserted using Data Manager.
Hence N cannot be added as prefix for the chinese data entered in the batch.
When we are using the query you have provided it is inserting the data properly.
Is there any way we can get the data in SQL?
June 23, 2011 at 10:39 am
Knowledge Hunter (6/23/2011)
The data is inserted using Data Manager.Hence N cannot be added as prefix for the chinese data entered in the batch.
When we are using the query you have provided it is inserting the data properly.
Is there any way we can get the data in SQL?
Is Data Manager a piece of commercial software? Google did not turn up anything definitive. Sorry, I am not familiar with that specific name.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 23, 2011 at 11:30 am
It is an ETL Tool that pulls data from JDE to SQL database.
June 23, 2011 at 11:33 am
I see. It appears that Data Manager is the problem. Unless Data Manager can be configured to generate those insert statements with the N in front of the string literal you will continue to lose data when transferring.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 23, 2011 at 11:42 am
That is what I was suspecting.
Thanks a lot for your help.
June 23, 2011 at 11:48 am
You're very welcome 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply