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!
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