'How to get correct encoding on R on SQL Server when executing external script in SSMS?

We have installed R 4.1 on one server and it produces gibberish when running external script through SSMS (SQL Server Management Studio). We used this guide when installing: Install an R custom runtime for SQL Server

EXEC sp_execute_external_script
    @language =N'myR',
    @script=N'
print(R.version);'

STDOUT message(s) from external script: ��_��
��platform�� ��x86_64-w64-mingw32��
��arch�� ��x86_64��
��os�� ��mingw32��
��system�� ��x86_64, mingw32��
��status�� ����
��major�� ��4��
��minor�� ��1.0��
��year�� ��2021��
��month�� ��05��
��day�� ��18��
��svn rev�� ��80317��
��language�� ��R��
��version.string�� ��R version 4.1.0 (2021-05-18)�� ��nickname�� ��Camp Pontanezen��

If I run something returning a resultset in a grid, everything seem to be fine.

EXEC sp_execute_external_script @language = N'myR'
    , @script = N'
OutputDataSet <- data.frame(installed.packages()[,c("Package", "Version", "Depends", "License", "LibPath")]);'
WITH result sets((
            Package NVARCHAR(255)
            , Version NVARCHAR(100)
            , Depends NVARCHAR(4000)
            , License NVARCHAR(1000)
            , LibPath NVARCHAR(2000)
            ));

SSMS Resultset is ok

If I run same script in RGui.exe, it's also fine

print(R.version); _
platform x86_64-w64-mingw32
arch x86_64
os mingw32
system x86_64, mingw32
status
major 4
minor 1.0
year 2021
month 05
day 18
svn rev 80317
language R
version.string R version 4.1.0 (2021-05-18) nickname Camp Pontanezen

An easier sample:

EXEC sp_execute_external_script
    @language =N'myR',
    @script=N'
print("ÅÄÖ");'

Gives:

"��ÅÄÖ��"

We're using the same SSMS towards another R Server, with bundled version of R (2.x or something) then it displays correctly.

Encoding on the server seem to be correct, but not the resulting output when it's run in SSMS and result as a "string". How can one correct this?

Sys.getenv() (related to R) on the machine not working:

R_ARCH /x64 R_ARCH_BIN /x64 R_BROWSER
R_BZIPCMD bzip2 R_CMD R CMD R_COMPILED_BY gcc 8.3.0 R_DEFAULT_PACKAGES
datasets,utils,grDevices,graphics,stats R_DOC_DIR
D:/RCompile/recent/R-4.0.3/doc R_GAMS_SYSDIR
d:/RCompile/CRANpkg/extralibs215/GAMS/win64 R_GC_GROWINCRFRAC
0.2 R_GSCMD C:/Progra~2/gs/gs9.21/bin/gswin32c.exe R_GZIPCMD gzip R_HOME C:/Program Files/R/R-4.1.0 R_INCLUDE_DIR
D:/RCompile/recent/R-4.0.3/include R_INSTALL_PKG RInside R_INSTALL_TAR tar.exe R_LIBS_USER
C:/Users/CRAN/Documents/R/win-library/4.0 R_MAX_NUM_DLLS 153 R_OSTYPE windows R_PACKAGE_NAME RInside R_PAPERSIZE a4 R_PAPERSIZE_USER a4 R_PARALLEL_PORT random R_RD4PDF times,inconsolata,hyper R_SCRIPT_LEGACY yes R_SESSION_TMPDIR
C:/WINDOWS/ServiceProfiles/MSSQLLaunchpad$MABI_SQLSERVER/AppData/Local/Packages/38af79a5ed4e7cad1e6ad6e9e57a562d-appcontainer1/AC/Temp R_SHARE_DIR D:/RCompile/recent/R-4.0.3/share R_UNZIPCMD
unzip R_USER C:/Users/CRAN/Documents R_VERSION
4.0.3 R_ZIPCMD zip

Sys.getenv() (related to R) on the machine working:

R_ARCH /x64 R_COMPILED_BY gcc 4.9.3 R_HOME D:/ProgramData/INSTANS01/R R_LIBS_USER
D:\ProgramData\INSTANS01\Temp-R\Appcontainer1\FF5697C0-8563-40AE-85B0-3DDE0B6C59C4/R/win-library/3.5 R_USER
D:\ProgramData\INSTANS01\Temp-R\Appcontainer1\FF5697C0-8563-40AE-85B0-3DDE0B6C59C4 R_ZIPCMD
D:/ProgramData/INSTANS01/R/library/RevoScaleR/utils/infoZip/zip.exe

rencodingsql-server-2019


Solution 1:[1]

SMSS has a default encoding of UTF-16. You should be able to change the default encoding in R to that or this article details how to change it within SSMS. I'm not sure if this will work but at this point, it can't hurt to try.

Best of luck

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 l_drak

Related Questions