Tag Archives: R

ByHariharan Rajendran

First R Script in SQL Server 2016

Follow my previous article to integrate R in SQL Server 2016, Once done then check the R script in SQL Server to make sure integration is done successfully.

Run the below script in SQL Server

CREATE TABLE Sample ([Value] int not null) ON [PRIMARY]

 INSERT INTO Sample   Values (101);

 INSERT INTO Sample   Values (102);

 INSERT INTO Sample   Values (103) ;

GO

execute sp_execute_external_script

  @language = N’R’

, @script = N’ OutputDataSet <- InputDataSet;’

, @input_data_1 = N’ SELECT *  FROM Sample;’

WITH RESULT SETS (([Value_R] int NOT NULL));

GO

When I run the script, I got below error.

“Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime. STDERR message(s) from external script:  Fatal error: cannot create ‘R_TempDir’”

To avoid this type of error, check the working directory on Rlauncher.config file

In my case, I have installed in E drive “E:\Local_Install_Applications\SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn”

The blue highlighted path will be different if you have installed SQL Server on a different path.

Open the Rlauncher.config file,

image

make sure your working directory is correct without any extra space and also check the permission for “ExtensibilityData” directory.

image

Provide similar permission level as above.

Now try to run the same script in SSMS,

image

ByHariharan Rajendran

Integrate R in SQL Server CTP 3

It is very easy to integrate R services with SQL Server 2016.

First, make sure you have selected the “Advanced Analytics Extensions” while installing SQL Server.

image

Next, download and install below two R components

1. Revolution R Enterprise 7.5

2.  Revolution R Open 3.2.2 for Revolution R Enterprise 7.5

Once the above components are installed then enable the external scripts in SQL Server Management Studio.

exec sp_configure 'external scripts enabled', 1; 
reconfigure;

 

The next step is run the post installation script in command prompt, Locate the registerRext.exe file in your system.

Mostly the file will be reside in following location, if you have provided the default installation path, “C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64”

Open a command prompt with admin privilege and go to above directory,

 

Step 1: Type below command in command prompt

cd  C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64

 

Step 2:

registerRext.exe  /install     (If your SQL Server is default instance, if your SQL Server is named instance then use the below code)

 

Step 3 (Optional): 

registerRext.exe” /install /instance<SQLNamedInstance>

12