locked
Searching Japanese text does not work RRS feed

  • Question

  • I have a user table that stores names in English and Japanese. The web application layers use the following technologies

    ASP.Net <----> EF 4 <----> SQL Server 2008

    EF4 does not to return any data when searching for Japanese names. Stored procedures are being employed for data retrieval operations. All the stored procedure parameters are of nvarchar type and the data is stored in the tables as nvarchar type as well.

    Thursday, June 7, 2012 11:25 PM

Answers

  • There could be a flaw inside the edmx definition about nvarchar and varchar.

    How we can verify is, firstly make sure your stored procedure in SQL is accepting input parameter as nvarchar.

    Then try to remove all the function definition and stored procedure from edmx and update edmx again to include the stored procedure and function.

    Or you know what you are doing mode....

    Right click edmx >> Open with >> choose XML editor, then look for stored procedure name in function tag and check the parameter type there.

    <Function Name="Your SP Name" ... ... ... >
    <Parameter Name="Para Name" Type="nvarchar" Mode="In" />
    </Function>

    • Marked as answer by CavemansIgloo Tuesday, June 12, 2012 6:11 AM
    Saturday, June 9, 2012 9:54 AM

All replies

  • Have you tested running these stored procedures with the correct parameter directly in SSMS first? Does it work there?

    Have you captured the exact command sent by your application to SQL Server using SQL Server Profiler?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 7, 2012 11:34 PM
  • Stored procedures work fine in SSMS

    exec sp_search_user N'Japanese text', N'Japanese Text'

    <<<< Have you captured the exact command sent by your application to SQL Server using SQL Server Profiler? >>>>

    I will try this next.

    Thanks,

    Thursday, June 7, 2012 11:38 PM
  • I suspect that you're calling the parameter as varchar (not nvarchar) from your application. Check if N' is present in the command.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 7, 2012 11:41 PM
  • Yes you can capture the  exact command sent by your application to SQL Server using SQL Server Profiler


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you

    Friday, June 8, 2012 12:26 AM
  • Couple of things to make sure that non-english text are safe with MSSQL

    • column datatype as NVARCHAR
    • insert into database with proper unicode entry [the insert ASP.Net page should use UTF-8 encoding]
    • use N' with query text

    Thanks,

    Friday, June 8, 2012 2:43 AM
  • Here is what I got from SQL Profiler:

    exec sp_search_user '??', '??'

    the Japanese text got replaced with ??

    Adding the following @ Page directive had no impact on the outcome:

    <%@ Page RequestEncoding="utf-8" ResponseEncoding="utf-8" %>
    
    Friday, June 8, 2012 3:52 PM
  • You need to dig deeper in the code now and find out why it's not using named parameters, for example. BTW, sp_search is a bad name for a stored procedure as sp_ prefix is reserved for the system stored procedures. spSearch is a better name.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, June 8, 2012 4:03 PM
  • The SQL that I have posted above is generated by EF4. The stored procedure is called from a function import.

    <<< BTW, sp_search is a bad name for a stored procedure >>>

    Good observation but I have only used "sp_search_user" as an example to illustrate the problem :)

    Friday, June 8, 2012 4:08 PM
  • I see - well, that generated code is no good. You may ask this question in the Entity Framework forum now as why such command was generated.

    If you do, please post a link here.

    Good luck in finding the root of the problem.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, June 8, 2012 5:42 PM
  • I am by no means a .net  guru, but does the application server have the language pack installed to understand the japanese character set? If it is working on the database instance, the problem has to be with the application.


    http://jahaines.blogspot.com/

    Friday, June 8, 2012 6:22 PM
  • According to this article a ? is used when the correct code page is not used. http://support.microsoft.com/kb/893663

    Codepage conversion issues


    When a character is replaced by a question mark (?) character,   this is an indication that a codepage conversion issue, has occurred. The   question mark (?) is a default character for the codepage conversion and   basically means that the operating system does not know how to handle the   character value and convert it. It replaces the character value with a question   mark (?). This could mean that the character has an invalid value for the   codepage or that the codepage that is needed for the conversion is not   installed.

    http://jahaines.blogspot.com/

    Friday, June 8, 2012 6:25 PM
  • I have also posted this question in the ADO.Net & EF forum:

    http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/373d16c8-d8ec-4065-bebf-f595a66ded04

    I have verified that East Asian Language pack was indeed installed in the application server. The application is able to send the data intact to the DAL. The message is getting lost in transit between EF and the stored procedure call in the database.

    Friday, June 8, 2012 8:52 PM
  • Here is what I got from SQL Profiler:

    exec sp_search_user '??', '??'


    There should be an N before the character literals. Else they are varchar
    literals.

    This is apparently an EF problem. Good luck!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 8, 2012 10:18 PM
  • There could be a flaw inside the edmx definition about nvarchar and varchar.

    How we can verify is, firstly make sure your stored procedure in SQL is accepting input parameter as nvarchar.

    Then try to remove all the function definition and stored procedure from edmx and update edmx again to include the stored procedure and function.

    Or you know what you are doing mode....

    Right click edmx >> Open with >> choose XML editor, then look for stored procedure name in function tag and check the parameter type there.

    <Function Name="Your SP Name" ... ... ... >
    <Parameter Name="Para Name" Type="nvarchar" Mode="In" />
    </Function>

    • Marked as answer by CavemansIgloo Tuesday, June 12, 2012 6:11 AM
    Saturday, June 9, 2012 9:54 AM
  • Your other thread, you show calls to functions.  Keep digging, deeper and deeper until there are no more functions to show, and when you get there, you'll see the actual call to the proc.  When you get there, there won't be the N in front of the parameters.
    Saturday, June 9, 2012 10:08 AM
  • Thanks tinmgaye,

    It worked ! I have updated the model and the function import but actually removing the SP and re-adding it did the trick.

    Tuesday, June 12, 2012 6:11 AM