1

The (R) code below works on my machine and when executed in R on the database server. I am also pretty sure that the security is set correctly. Unfortunately, when run as SQL script (see below), I get this message:

cannot change working directory
Calls: source -> withVisible -> eval -> eval -> setwd

Btw, I can setwd and read/write directly on the server using:

setwd("X:/somewhere_amazing/xyz")

So I am bit lost, as to what the issue may be? Any feedback would be very much appreciated! Thanks.

EXEC sp_execute_external_script @language = N'R',
       @script = N'  
    df <- data.frame(x = c("a", "b"))

    setwd("\\\\bla\\somemore\\another_subfolder\\")
    write.csv(df, "test.csv", row.names=F)
'

I also tried:

EXEC sp_execute_external_script @language = N'R',
           @script = N'  
        df <- data.frame(x = c("a", "b"))
    
        write.csv(df, "\\\\bla\\somemore\\another_subfolder\\test.csv", row.names=F)
    '
cs0815
  • 16,751
  • 45
  • 136
  • 299
  • Instead of changing the working directory, try to use full path, something like: `write.csv(df, "path/to/myFolder/test.csv", row.names = FALSE)` , also, try to use forward slash. – zx8754 Mar 03 '21 at 10:21
  • funny you say this. not sure why I got hang up on setwd. Unfortunately, this also does not work ... it says: cannot open the connection – cs0815 Mar 03 '21 at 10:23
  • Might be obvious, but check if you have write permissions on that folder, and ensure it exists? – zx8754 Mar 03 '21 at 10:26
  • @zx8754 Thanks - I am assured by IT I, or in this case the sql server account, running the code does ... the error is not helpful in this regard ... – cs0815 Mar 03 '21 at 10:28
  • Related post, says write permissions, https://stackoverflow.com/q/17779004/680068 – zx8754 Mar 03 '21 at 10:32
  • Thanks. In my case it says: "Invalid argument" rather than "Permission denied" so I now suspect my path format is not ok? – cs0815 Mar 03 '21 at 12:00
  • Would be nice if you could share your code fully? Are we sure, there is no typo, comma missing? Or backslashes escaping stuff they shouldn't? – zx8754 Mar 03 '21 at 12:25
  • the code above is my complete code (obviously I am not revealing the exact name of the folder). – cs0815 Mar 03 '21 at 12:27
  • How about using a path variable rather than a working directory ? Like `path <- "\\\\bla\\somemore\\another_subfolder\\test.csv"` – Alexandre georges Mar 03 '21 at 12:31
  • @Alexandregeorges thanks I tried. mentioned it in comment but need to update question. – cs0815 Mar 03 '21 at 12:43
  • 1
    Python and R scripts are hosted by the [SQL Server Launchpad Service](https://learn.microsoft.com/en-us/sql/machine-learning/security/sql-server-launchpad-service-account) and so may be using different credentials than the SQL Server service. You'd need to check which account Launchpad is using and ensure that it has appropriate permissions at the share and file system levels. – AlwaysLearning Mar 13 '21 at 15:45
  • @AlwaysLearning - thanks will have a look next week. – cs0815 Mar 13 '21 at 16:08
  • @AlwaysLearning it might be it - still verifying – cs0815 Mar 15 '21 at 15:56

0 Answers0