How do I return a Unicode value in this SQL example?

Question

I need to return Russian text from a SQL Server 2005 database table.

In the following example which is a simple way of describing my dilemma, the @Test variable will print out question marks:

DECLARE @Test nvarchar(max)
SET @Test = 'Баннер'
PRINT @Test

(Note that the @Test value is Russian text, for those who don't have the font installed.)

But if I change the code to the following, the @Test variable will print out the text as intended:

DECLARE @Test nvarchar(max)
SET @Test = N'Баннер'
PRINT @Test

Here is what I want to know:

In my real-world example I am doing the following with a stored proc:

EXEC usp_GetContent @Content = @Test

The value for @Test is in Russian, but gets displayed as question marks. If the proc looked like this, the Russian comes through fine:

EXEC usp_GetContent @Content = N'Баннер'

But this is not a possibility for me; I need to pass in a variable.

Any advice?

Thanks.

Answer 1

There are 2 possible reasons you're getting question marks. Either when you're setting the variable before the stored proc call you're not putting an 'N'. Or when you're printing the output the resultant font doesn't support the double byte characters.

Answer 2

As long as the stored procedure parameter is defined as unicode such as nvarchar, and the calling code explicitly defines the parameter object of the command with the same type it should work fine.

When you have written it in SQL you have passed in a varchar in your first example, so it has not understood it.

The issue is the line:

SET @Test = 'Баннер'

Not the stored procedure.

Answer 3

This query:

SELECT  COLLATIONPROPERTY(CAST(SERVERPROPERTY('Collation') AS NVARCHAR), 'CodePage')

will return your server's default code page which most probably will differ from 1251 or 866.

When you use this constant:

'Баннер'

, the server will try to convert Russian symbols it gets over the connection into something from your codepage.

But your codepage 437 (or whatever you have) does not allow for this conversion, that's why the characters get replaced with the question marks.