0

I want to run an R script (in Win 7) from SQL Server 2014 each time a new record is added (to perform some analysis on the data). I saw that this can be done with the xp_cmdshell command which is like running it manually from the command line.

My problems (and questions) are:

  1. I've made out from various websites that probably the best option is to use Rscript. This would have to be used at the command line as:

C:\Program Files\R\R-3.2.3\bin\x64\Rscript "my_file_folder\my_file.r

Can I copy Rscript.exe to the folder where my script is, such that I can run my script independently, even if R is not installed? What other files do I need to copy together with Rscript.exe such that it would work independently?

  1. My script loads some packages that contain functions that it uses. Is there a way to somehow include these in the script such that they don't have to be loaded every time (it takes about 5 sec so far and I need this script to be faster)? Or is there a way to only load these packages the first time that the script runs?

In case the overall approach I've described here is not the best one, I am open to doing it differently. Maybe there is a way to somehow package the R script together with all the required dependencies (libraries and other parts of the R software which the script would need to run independently).

What I ultimately need is a for the script to run silently, and reasonably fast, without any windows or anything else popping up, each time a new record is added to my database, do the analysis and exit.

Thanks in advance for any answers.

UPDATE: I figured out an elegant solution to running the R script. I'm setting up a job in SQL Server and inside that job I'm using "xp_cmdshell" to run my script as a parameter to Rscript.exe, as detailed at point 1 above. I can start this job from any stored procedure and the beauty of it is that the stored procedure does not wait for the script to finish. It just triggers the job (that runs the script in a separate thread) and then it continues with its business.

But questions from points 1 and 2 still remain.

Alex B
  • 9
  • 2
  • Can you upgrade to a newer version of SQL Svr? R can run within SQL Server now thanks to Microsoft R. w/r/t you other q's: R has to be on the server & there's no real way to speed up the loading of the Rscript script load/run apart from using a RAM disk. You could always try to run OpenCPU on the same or separate box and invoke a REST call frm the insert rcrd trigger but that's probably going to be somewhat slow, tho it won't have to load pkgs every time, but that also sounds like it'll be [an `xp_xmdhsell` call](http://stackoverflow.com/questions/6153330/can-a-sql-trigger-call-a-web-service). – hrbrmstr Aug 29 '16 at 01:45
  • Thanks for the reply. I've sorted out the running of the script in a separate thread (detailed in the update to the original post) which means that I'm not very bothered anymore with the time it takes to run because my SQL code does not wait for it to finish. It would still be nice to avoid loading all packages every time, as that is the bulk of the time it needs to run. – Alex B Aug 29 '16 at 20:27

0 Answers0