Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

Installing R Packages for SQL Server Machine Learning Services

SQL Server - sp_execute_external_script - R

I've had some issues installing R packages for SQL Server. There have been a scant few packages I've installed here and there. I muddled through each one, never remembering the steps I took the previous time because I never documented anything. No longer. Here I'll show what has worked for me.

It always starts with an error similar to this when trying to run the sp_execute_external_script procedure in SSMS. It's overly verbose, but with patience, you'll see the problem:


Msg 39004, Level 16, State 20, Line 0
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
During startup - Warning message:
In setJsonDatabasePath(system.file("extdata/capabilities.json", :
bytecode version mismatch; using eval
Error in library(generator) : there is no package called 'generator'
Calls: source -> withVisible -> eval -> eval -> library

Error in execution. Check the output for more information.
Error in eval(expr, envir, enclos) :
Error in execution. Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted


install.packages()

Installing an R package is normally just a one-liner. Here's what it looks like with RStudio:


> install.packages("generator", quiet = TRUE)
package ‘generator’ successfully unpacked and MD5 sums checked

If we try running install.packages() in the SQL Server database engine via sp_execute_external_script, we get an error:


EXEC sp_execute_external_script
@language = N'R',
@script = N'install.packages("generator", quiet = TRUE)'

Msg 39004, Level 16, State 20, Line 0
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
During startup - Warning message:
In setJsonDatabasePath(system.file("extdata/capabilities.json", :
bytecode version mismatch; using eval
Warning in install.packages("generator", quiet = TRUE) :
'lib = "C:/Program Files/Microsoft SQL Server/MSSQL14.SQL2017/R_SERVICES/library"' is not writable
Error in install.packages("generator", quiet = TRUE) :
unable to install packages
Calls: source -> withVisible -> eval -> eval -> install.packages

Error in execution. Check the output for more information.
Error in eval(expr, envir, enclos) :
Error in execution. Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted


R.exe

To install a package, the R.exe utility has been successful for me so far. It's located in this path, that is specific to the version of SQL Server installed and whether it's a default or named instance:
%programfiles%\Microsoft SQL Server\MSSQLnn.Instance\R_SERVICES\bin

Navigate to that path in Windows Explorer, right-click R.exe, and "Run as administrator". This opens a command line interface. I prefer to clear the console first, so I type in and enter shell("cls"). Next, type in install.packages(), the name of the package to install (plus any other desired parameters), and hit enter. Here, I'll specify the "generator" package along with the "lib" parameter, which specifies the directory where the package is to be installed. Note that this path is specific to my instance of SQL Server 2017, which is a named instance ("SQL2017"):


install.packages("generator", lib = "C:/Program Files/Microsoft SQL Server/MSSQL14.SQL2017/R_SERVICES/library")

When typing in the R.exe console, for me the command gets abbreviated--notice the $ character to the far left as the command is entered, and again to the far right after hitting the ENTER key:

SQL Server - R.exe

Now back to SSMS. When I try running my R code again, the "there is no package" error is gone. If there are no other errors, it should work now:


EXEC sp_execute_external_script
@language = N'R',
@script = N'
library(generator)
OutputDataSet <- as.data.frame(r_latitudes(10))
'
WITH RESULT SETS ((
latitudes VARCHAR(255)
));

sp_execute_external_script SSMS

Comments

Comment by Bob

Yes this is the one reliable way to install packages on the SQL R services. It has to be done through the R.exe with admin privileges and the location has to be that specific library folder. Anything else results in a error. You can check if it installed with: EXEC sp_execute_external_script @language = N'R', @script = N'OutputDataSet <- as.data.frame( installed.packages() );';

Bob
Post comment