Getting Chinese characters out of MS SQL

[Steve Workings]

Anyone outside of China have any success at getting Chinese characters out of MS SQL?

I'm trying to write Xbasic routines or, do something simple such as set up a report data source. And, the data is in field type nvarchar(). And, yes, I've tried a UTF8 SQL connection property.

Selwyn says it's because the server doesn't have the correct code page. But I've added a couple Chinese languages and still no dice (did not re-start the SQL server -- might it be as simple as that?)

An Xbasic retrieval just gets lots of garbage like this: 外场称重并

A report data source definition just returns lots of question marks like ????????

Any successful experience welcomed here.

[lgrupido]

Re: Getting Chinese characters out of MS SQL

I don't have any experience with this - but here are my suggestions:

How did the Chinese characters get into the data? You'll want to use the same language code, I would think.

Can you see the data properly in MS SQL management studio? If you can't see it in a query window there, you will never see it in Alpha.

Also, it could be a FONT issue. If your computer can't display the proper font to render the characters, it's going to substitute.

[Steve Workings]

Re: Getting Chinese characters out of MS SQL

How did the Chinese characters get into the data? You'll want to use the same language code, I would think.

Typed by users, stored in NVarchar(max) fields.

Can you see the data properly in MS SQL management studio?

Yes

Also, it could be a FONT issue. If your computer can't display the proper font to render the characters, it's going to substitute.

Selwyn suggested the server (in Singapore) doesn't have correct code pages. See my original message here - I've added what I believe to be the correct ones, still no joy.

[lgrupido]

Re: Getting Chinese characters out of MS SQL

Have you tried just showing the data in a grid?

I realize that is not the end result you want, but being able to see the data in a grid (or not) might provide some clues.

Also, do you have a local copy or are you doing all the work on the server?

I wonder if - in a grid - you might be able to see the data in working preview, whereas trying to see it in an a5w page (and thus in a browser) might be different.

It is certainly different than seeing it in Management studio.

Further: You could trying setting up an active table over the data. It would be interesting whether you can see the correct data in an Alpha Table view.

These different views might give you some insight into what is happening with the character sets.

Last thought: Take Alpha out of the equation. Can you view the data in Excel or Access?

[Steve Workings]

Re: Getting Chinese characters out of MS SQL

Have you tried just showing the data in a grid?

Yes, works fine.

Also, do you have a local copy or are you doing all the work on the server?

Working on the production server

I wonder if - in a grid - you might be able to see the data in working preview, whereas trying to see it in an a5w page (and thus in a browser) might be different.

This works. I have an "input" form and an "Edit" form for the particular instance I need first, and users can see and edit their data in the grid (obviously on the a5w page)

Haven't tried the other suggestions, will see if I can at least run out an Excel copy of a few records later today. But because I can get the Chinese characters with Xbasic, and with Grids (and UX), I sure don't understand why such problems getting it as a data source for reports.

[lgrupido]

Re: Getting Chinese characters out of MS SQL

Well, keep in mind, the reports are not actually running directly from the data. The reports are copied from the SQL tables into a local DB - then the report is run from the report db. If something is happening in translation ....

I'm not sure how - but there has to be a way to see what is actually in the local Alpha DB that the report creates.

[Steve Workings]

Re: Getting Chinese characters out of MS SQL

OK, chased that down. No luck. The temporary .dbf created for the report data also contains the same "???????????????" where I should see Chinese characters.

[Steve Workings]

Re: Getting Chinese characters out of MS SQL

Well, just tried your suggestion to export from Management Studio to Excel. The Wizard gives me this error referring to the pertinent field:

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

This lends further credence to Selwyn's assertion that the server probably doesn't have the correct code page (was there ever any doubt?) but so far -- see previous messages -- I haven't figured out how to add it.

I've added a couple what I believe to be the correct code pages. Is it necessary to re-start the SQL server to load 'em? I haven't because this is a 24-hour production machine.

[GGandhi]

Re: Getting Chinese characters out of MS SQL

