Sql-server – sp_executesql changes non standard characters to

dynamic-sqlsql-server-2012unicode

Am trying to use sp_executesql to run commands but I've come across an issue where in non-english characters (aka Chinese & Japanese) are changed to ??? when ran.

sql server 2008R2 & 2012

Example Code

DECLARE @text nvarchar(30) = N'select ''J こんにちは C 你好''' 
SELECT @text 
SELECT N'J こんにちは C 你好'
exec sp_executesql @text
exec sp_executesql  N'select ''J こんにちは C 你好'''

J= Japanese 'Hi' C = Chinese 'Hi' as processed by google translate for testing purposes

Output

------------------------------
select 'J こんにちは C 你好'

------------
J こんにちは C 你好

------------
J ????? C ??

------------
J ????? C ??

As you can see when ran naturally or the code is selected the output is returned as expected, however once it is passed through sp_executesql it returns as ? characters. Its worth noting if you attempt to send a varchar instead of an nvarchar it errors out so it defiantly uses unicode.

Have tried a few different collations for the input but that doesn't seem to change anything

Best Answer

You should pass your unicode characters within SELECT as UNICODE using N:

DECLARE @text nvarchar(30) = N'select N''J こんにちは C 你好''' 
SELECT @text 
SELECT N'J こんにちは C 你好'
exec sp_executesql @text
exec sp_executesql  N'select N''J こんにちは C 你好'''
Related Question