R Engine in SQL Server cannot create file to local storage

Question

Dear everybody!

I did all the recipes expressed on the following link:

https://www.mssqltips.com/sqlservertip/6425/how-to-export-data-from-sql-server-to-excel

but I still face one fatal problem.

The R script tries to create the excel file but is denied the write permission to the local drive. I formatted the local drive with FAT32 file system but the problem persists. My operating system is Windows Server 2019. The error returned is the following from the messages section of SSMS:

Warning message:

In file.create(to[okay]) :

  cannot create file 'e:\ table.xlsx', reason 'Permission denied'

here is my code:

DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
    OutputDataSet <- SqlData;
    
   library(openxlsx)
   library(dplyr)
   
   wb <- createWorkbook()
   addWorksheet(wb, sheetName = mytname)
   writeData(wb, mytname, OutputDataSet)
 
   saveWorkbook(wb, file = paste(paste("e:\\",mytname),".xlsx", sep=""), overwrite = TRUE)
';			

DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
    SELECT top 1 AddressID, AddressLine1 from [AdventureWorks2014].Person.Address
	';
	
--SELECT top 1 AddressID, AddressLine1 from [AdventureWorks2014].Person.Address

EXEC sp_execute_external_script
    @language = N'R',
    @script = @rscript,
    @input_data_1 = @sqlscript,
    @input_data_1_name = N'SqlData',
    @params = N'@mytname nvarchar(20)',
    @mytname = N'table';
GO

I truly appreciate your help. :)

Answer 1

Dear everybody!

I did all the recipes expressed on the following link:

https://www.mssqltips.com/sqlservertip/6425/how-to-export-data-from-sql-server-to-excel

but I still face one fatal problem.

The R script tries to create the excel file but is denied the write permission to the local drive. I formatted the local drive with FAT32 file system but the problem persists. My operating system is Windows Server 2019. The error returned is the following from the messages section of SSMS:

Warning message:

In file.create(to[okay]) :

  cannot create file 'e:\ table.xlsx', reason 'Permission denied'

here is my code:

DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
    OutputDataSet <- SqlData;
    
   library(openxlsx)
   library(dplyr)
   
   wb <- createWorkbook()
   addWorksheet(wb, sheetName = mytname)
   writeData(wb, mytname, OutputDataSet)
 
   saveWorkbook(wb, file = paste(paste("e:\\",mytname),".xlsx", sep=""), overwrite = TRUE)
';			

DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
    SELECT top 1 AddressID, AddressLine1 from [AdventureWorks2014].Person.Address
	';
	
--SELECT top 1 AddressID, AddressLine1 from [AdventureWorks2014].Person.Address

EXEC sp_execute_external_script
    @language = N'R',
    @script = @rscript,
    @input_data_1 = @sqlscript,
    @input_data_1_name = N'SqlData',
    @params = N'@mytname nvarchar(20)',
    @mytname = N'table';
GO

I truly appreciate your help. :)

Answer 2

I'd try asking for help over here.

sql-server-transact-sql - Microsoft Q&A

 

 

---

Regards, Dave Patrick ....
Microsoft Certified Professional
Microsoft MVP [Windows Server] Datacenter Management

Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.