Searching Japanese text does not work

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.

Answer 1

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

Answer 2

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,

Answer 3

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

Answer 4

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

Answer 5

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,

Answer 6

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

Answer 7

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

Answer 8

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 :)

Answer 9

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

Answer 10

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/

Answer 11

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/

Answer 12

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.

Answer 13

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, [e-mail]

Answer 14

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>

Answer 15

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.

Answer 16

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.