NCHAR datatype with Microsoft Query in Excel

[Former Member]

Hi, all.

I have a problem in retrieving the NCHAR column data in my HANA table into Microsoft Excel 2007 by using Microsoft Query.

The following error message is shown when I click 'Finish' in the Query Wizard.

General error; -10427 Conversion of parameter (2) from data type NCHAR to ASCII failed

I don't have any problem in retrieving English string from CHAR column.

I'm using NCHAR datatype because I heard non-ASCII (non-ISO8859-1?) characters must be stored in NCHAR.

Thank you very much,

-mamoru

[tomas-krojzl]

Hello,

I just tried (but using Microsoft Excel 2003 SP3) and replicated the same issue...

Step sequence can be found in SAP HANA Master guide

https://service.sap.com/~sapidb/011000358700000604552011

It looks that problem is there only when you are having non-English characters in the table...

Another interesting fact is that if you add special characters AFTER query definition and click refresh then no error is raised.

It looks that it is Microsoft Query EDITOR that is having issue with data conversion but not Microsoft Excel itself nor Microsoft Query (data manipulation) functionality.

Here is functional fix:

1.) When you are specifying data source (page 38 first image) - deselect option to use query wizard

2.) Enter user/password to connect Microsoft Query into SAP HANA database

3.) Close the add table dialog

4.) In menu Records disable option Automatic query

Note: This option is needed if you will be editing the query using Microsoft Query editor

5.) In menu Edit choose item Options and deselect option Validate queries

Note: This option is needed always in case that table contains special characters

Now you can close the Microsoft Query editor and repeat the query creation using the wizard - checks are deactivated and will no longer raise error.

I do not think that having checks deactivated is best solution and therefore I would suggest to open OSS message for SAP. They will probably answer to wait for HANA SP03 to see if the problem will be solved but I guess that if noone reported this issue before then it will not be solved.

Hope it helped.

Tomas

Edited by: Tomas Krojzl on Oct 27, 2011 10:55 AM

[Former Member]

Thank you very much!

I could get Japanese strings.