I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
Installing R packages in SQL Server 2017 is a breeze with the stored procedure sp_execute_external_script. But so far, it is limited to installing one package at a time. If you have an R Package with dependencies, you have to install those in a separately.
Here’s an easy way to check for package dependencies using the package miniCRAN.
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64. Run the executable as an administrator (Right-Click & Select “Run as administrator”).
If you want to add the new package to the default library, you have to tell the installer exactly that by using the
lib argument. I previously installed R and its environment in my laptop. Without specifying a library, R would have installed the package in the old default library. See the list of my library paths below (.libPaths())
install.packages("miniCRAN", lib="C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\R_SERVICES\\library")
Then you should get a message like this one:
trying URL 'https://mran.microsoft.com/snapshot/2017-05-01/bin/windows/contrib/3.3/miniCRAN_0.2.7.zip' Content type 'application/zip' length 1025529 bytes (1001 KB) downloaded 1001 KB package ‘miniCRAN’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\Marlon.Ribunal\AppData\Local\Temp\Rtmp4UPMso\downloaded_packages
You can find all your library paths with the .libPaths() function.
> .libPaths()  "C:/Users/Marlon.Ribunal/Documents/R/win-library/3.3"  "C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library"
Then we can double check real quick if the package actually made it:
sp_execute_external_script @language = N'R', @script = N'OutputDataSet <- data.frame(installed.packages())';
> library("miniCRAN") > pkg <- "ggplot2" > pkgDep(pkg)
You may not need all the listed packages. Check the web page for the package you’re installing for the info.