i have no knowledge of the problem or any insight in solving, but this page seems interesting.
http://wiki.alphasoftware.com/~alpha...UTF-8+Data+V10
i liked the few sentences in the second paragraph so i am thinking this might be of use.
there seems to be a function converting the data to ascii format.
do you think there is any use for it?

[lgrupido]

Re: Getting Chinese characters out of MS SQL

The text was truncated error could just be that the fields are too big to fit into excel. That happens all the time. It may not have had anything to do with the code page. Try the export again without the longtext fields - just use fields under 255 characters.

I wasn't actually thinking of exporting from MSSQL to excel anyway. I was thinking you could point excel directly at the table using odbc.

But based on what you've said - I think something is happening when Alpha is copying the data from MS SQL into the report DB. Things are getting lost in translation.

I don't know if you have to restart a server when loading code pages, sorry. Even 24/7 servers go down occasionally for maintenance though - and this seems like maintenance to me!

[Steve Workings]

Re: Getting Chinese characters out of MS SQL

Gandhi - I'm using a names SQL connection that specifies with UFT-8 conversion (and, I've tried without of course too). But the notes you point to still aren't what I need to use SQL as a report's datasource when the datasource contains Chinese characters. Everything else I've resolved (so far) -- and it is this specific problem that leaves my newest client in Shanghai without full use of the 100 or so reports that they expect.

[DoronF]

Re: Getting Chinese characters out of MS SQL

Hi Steve,

Since you could not make it working try using MS SQL Report services which is quite easy. Create a simple report and see how the Chinese characters are coming out. Try this link: http://blog.sqlauthority.com/2013/07...report-wizard/

Regards,

Doron
The Farber Consulting Group, Inc.

Main Web Site: http://www.dFarber.com
MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
Convert Ms Access to Web
Custom Software Development
Alpha Five Development
No Interest Loans Application
MS SQL Remote DBA

[Steve Workings]

Re: Getting Chinese characters out of MS SQL

Thanks Doron, but I don't know how to be clearer: I have 100 EXISTING reports that I need to work with Chinese characters. All these other/new/alternative methods ya'll are so kindly suggesting don't address my problem. I really don't want to completely re-write all these reports (or the majority of them). It just seems to me that since so many other parts of Alpha are working just fine with the Chinese characters there ought to be a way to make EXISTING reports work as well.

[Steve Workings]

Re: Getting Chinese characters out of MS SQL

I have a bit of confidence that COLLATE can work, but I can't quite figure out how to use it.

For instance, this obviously works:

SELECT S_ID, FAC_ID, Animal_Name, Comments COLLATE Latin1_General_CI_AI as Comments_Chinese

But what would I use to COLLATE to Simplified Chinese, which I think uses code page 936?
https://technet.microsoft.com/en-us/...ql.105%29.aspx

UPDATE
Crap. You can do this to find the code pages in your SQL

SELECT [Name], [Description], [CodePage] = COLLATIONPROPERTY([Name], 'CodePage')
FROM ::fn_helpcollations()

This verifies for me that the particular COLLATE I want is there. So, this works in Management Studio:

SELECT S_ID, FAC_ID, Animal_Name, Comments COLLATE Chinese_Simplified_Pinyin_100_CI_AI as Comments_Chinese

But, sadly, returns the same ???????? in Alpha's report datasource query.

[DoronF]

Re: Getting Chinese characters out of MS SQL

Hi Steve,

Here is another one for you....

Simplified Chinese - Chinese_PRC_CI_AS
Traditional Chinese can be either: Chinese_Taiwan_Stroke_CI_AS ,
or Chinese_Hong_Kong_Stroke_90_CI_AS

Simplified Chinese collate:

CREATE TABLE [Steve] (
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FullName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Dep] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Remarks] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL)

Let me know

Regards,

Doron
The Farber Consulting Group, Inc.

Main Web Site: http://www.dFarber.com
MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
Convert Ms Access to Web
Custom Software Development
Alpha Five Development
No Interest Loans Application
MS SQL Remote DBA