CODEWITHSUNDEEP
sql-servert-sqlunicodecollation
Mustafa aga
Mustafa aga

Reputation: 31

Stored procedure Inserts Hebrew characters into an NVARCHAR column, but SELECT shows "?"

When I SELECT from the table, the data that I stored is stored as question marks.

@word is a parameter in my stored procedure, and the value comes from the C# code:

string word = this.Request.Form["word"].ToString();

cmd.Parameters.Add("@word", System.Data.SqlDbType.NVarChar).Value = word;

My stored procedure is like this:

CREATE PROCEDURE ....
(
   @word nvarchar(500)
   ...
)

Insert into rub_translate (language_id,name)
values (8 ,@word COLLATE HEBREW_CI_AS )

My database, and the column, is using the SQL_Latin1_General_CP1_CI_AS collation and I cannot change them.

Can anybody give me a solution how can I solve this problem just by modifying the column or the table?

Upvotes: 2

Views: 3250

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

In order for this to work you need to do the following:

  1. Declare the input parameter in the app code as NVARCHAR (you have done this)

  2. Declare the input parameter in the stored procedure as NVARCHAR (no code is shown for this)

  3. Insert or Update a column in a table that is defined as NVARCHAR (you claim that this is the case)

When using NVARCHAR it does not matter what the default Collation of the Database is. And actually, when using NVARCHAR, it won't matter what the Collation of the column in the table is, at least not for properly inserting the characters.

Also, specifying the COLLATE keyword in the INSERT statement is unnecessary and wouldn't help anyway. If you have the stored procedure input parameter defined as VARCHAR, then the characters are already converted to ? upon coming into the stored procedure. And if the column is actually defined as VARCHAR (you haven't provided the table's DDL) then if the Collation isn't Hebrew_* then there is nothing you can do (besides change either the datatype to NVARCHAR or the Collation to a Hebrew_ one).

If those three items listed at the top are definitely in place, then the last thing to check is the input value itself. Since this is a web app, it is possible that the encoding of the page itself is not set correctly. You need to set a break point just at the cmd.Parameters.Add("@word", System.Data.SqlDbType.NVarChar).Value = word; line and confirm that the value held in the word variable contains Hebrew characters instead of ?s.

ALSO: you should never create a string parameter without specifying the max length/size. The default is 30 (in this case, sometimes it's 1), yet the parameter in the stored procedure is defined as NVARCHAR(500). This could result in silent truncation ("silent" meaning that it will not cause an error, it will just truncate the value). Instead, you should always specify the size. For example:

cmd.Parameters.Add("@word", System.Data.SqlDbType.NVarChar, 500).Value = word;

Upvotes: 2

Erwin Dockx
Erwin Dockx

Reputation: 283

You could just insert it as-is, since it's unicode and then select it with a proper collation:

    declare @test table([name] nvarchar(500) collate Latin1_General_CI_AS);

    declare @word nvarchar(500) = N'זה טקסט.';

    insert into @test ( [name] ) values  ( @word );

    select [t].[name] collate Hebrew_CI_AS from @test as [t]

Or you can change the collation of that one column in the table all together. But remember that there is a drawback of having a different collation from your database in one or more columns: you will need to add the collate statement to queries when you need to compare data between different collations.

Upvotes: 3

Related Questions