Installing R packages in SSMS using T-SQL can take some time, especially when you need to switch between R Tools for Visual Studio or R Studio or your favorite R GUI tool (or R CMD). But so far, installing any additional library using sp_execute_external_script is just not working.
--InstallPackage using sp_execute_external_script EXECUTE sp_execute_external_script @language = N'R' ,@script=N'install.packages("ggplot2")' WITH RESULT SETS (( ResultSet VARCHAR(50))); -- using Download.file command EXECUTE sp_execute_external_script @language = N'R' ,@script=N'download.file("https://cran.r-project.org/bin/windows/contrib/ 3.4/ggplot2_2.1.0.zip","ggplot2") install.packages("ggplot2", repos = NULL, type = "source")' WITH RESULT SETS (( ResultSet VARCHAR(50)));
Both executions yield in error messages. Especially the first one would be great way of installation. At this point, I would not find a way of just passing this command to install the package.
Julie Koesmarno made a great post on installing R packages. Please follow this post. Also Microsoft suggests the following way to install R packages on MSDN.
Since I wanted to be able to have packages installed directly from SQL Server Management Studio (SSMS) here is yet another way to do it. I have used xp_cmdshell to install any additional package for my R (optionally you can set EXECUTE AS USER).
USE WideWorldImporters; GO -- enable xp_cmdshell EXECUTE SP_CONFIGURE 'xp_cmdshell','1'; GO RECONFIGURE; GO EXEC xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER \R_SERVICES\bin\R.EXE" cmd -e install.packages(''tree'')'; GO
in this case I have installed package tree by calling Microsoft R Services with -e switch and adding straight R code.
It might not be the proposed way, but when completing T-SQL and R code it is just a nice way with little annoyances. I would still suggest the way Microsoft proposed or Julie described on her blog, but if you feel confident, you can use this method.
Code is available at Github.