0

I am trying to create a RScript file that can be run like an executable. I have R code that connects to a Microsoft Azure SQL Server database which uses active directory password authentication, queries the database, and writes a csv report. I created a DSN for the database and have used the following code to successfully connect to the database in both 32-bit and 64-bit environments of both RStudio.exe and RGui.exe:

library(DBI)
library(tidyverse)
library(profvis)

show("Library Installed...")

pause(2)

CON <- dbConnect(odbc::odbc(), "My_DSN", uid = "UserName", pwd = "Password", timeout = 10)

show("Database Connected...")

pause(2)

SQL <- "SELECT * FROM Table"

DATA <- dbGetQuery(CON, SQL)

show("Data Extracted...")

pause(2)

NAME = unique(DATA$Name)

DATA.INDIVIDUAL = list()
for (i in NAME){
  DATA.INDIVIDUAL[[i]] <- DATA %>% filter(Name == i) %>% select("Field1", "Field2", "Field3")
  write.csv(DATA.INDIVIDUAL[[i]], paste("C:/My Documents/", i, "/Report.csv", sep = ""), row.names = FALSE)
  show(paste("Exported",i))
  pause(2)
}

I have also connected by explicitly naming the database with

library(DBI)
con <- dbConnect(odbc::odbc(), uid = "UserName", pwd = "Password", Driver = "ODBC Driver 17 for SQL Server", Server = "ServerName", Database = "DBName", Authentication = "ActiveDirectoryPassword")
show("Database Connected...")

However, when I use RScript.exe to run the same code (both versions), NULL is printed to the RScript command line (before the output "Database Connected..." is printed) and the application exits without completing the rest of my code. Why would Rstudio and RGui connect but RScript fail to connect? Why is there no error, just NULL printed?

Appreciate any help!

Joshua Maxwell
  • 11
  • 1
  • Hmmm...since you assign a connection object to `dbConnect`, Rscript should not output anything. Please post the exact lines sent to Rscript. It cannot be what you posted since there is no `Library` command but `library` (R is case-sensitive). – Parfait May 28 '21 at 17:38
  • @Parfait I updated my post with the full code – Joshua Maxwell May 28 '21 at 18:21
  • So with that code, `NULL` appears before `show("Database Connected...")`? Does `show("Library Installed...")` appear? With `CON <- dbConnect` line, I cannot see how other than an error message appears in console. What happens if you remove all `show` and `pause` lines? – Parfait May 28 '21 at 19:46
  • @Parfait ```show("Library Installed...")``` appears and then ```NULL``` appears and ```show("Database Connected...")``` does not appear and then the window closes. If I remove all the ```show``` and ```pause``` lines then the RScript window opens, I can see the libraries load and then the window closes. I added the ```show``` and ```pause``` to slow down the execution so I could see what was happening. – Joshua Maxwell May 28 '21 at 19:59
  • Rscript is not an application you click to run but an automated command line tool. You would open PowerShell or CMD (Bash in Unix machines) and call `Rscript 'C:\path\to\myscript.R'` or if R bin folder is not in PATH env variable: `C:\path\to\R\bin\Rscript 'C:\path\to\myscript.R'`. See [Run R script from command line](https://stackoverflow.com/q/18306362/1422451). – Parfait May 28 '21 at 20:05
  • @Parfait The way I have used RScript is by appending the end of the text file with ".RScript" and setting windows to always run ".Rscript" files with the RScript.exe executable. This has worked for me in the past. I have tried the method you described and get the same error. – Joshua Maxwell Jun 01 '21 at 14:09
  • @ Parfait However I also get the added error message ```Error: nanodbc/nanodbc.cpp:1021: FA004: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user 'UserName' in Active Directory (Authentication option is 'ActiveDirectoryPassword'). Error code 0x800401F0; state 10 CoInitialize has not been called. Execution halted``` – Joshua Maxwell Jun 01 '21 at 14:09

1 Answers1

0

For future reference I found the cause. The problem is in the odbc driver, in applications without an user interface, CoInitialize is not called.

See https://github.com/r-dbi/odbc/issues/343

Edit: updating the driver solved the problem

Joshua Maxwell
  • 11
  • 1