Package information

Packages are always saved in the library folder but, depending on your version of R (Open, Client, or Server), SQL Server instance names and paths can be different.

In general, the Client or Server versions will store your libraries on your main drive. For the Client version, the default path is C:\Program Files\Microsoft\R Client\R_SERVER\library. You can see the folder contents in the following screenshot:

In the R Server version, you will find libraries on the path of your default SQL Server instance: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\library. The following are the contents of a Server installation:

Sub-folders represent the name of installed and available packages. To find the default path to your packages, you can execute the following code:

-- Path to libraries on your computer/server
EXECUTE sp_execute_external_script
       @language = N'R'
,@script = N'OutputDataSet <- data.frame(.libPaths());'
WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL));
GO  

In my case, the following is the default path for R packages in the R Server edition:

Much more information can be retrieved using the R function installed.packages(). In this example, we extract much more information on packages and insert the information into a SQL Server table:

-- You can create a table for libraries and populate all the necessary information
CREATE TABLE dbo.Libraries
       (
               ID INT IDENTITY NOT NULL CONSTRAINT PK_RLibraries PRIMARY KEY CLUSTERED
,Package NVARCHAR(50)
,LibPath NVARCHAR(200)
,[Version] NVARCHAR(20)
,Depends NVARCHAR(200)
,Imports NVARCHAR(200)
,Suggests NVARCHAR(200)
,Built NVARCHAR(20)
       )

INSERT INTO dbo.Libraries
EXECUTE sp_execute_external_script
              @language = N'R'
,@script=N'x <- data.frame(installed.packages())
OutputDataSet <- x[,c(1:3,5,6,8,16)]'


SELECT * FROM dbo.Libraries
DROP TABLE dbo.Libraries

By querying this table, you get information on library dependencies, versions, imports, and builds in one execution of sp_execute_external_script:

In the following, we will explore how to install missing R packages. With SQL Server 2016 there were several ways (official or unofficial) which will be addressed and with SQL Server 2017, we have an elegant way of using rxInstall package or creating an external library. Both new ways introduced in SQL Server 2017 are far better, safer, and faster ways to install missing packages.