Execute R script with sp_execute_external_script in MSSQL

Question

I am trying to execute an external R file with sp_execute_external_script in MSSQL:

EXECUTE sp_execute_external_script @language = N'R'
    , @script = N'
source("C:/Rscripts/script.R");';

I get the following error (translated):

External script error: 
Error in file(filename, "r", encoding = encoding) : 
  could not open connection
Calling: source -> withVisible -> eval -> eval -> source -> file
Additional warning:
In file(filename, "r", encoding = encoding) :
  Could not open file 'C:/Rscripts/script.R': Permission denied

Which can be summed up as a file permission error.

I tried the following:

assigning several server roles to the user which executes the sql script
assigning full access rights to the folder and script file to user 'MSSQLLaunchpad' and group 'SQLRUserGroup'

but without success.

The *.R file contains valid R script and can be executed by using the script from the SQL query in RStudio.

We need to open an external R file as the procedure sp_execute_external_script only takes a varchar(max) @script argument.

Stack Overflow is an English Community, and any text, including errors, needs to be in English, so that anyone on the community can read and understand it. Please ensure you take the time to translate the error as well. — Thom A
– Thom A ♦, Commented Sep 24, 2020 at 13:15
have you tried assigning permissions for the script folder to "all application packages"? — lptr
– lptr, Commented Sep 24, 2020 at 14:01
Try to give RW permission on this folder for user under which SQL Server is running — gofr1
– gofr1, Commented Sep 24, 2020 at 18:13
Does it work if you use backslashes for path separators? e.g.: source("C:\Rscripts\script.R"); — AlwaysLearning
– AlwaysLearning, Commented Sep 24, 2020 at 22:19
@lptr This is working! If you make an answer out of your comment, I will accept it. Thank you! — Julius S.
– Julius S., Commented Sep 25, 2020 at 5:39

lptr · Accepted Answer · 2020-09-25 07:27:47Z

2

For reading/writing from/to folders with sp_execute_external_script, assign permissions to "ALL APPLICATION PACKAGES"

For accessing other machines/external resources, check (and disable) the blocking outgoing firewall rules for the several "AppContainers-nn in SQLServer".

edited Sep 25, 2020 at 7:27

answered Sep 25, 2020 at 7:21

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute R script with sp_execute_external_script in MSSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related