SQL 2016 with R - Error HRESULT 0x80004004

3

I am working through a number of tutorials on using SQL and R. But when I am trying to Run the R Script to obtain the 'ggplot' library I am getting the following error

Msg 39004, Level 16, State 20, Line 1
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 1
An external script error occurred: 
Error in library("ggplot2") : there is no package called 'ggplot2'
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

(0 row(s) affected)

The Original Script is

INSERT INTO chartBinary (binData)
EXEC sp_execute_external_script
@language = N'R',
@script = N'
library("ggplot2");
img <- inputDataSet;
image_file = tempfile();
png(filename = image_file, width=800, height=600);
print(ggplot(img, aes(x = AirportID, y = WindSpeed)) +
labs(x = "Airport ID", y = "Wind Speed") +
theme(axis.text.x = element_text(angle=90, hjust=1, vjust=0)) +
geom_point(stat = "identity") +
geom_smooth(method = "loess", aes(group = 1)) +
geom_text(aes(label = AirportID), size = 3, vjust = 1.0) +
geom_text(aes(label = round(WindSpeed, digits = 2)), size = 3, vjust = 2.0));
dev.off();
OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));',
@input_data_1 = N'SELECT AirportID, AVG(CONVERT(float, WindSpeed)) as   WindSpeed 
FROM
[Weather_Sample] GROUP BY AirportID ORDER BY AirportID;',
@input_data_1_name = N'inputDataSet',
@output_data_1_name = N'OutputDataset';

The system has SQL 2016, SSMS 2017, MS R Open 3.4.0 The integration with R works well with Visual Studio 2015 and has no errors. Can download library packages and run the scripts with no errors. Only when I start using SMSS I am unable to download packages

r
sql-server-2016
microsoft-r
asked on Stack Overflow Jun 20, 2017 by Stevieb143 • edited Jun 21, 2017 by Hong Ooi

2 Answers

3

You will need to install the ggplot2 package to the SQL Server instance. There are various ways to install unavailable R packages to the SQL Server instance.

You will choose the method suitable for you depending on your setup.

If you are working on a local machine then you would want to download the Windows Binaries (zip file) of the package and install using T-SQL.

Check here: Install additional R packages on SQL Server

answered on Stack Overflow Nov 11, 2017 by BenAdaba
0

SQL Server runs your R script using a separate account with lowered privileges, for security reasons. In particular, this is not the same as your own user account. So if you installed your packages under your user directory, the script won't be able to find them.

A fix is to install the packages in a separate, globally readable directory (say c:\Rlib). After doing that, point your script to that location by adding .libPaths("c:\\Rlib") before the library() call.

answered on Stack Overflow Jun 21, 2017 by Hong Ooi

User contributions licensed under CC BY-SA 3.0