3

So I'm messing around with the new "Integration with R" feature in SQL 2016. However, having trouble setting the working directory to a mapped network drive (Z:) within SSMS.

Here's my code:

EXEC sp_execute_external_script
@language = N'R',
@script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
@input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

Getting the error message:

"Error in setwd("Z:/"): cannot change working directory
Calls: source -> withVisible -> eval -> eval -> setwd"

I've tried changing the drive letter to local, physical drives on the server and it works fine, just doesn't work for the mapped network drive.

1
1

Each user session on Windows has its own set of network drives. Just because your user session has the drive mapped doesn't mean the session being run by the SQL Server service has that drive mapped.

You can check if the mapped drive is visible to SQL Server by running this:

EXEC sys.xp_cmdshell 'net use';

This will output something similar to:

output  
New connections will be remembered.  
NULL  
NULL  
Status       Local     Remote                    Network
-------------------------------------------------------------------------------  
OK           Z:        \\machine\share           Microsoft Windows Network  
The command completed successfully.  
NULL  
NULL  

In order to successfully run the xp_cmdshell extended stored procedure, you may need to enable it, using this code:

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE
GO
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE
GO

You can determine what account SQL Server is using by looking at the output from this command:

EXEC sys.xp_cmdshell 'whoami';

The account referenced in the above output must have access to the share, as well as the file system referenced by the share.

I don't have access to R here, however you might try mapping the drive, prior to running the R script, like so:

EXEC sys.xp_cmdshell 'net use Z: \\server\share /user:[DOMAIN]\Username <password>';
EXEC sys.sp_execute_external_script @language = N'R',
    @script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
    @input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

The above code will cause the drive to be mapped for SQL Server's session until SQL Server is restarted. Be advised, it's not a great idea to save the password in a script unless the script's location is guaranteed safe, otherwise an attacker might see the password.

4
  • Do I map the drive using my own credentials or the sql server service account? I tried using my own credentials just now and I was able to map it but when running the R script, still getting the same error. – Chinesinho Aug 22 '16 at 21:54
  • @Chinesinho, mapped drives are per user profile so you need to SQL Server service account. Not sure but you might need to restart SQL too. – Dan Guzman Aug 22 '16 at 22:51
  • @chineseinho - did you try using my sample code to map the drive into SQL Server's Windows Session? The credentials used must have access to the share and the file system before ng connected to via the share. – Hannah Vernon Aug 23 '16 at 12:03
  • @MaxVernon: I did try your sample code and I "think" the issue is that the SQL service account doesn't have permissions to that file share, so I've put in a request to get that taken care of. Will try again once that is done and let you know the outcome. – Chinesinho Aug 24 '16 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.