<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/installing-r-packages-in-sql-server-using-only-t-sql/"></div></div><div><a data-url="https://www.r-bloggers.com/installing-r-packages-in-sql-server-using-only-t-sql/" data-counturl="https://www.r-bloggers.com/installing-r-packages-in-sql-server-using-only-t-sql/" data-count="horizontal" data-text="Installing R packages in SQL Server using only T-SQL" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2016/08/02/installing-r-packages-in-sql-server-using-only-t-sql/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Installing R packages in SSMS using T-SQL can take some time, especially when you need to switch between <a target="_blank" href="https://www.visualstudio.com/en-us/features/rtvs-vs.aspx">R Tools for Visual Studio</a> or <a target="_blank" href="https://www.rstudio.com/">R Studio</a> 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.</p>
<pre>--InstallPackage using sp_execute_external_script
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'install.packages(&quot;ggplot2&quot;)'
WITH RESULT SETS (( ResultSet VARCHAR(50)));

-- using Download.file command
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'download.file(&quot;https://cran.r-project.org/bin/windows/contrib/
3.4/ggplot2_2.1.0.zip&quot;,&quot;ggplot2&quot;)
                 install.packages(&quot;ggplot2&quot;, repos = NULL, type = &quot;source&quot;)'
WITH RESULT SETS (( ResultSet VARCHAR(50)));</pre>
<p>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.</p>
<p><a target="_blank" href="http://www.mssqlgirl.com/installing-packages-in-sql-server-r-services.html">Julie Koesmarno made a great post on installing R packages</a>. Please follow this post. Also Microsoft suggests the following way to install R packages on <a target="_blank" href="https://msdn.microsoft.com/en-us/library/mt591989.aspx">MSDN</a>.</p>
<p>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 <a target="_blank" href="https://msdn.microsoft.com/en-us/library/ms175046.aspx"> xp_cmdshell</a> to install any additional package for my R (optionally you can set <a target="_blank" href="https://msdn.microsoft.com/en-us/library/ms181362.aspx">EXECUTE AS USER</a>).</p>
<pre>USE WideWorldImporters;
GO
-- enable xp_cmdshell
EXECUTE SP_CONFIGURE 'xp_cmdshell','1';
GO

RECONFIGURE;
GO
EXEC xp_cmdshell '&quot;C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER
\R_SERVICES\bin\R.EXE&quot; cmd -e install.packages(''tree'')';  
GO</pre>
<p>in this case I have installed package tree by calling Microsoft R Services with <em><strong>-e</strong></em> switch and adding straight R code.</p>
<p>Results are:</p>
<p><img alt="Capture.PNG" class="wp-image-1889" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/08/capture1.png?resize=358%2C699&ssl=1" data-recalc-dims="1"></p>
<p>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.</p>
<p>Code is available at <a target="_blank" href="https://github.com/tomaztk/Installing_R_packages_using_TSQL">Github</a>.</p>
<p>Happy R-SQLing!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/1865/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/1865/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/installing-r-packages-in-sql-server-using-only-t-sql/"></div></div><div><a data-url="https://www.r-bloggers.com/installing-r-packages-in-sql-server-using-only-t-sql/" data-counturl="https://www.r-bloggers.com/installing-r-packages-in-sql-server-using-only-t-sql/" data-count="vertical" data-text="Installing R packages in SQL Server using only T-SQL" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2016/08/02/installing-r-packages-in-sql-server-using-only-t-sql/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/hTIXJ21hIjo"></a></div>