Case sensitive R in sp_execute_external_script

R Language is case sensitive! Any R-coder can tell you this and share the experience. Case sensitive in R can be a challenge as with any case sensitive language.

This also applies to SP_EXECUTE_EXTERNAL_SCRIPT stored procedure in T-SQL. This is a simple demo that will support this. This script will return normal result.

EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'OutputDataSet<-c1'
      ,@input_data_1 = N'SELECT 1234 as number'
      ,@input_data_1_name = N'c1'

 

2016-06-21 21_33_05-SQL_Queries.sql - SICN-00031_SQLSERVER2016RC3.WideWorldImporters (SPAR_si0101798

With slight modification of parameter @input_data_1_name I will change the name from c1 (small caps) to C1 (all caps).

-- modification of @input_data_1_name  from "c1" (small caps) to "C1" ( all caps)
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'OutputDataSet<-c1'
      ,@input_data_1 = N'SELECT 1234 as cifra'
      ,@input_data_1_name = N'C1'

It can be seen the difference of name of input data set (C1) with parameter @input_data_1_name and the dataset introduced into R with @script parameter.

Capture

In this case R output result shown in Message window of SSMS is at least informative that it will tell you: object ‘c1’ not found. It might be that some other time the error message will not be that straightforward.

Despite relative “oh, yes. I know this error” I am posting this, as I have found myself numerous times forgetting the fact, R is case sensitive. In opposite to @script or @input_data_1_name parameters, all other parameters (@input_data_1, @params, @output_data,..) are not case sensitive as the not go through R engine.

So spear yourself minutes of searching for errors in R script by keeping in mind that all code passed through @script or @input_data_1_name are case sensitive!

Unfortunately SSMS does not have debugger for R code (yet!), so you can always copy/paste the R code into RTVS or into RStudio (or any other) and validate and debug your R code.

Happy R-SQLing!