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

Hong Ooi
  • 56,353
  • 13
  • 134
  • 187
Stevieb143
  • 31
  • 1
  • 4

3 Answers3

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

BenAdaba
  • 31
  • 4
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.

Hong Ooi
  • 56,353
  • 13
  • 134
  • 187
  • Hi Hong, many thanks. It was a permission issue. The default install of R was inside the SQL directory so write permissions were disabled – Stevieb143 Jun 22 '17 at 05:33
0

I've been trying to find a solution to this error almost 2 days. I've tried to set up permissions and the result where still the same. There was no dependencies issues. I was trying to run external C# code (i'm aware of SQLCLR).

So after googling and trying all possible proposed solutions, i went back to the Microsoft website and i noted that Language Extension can run only from version of SQL Server CU3 and later. I decided to check which version i was using and it turns out that i was running an earlier version. So i installed the last cumulative update and the job was done, i get rid of that error.

Bottom line:

  • Check that you have installed at least SQL Server CU3
  • set permissions for "ALL APPLICATION PACKAGES" to the <SQL_SERVER_INSTALLATION_PATH><SERVER_FOLDER>\MSSQL

Hope it help's you.

Rouslan Ngouyamsa
  • 1
  • 1