0

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

3

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 你好'''
  • Of course I do... works like a charm, Thanks :) – Ste Bov Apr 11 '18 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.