I was trying to create a SQL Server stored procedure that contains R code. I was using RTVS -- R Tools for Microsoft Visual Studio 2015.
The problem is Visual Studio won't recognize the R packages I installed, other than "Base R". For example, 'dplyr', I got this error message when executing the procedure:
A 'R' script error occurred during execution of 'sp_execute_external_script' ...
An external script error occurred:
Error in library(dplyr) : there is no package called 'dplyr'
Calls: source -> withVisible -> eval -> eval -> library ...
But I am pretty sure 'dplyr' has been installed and working fine. Just in case, I first did this:
EXEC sp_execute_external_script @language = N'R',
@script = N' print(normalizePath(R.home())); print(.libPaths());';
and found the location where R packages reside. Then I manually re-installed the 'dplyr' package:
lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\R_SERVICES\\library"
install.packages("dplyr", lib = lib.SQL)
And I restarted VS, re-published this SQL Server stored procedure with R, executed it, got the same error.
In fact, I can manually run the R code with 'dplyr' functionality in Visual Studio and have it interact with SQL Server databases all fine. I just cannot publish the whole thing as a stored procedure and then execute it.
I have searched literally everywhere, found no solution so far. Any help will be tremendously appreciated.
I actually resolved the issue by reinstalling R packages using the command line prompt on each server. Just want to post it here so others can see.