sp_execute_external_script R script 'unable to start png() device'

Question

I am trying to create a plot in SQL Server R using the sp_execute_external_script command, but it fails to create the plot png image:

DECLARE @stateName nvarchar(50) = 'Michigan'
EXEC sp_execute_external_script
 @language = N'R',
 @script = N'
    covidWeeklyDataSet <- InputDataSet
    # set up report file for chart
    reportfile <- "C:\\temp\\Covid19-Weekly.png"
    png(file = reportfile)
    plot(x = covidWeeklyDataSet[, 1], y = covidWeeklyDataSet[, 2],
        main = paste(state_name, "Weekly Covid 19 Counts", sep = ""),
        col = 3, ylab = "Cases", xlab = "Dates", ylim = c(0, 35000))
    par(new = TRUE)
    plot(x = covidWeeklyDataSet[, 1], y = covidWeeklyDataSet[, 3],
         col = 2, ylab = "Cases", xlab = "Dates", ylim = c(0, 35000))
    dev.off()
 ',
 @input_data_1 = N'SELECT [date], cases, deaths FROM #weekly',
 @params = N'@state_name nvarchar(20)',
 @state_name = @stateName

The error message is as follows:

Msg 39004, Level 16, State 20, Line 13 A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 13 An external script error occurred: Error in png(file = reportfile) : unable to start png() device Calls: source -> withVisible -> eval -> eval -> png In addition: Warning messages: 1: In png(file = reportfile) : unable to open file 'C:\temp\Covid19-Weekly.png' for writing 2: In png(file = reportfile) : opening device failed

Error in execution. Check the output for more information. Error in eval(ei, envir) : Error in execution. Check the output for more information. Calls: runScriptFile -> source -> withVisible -> eval -> eval -> .Call Execution halted

It also fails as an administrator. Please help.

lptr lptr · Accepted Answer · 2020-04-23T12:46:32

READ & WRITE permissions for c:\temp to "ALL APPLICATION PACKAGES".

EXEC sp_execute_external_script
 @language = N'R',
 @script = N'
#file.create("c:\\temp\\mytest.png")
png(filename = "c:\\temp\\mytest.png",
    width = 500, height = 500, units = "px", pointsize = 12,
    bg = "white",  res = NA)
x <- sample(c("A","B","C","D"), 20, replace=TRUE)
plot(table(x))
dev.off()'

sp_execute_external_script R script 'unable to start png() device'

1 Answers