Tuesday, February 28, 2017

How Do I Add an R Package to SQL Server from In-Database R?

Hello Dear Reader!  I'm working on some really fun stuff with in-database R using SQL Server 2016.
I needed to add a couple packages to my R engine so I could use them and call them from an R script. I realized I had not added any external packages to my R instance so this was a perfect opportunity to write a blog!  So here is a quick blog on how you add an R Package to SQL Server.  This entire tutorial is if you have internet.  If you need to do this for a server, which probably doesn't have access to internet look at this MSDN article, Install Additional R Packages on SQL Server.

First we need to find our instance default install path for SQL Server.  In this case I'm a comic book geek and my named instances are JARVIS and STARK.  STARK is my 2016 instance so we'll need to find that path.

In this case my path is C:\Program Files\Microsoft SQL Server\MSSQL13.STARK\R_SERVICES\bin\x64 , then I want to hold down SHIFT and right click on the Rgui.exe, then select Run as administrator.

After this opens we need to create a variable to hold the location were we would like the package to be installed.  Then we can install the package we want to use, in this case the plyr package.

lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL13.STARK\\R_SERVICES\\library"
install.packages("plyr", lib=lib.SQL) 

Notice I adjusted the path for my named instance.

After it installs our window should look like this.

Now I can run a simple test script loading the plyr package.

execute sp_execute_external_script
,@script =N'
,@input_data_1=N'select 1'


If I get this error the package did not load.

Msg 39004, Level 16, State 20, Line 4
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 4
An external script error occurred: 
Error in library(plyr) : there is no package called 'plyr'
Calls: source -> withVisible -> eval -> eval -> library

Error in ScaleR.  Check the output for more information.
Error in eval(expr, envir, enclos) : 
  Error in ScaleR.  Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
Msg 11536, Level 16, State 1, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

If I the statement compiles, everything is fine and I get a result set.

As always Thanks for stopping by.




  1. With OMC Mark Hurd, customers can eliminate multiple information silos, resolve application issues faster, and run IT like a business. Log Exploration and Machine Learning for Applications and Infrastructure; Automate Application and Infrastructure Configuration Assessments…..

  2. Hello, though to be honest I don’t know anything about computer related stuff, this is a pretty neat tutorial. It is quite well written.

  3. This comment has been removed by the author.

  4. http://www.youtube.com/channel/UC-iXeGFTWJdCVbmOG0x4-AA

  5. This comment has been removed by the author.

  6. Wow, thank you for keeping posing interesting material to your blog! It's really cool that I can find so much useful information here. Like, my favorite blog. Indeed. The only thing I would like to see here more - are reviews on Essaytigers. Can you add them?

  7. It is imperative that we read blog post very carefully. I am already done it and find that this post is really amazing. vpnveteran

  8. Thank you for your great job. This is the info I have been looking for!dsploit OGYoutube magisk manager

  9. Impressive web site, Distinguished feedback that I can tackle. I am moving forward and may apply to my current job as a pet sitter, which is very enjoyable, but I need to additional expand. Regards click here

  10. Incredible articles and awesome design. Your blog entry merits the greater part of the positive input it"s been getting. privacyonline

  11. I think this is a better than average article. You make this data intriguing and locks in. You give perusers a considerable measure to consider and I welcome that sort of composing. weneedprivacy.com

  12. pleasant post, stay aware of this fascinating work. It truly regards realize that this subject is being secured likewise on this site so cheers for setting aside time to talk about this! lemigliorivpn.com

  13. Idiotic and additionally trusting people at the same time can be an obstacle, on aged and also fully grown folk assembled also will follow a complication. Age and also embryonic concern together. https://vpnveteran.com/