I was trying to test an install of R in SQL Server 2016 and when running a script I received this error: Fatal error: cannot create ‘R_TempDir’
Following the instructions here, I enabled external scripts, restarted the sql server service, and then tried to run the following test script:
exec sp_execute_external_script @language =N'R',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
This is when the fatal error occurred. As the error suggests, R is having some issues creating a temporary directory. After some internet searching and trial and error I got past the issue.
Enable 8dot3 File Names
R configuration uses the 8dot3 file name convention, also known as “short names”. To enable this on windows 10, run the following command in CMD (command prompt):
fsutil.exe behavior set disable8dot3 0
For more options and information look here: https://support.microsoft.com/en-us/kb/121007
Give access to the working directory to R
Locate and open “rlauncher.config” file in a text editor. This file will be under the “<sqlserver_instance>\binn” directory. Take a look at the location of WORKING_DIRECTORY. This should have a “short name” file path. The path should be something like “<sqlserver_instance>\EXTENS~1”, and “\EXTENS~1” is equivalent to “\ExtensibilityData”. We need to give access to R to this folder. I did this by changing the permissions to full control to everyone. You may want to be more restrictive here, but in my case this did not matter.
- Right click folder > Properties > Security tab > Advanced > Add
- Select a principal (I entered “Everyone”)
- Tick “Full control” under basic permissions and click “OK”
- Tick “Replace all child object and permissions entries with inheritable permissions entries from this object” and click “OK”
Now if you rerun the script above you should get a result of “hello, 1”.