The aim is to read data from a Microsoft SQL server in RStudio. I am on Windows. So I have:
- Installed RODBC package;
- Created ODBC DSN pointing to the SQL server I need to read data from.
I can successfully connect to the server and read data through the standart R on Windows (RGUI). I can define connection both with
odbcDriverConnect() without any errors. For example:
> con <- odbcConnect("SQLB") > as.list(odbcGetInfo(con)) $DBMS_Name  "Microsoft SQL Server" $DBMS_Ver  "09.00.5000" $Driver_ODBC_Ver  "03.52" $Data_Source_Name  "SQLB" $Driver_Name  "SQLSRV32.DLL" $Driver_Ver  "06.03.9600" $ODBC_Ver  "03.80.0000" $Server_Name  "SQLB\\SQLB"
If I do the same in RStudio it always fails with error messages:
> con <- odbcConnect("SQLB") Warning messages: 1: In RODBC::odbcDriverConnect("DSN=SQLB") : [RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. 2: In RODBC::odbcDriverConnect("DSN=SQLB") : [RODBC] ERROR: state 01000, code 67, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). 3: In RODBC::odbcDriverConnect("DSN=SQLB") : ODBC connection failed
It takes on average 16.5 seconds to return the error message.
> require(microbenchmark) > microbenchmark(odbcConnect("SQLB"), times = 10L) Unit: seconds expr min lq mean median uq max neval odbcConnect("SQLB") 16.52802 16.52953 16.53059 16.5298 16.53141 16.53639 10 There were 30 warnings (use warnings() to see them)
I have tried other drivers but the situation is the same - they all work for R but they fail for RStudio. I can not understand why it works in R but it does not work in RStudio. Please ask if more details should be provided.