SQL Server and custom R: question marks added to output

Question

After R update according to tutorial https://learn.microsoft.com/en-us/sql/machine-learning/install/custom-runtime-r?view=sql-server-ver15&pivots=platform-windows
Query:

EXEC sp_execute_external_script
@language =N'myR',
@script=N'
print("Hello RExtension!");'

Output:
STDOUT message(s) from external script:
[1] " ��Hello RExtension! ��"

Completion time: 2021-09-14T09:05:00.2416329+02:00

It seems to be encoding problem. Does anyone have tips on solving? Thank you!

Answer

Hi @Olga Larina ,

You can try this setting:
https://joehanna.com/sql-server/changing-the-default-encoding-of-sql-files-in-ssms/

Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

Here is a small update. Together with a friend I dug into this a little more and I was able to analyse what is being sent. Consider this script:

   EXEC sp_execute_external_script  
          @language =N'myR',  
          @script=N'  
          x <- "räksmörgås"  
          print(x)  
          print(Encoding(x))'

("räksmörgås" is Swedish for "shrimp sandwich".)

SSMS prints this:

   STDOUT message(s) from external script:   
   [1] " ��rÃ¤ksmÃ¶rgÃ¥s ��"  
   [1] " ��latin1 ��"

The Swedish word has been encoded as UTF-8, but the string is then interpreted as Latin-1.

I wrote a small Perl script to run the batch above. This permitted me to capture the actual output and then analyse the bytes. For the second line, I got these bytes for the first couple of characters:

   5b 31 5d 20 22 02 fd fd 72 c3 92 c2 a4 6b   
    [  1  ] SP  "     ý  ý  r  Ã  ƒ  Â  ¤  k

So it seems that this is UTF-8 encoded string that has been interpreted as Latin-1 and then been re-encoded into UTF-8 a second time. SSMS expects the string to be UTF-8, so it displays only one layer of UTF-8 conversion. However, the sequence fd-fd is not legal UTF-8, and therefore SSMS displays the rhombus with the question mark. The character is known as REPLACEMENT CHARACTER, and tells is that there is an encoding error.

The full sequence of mysterious characters is 02-fd-fd, but why it appears here, I don't know.

So this does not solve the problem, but at least it gives an idea of what is going on.

SQL Server and custom R: question marks added to output

2 answers