“In the last post, you have seen how we can create a chart by importing data from SQL Server 2017. By doing this, we have a reliable code for drawing chart. Now, we sure our codes work fine, we can put the codes in SQL Server 2017.
In this post, I am going to show how we can have the same chart in SSRS.
I already explained in Post about writing code in SQL Server how to run the R codes inside SQL Server 2017 using the external stored procedure.
ggplot2 Package in ML Services
you able to see the name of installed packages in ML services using some R codes.
EXECUTE sp_execute_external_script @language=N'R' ,@script = N'str(OutputDataSet); packagematrix <- installed.packages(); NameOnly <- packagematrix[,1]; OutputDataSet <- as.data.frame(NameOnly);' ,@input_data_1 = N'SELECT 1 as col' WITH RESULT SETS ((PackageName nvarchar(250) ))
the function to check the name of installed packages in R scripts is “installed.package()”.
when I run this for the first time there is no package name “ggplot2”
I need to install this package to work with it,
The new question comes up:
How to install new Packages in SQL Server 2017 R services?
there are many ways:
use command install.packages in external scripts store procedure as below
the first approach
EXECUTE sp_execute_external_script @language=N'R' ,@script = N' install.packages("ggplot2");' ,@input_data_1 = N'SELECT 1 as col' WITH RESULT SETS ((PackageName nvarchar(250) ))
the above codes may work but sometimes not because of the administrator permission, you may get below message “Warning in install.packages(“arules”) :
‘lib = “C:/Program Files/Microsoft SQL Server/MSSQL14.ML2017/R_SERVICES/library”‘ is not writable
Error in install.packages(“ggplot2”) : unable to install packages
Calls: source -> withVisible -> eval -> eval -> install.packages
Run as admin Rugi.exe to install the package. but you need to specify where to install the packages. As a result, we need the path of the ML services for R in SQL Server. there is a function in R: .libPaths() that show the location of installed packages. so you need to run this code in SQL Server 2017 to find out the R library path.
EXECUTE sp_execute_external_script @language = N'R' , @script = N'OutputDataSet <- data.frame(.libPaths());' WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL)); GO
the result will be
after that, you able simply run the below codes to install packages
lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\R_SERVICES\\library" install.packages("ggplot2", lib = lib.SQL)
still, you may get the error!
you install ggplot2, but this package need other packages to work properly, so you need to install other related packages as well.
Easy but not reliable approach: copy the whole package list you have in your local Rstudio into SQL Server location.
this approach is not a trusted one, but most of the time will work just be careful about the package versions and also the not replace the one that already exists.
by run the below code you will be sure ggplot2 has been installed:
exec sp_execute_external_script @language =N'R', @script=N'OutputDataSet<-InputDataSet; library("ggplot2")', @input_data_1 =N'select 1 as hello' with result sets (([hello] int not null)); go
the result should be
Now I am sure I have access to the ggplot2 package.
I am able to create chart inside SQL Server. IN the next post I will show how to shows the chart in SSRS, or show as an HTML chart.