0

In the SQL Docs (xp_cmdshell (Transact-SQL) - Result Sets) it mentions that xp_cmdshell should output as an nvarchar(255) column

Result Sets

Executing the following xp_cmdshell statement returns a directory listing of the current directory.

EXEC xp_cmdshell 'dir *.exe';  
GO

The rows are returned in an nvarchar(255) column. If the no_output option is used, only the following will be returned:

The command(s) completed successfully.

However, when using xp_cmdshell (On SQL Server 2019) it seems to be returning the data as varchar. For example, the below script has a unicode character (Latin capital O) being inserted into a temp table of column nvarchar and returned directly from xp_cmdshell:

Create Table #Temp ([nvarchar] NVARCHAR(100))
Insert into #Temp Values (N'Ȭ')
Select * from #Temp

Outputs 'Ȭ'

exec xp_cmdshell N'echo Ȭ'

Outputs '?'

I'd like xp_cmdshell to handle unicode characters ideally, but don't understand why this is different from the documentation. I did check and command prompt is able to display this character when run normally

enter image description here

Thom A
  • 88,727
  • 11
  • 45
  • 75
Kevin Pione
  • 299
  • 3
  • 12
  • Please don't post images of the documentation; use quote blocks. – Thom A Feb 03 '21 at 09:51
  • 4
    Unfortunately the column type being `NVARCHAR(255)` is not a promise that the input and output will be correctly encoded. You can prove the type is indeed `NVARCHAR(255)`: `create table #x(c sql_variant); insert #x exec (N'exec xp_cmdshell N''echo Ȭ'''); select sql_variant_property(c, 'BaseType'), sql_variant_property(c, 'MaxLength') from #x`. Of course that doesn't solve your problem, but I'm not sure that even has a solution (`cmd` has no support for the UTF-16 used by SQL Server and I have no idea how `xp_cmdshell` encodes things). Consider not using `xp_cmdshell`, that's my go-to solution. – Jeroen Mostert Feb 03 '21 at 10:02
  • Ideally you wouldn't use a feature that is not enabled by default nor would you use tsql to execute logic that is outside the database engine. This sort of kludgey coding is fraught with issues of many types - permissions just one of them. – SMor Feb 03 '21 at 12:50

0 Answers0