Search code examples
linuxrubunturodbc

R RODBC package under Ubuntu

I get below error when I try my code on ubuntu/linux R

> library('RODBC')
> sname <- "BLAH"
> dbname <- "BB"
> dbhandle <- odbcDriverConnect(paste("driver={SQL Server};server=", sname, ";database=", dbname, ";trusted_connection=true", sep = ""))
Warning messages:
1: In odbcDriverConnect(paste("driver={SQL Server};server=", sname,  :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect(paste("driver={SQL Server};server=", sname,  :
  ODBC connection failed

But the same code runs fine on windows R. I am using RStudio on windows while using command prompt on linux/ubuntu

> library("RODBC", lib.loc="~/R/win-library/3.1")
> 
> sname <- "BLAH"
> dbname <- "BB"
> dbhandle <- odbcDriverConnect(paste("driver={SQL Server};server=", sname, ";database=", dbname, ";trusted_connection=true", sep = ""))
Solution

  • I ran into this issue and solved it with the following modification between windows and linux versions:

    on windows:

    dbhandle <- odbcDriverConnect(paste("driver={SQL Server};server=", ...

    on linux:

    dbhandle <- odbcDriverConnect(paste("driver=SQLServer;server=", ...

    the difference is just in declaring the driver, with/without curly braces and a space between SQL and Server

    still searching for an explanation of why this works though...