SQL 2016 R Integration – Install Custom Packages – unable to access index for repository


To install custom packages like ggplot2 in the RTM Version of SQL Server 2016 as described in the R Script below you have to disable the following Outbound Firewall Rule on the SQL Server!

“Block network access for R local users accounts in SQL Server instance SQL2016”

image

 

Create procedure [dbo].[get_iris_plot1]
as
begin
    -- Demonstrate how to generate plots from R & return to any SQL client:
    execute sp_execute_external_script
      @language = N'R'
    , @script = N'
    install.packages("ggplot2")
library("ggplot2");
image_file = tempfile();
jpeg(filename = image_file, width=600, height = 800);
print(qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
    xlab = "Sepal Length", ylab = "Petal Length",
    main = "Sepal vs. Petal Length in Fisher''s Iris data"));
dev.off();
OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));
'
    , @input_data_1 = N''
    with result sets ((plot varbinary(max)));
end;
GO

Then it`s possible to install the package and all its dependencies in the Sql Server R environment:

 

image

otherwise you get the following error:

Msg 39004, Level 16, State 20, Line 2
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 2
An external script error occurred:
Warnung: unable to access index for repository
http://mran.revolutionanalytics.com/snapshot/2015-11-30/src/contrib
Warnung: unable to access index for repository http://www.stats.ox.ac.uk/pub/RWin/src/contrib
Warnung: unable to access index for repository http://mran.revolutionanalytics.com/snapshot/2015-11-30/bin/windows/contrib/3.2
Warnung: unable to access index for repository http://www.stats.ox.ac.uk/pub/RWin/bin/windows/contrib/3.2
Fehler in library("ggplot2") : es gibt kein Paket namens 'ggplot2'
Ruft auf: source -> withVisible -> eval -> eval -> library
Zusätzlich: Warnmeldung:
package 'ggplot2' is not available (for R version 3.2.2)

Error in ScaleR.  Check the output for more information.
Fehler in eval(expr, envir, enclos) :
  Error in ScaleR.  Check the output for more information.
Ruft auf: source -> withVisible -> eval -> eval -> .Call
Ausführung angehalten
Msg 11536, Level 16, State 1, Procedure get_iris_plot1, Line 6 [Batch Start Line 2]
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.

 

 

Hint: As an alternative to automatic installation from the R Script you can also download packages as zip files and copy paste them to the R Environment Library Folder:

Here is the default path for a named SQL2016 Instance: C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\R_SERVICES\library

 

(You have to restart the SQL Server Launchpad if you choose the manual installation!)

image


Comments (0)

Skip to main content