Sql-server – How one can install a given R package in SQL Server

rsql server

I was asked to write a R code to do computations. I managed to do it such that it works well in R. Next, I was asked to do the same function in SQL Server. I was unable to do it as it requires a package psych that SQL Server won't find. How can I get R running properly in SQL Server such that it find the package psych? Microsoft SQL Server Management Studio v17.0 outputs

An external script error occurred:
Error in library(psych) : there is no package called 'psych'
Calls: source -> withVisible -> eval -> eval -> library`

Best Answer

How one can install a given R package in MSSQL?

See installation of R services step by step Here & Here

How can I get R running properly on MSSQL?

So far, we have installed the components required for running R Services; now to enable the R Services feature itself you need to explicitly enable it with the following command before you can invoke R scripts in SQL Server.

--Execute this script to verify if R Services in enabled 

EXEC sp_configure  'external scripts enabled';
Go

enter image description here

To Verify R services

--Execute this script to enable R Services on the SQL Server instance

EXEC sp_configure  'external scripts enabled', 1; 
RECONFIGURE WITH OVERRIDE
Go

--Execute this script to verify if R Services in enabled

EXEC sp_configure  'external scripts enabled';
Go

enter image description here

To Verify R services

Finally, you need to restart SQL Server service, which will automatically restart the related SQL Server Trusted Launchpad service (it runs advanced analytics extensions to enable integration with Microsoft R Open using standard T-SQL statements; disabling this service will make the Advanced Analytics features of SQL Server unavailable) as well for executing external R scripts. enter image description here

At this time, if you execute the following command again, you will notice 1 for both config_value and run_value, which means you are not good to execute R script on this SQL Server instance:

--Execute this script to verify if R Services in enabled 

  EXEC sp_configure  'external scripts enabled';
  Go

enter image description here

SQL Server 2016 includes a new system stored procedure (sp_execute_external_script) to enable you to execute external scripts inside the database engine. The stored procedure loads and executes a script written in a supported language from an external location.

For your further ref here

Related Question