Trying to Connect to an Odbc Server Using Rodbc in Ubuntu

Error connecting to SQL Server using RODBC in linux

I guess my connection string and config file looks good.

Maybe there was a network issue which was dropping an open RODBC connection.

I tried this solution:-

 repeat
        {
          dbhandle <- odbcDriverConnect(paste0("DRIVER={SQL Server}; server=",server,"; database=",dbname,"; uid=",usernm,"; pwd=",passwd, "; TDS_Version=8.0; Port=1433;", sep=""))
          if(class(dbhandle) == "RODBC"){
              break
            }
        }

With this I'm not proceeding till my dbhandle is of class RODBC.

By integrating this code in my program wherever database connection was required, I was able to run programs without getting those error messages.

RODBC not recognizing my odbc settings

Wow, I thought I was the only person working on this kind of stuff. I had to solve the same problem and found the best solution was to use rjdbc. This is much easier to configure as ODBC on linux or osx was very spotty. RJDBC performance is great as it uses the native ms sql server jar to execute the query. Here is an example right out of one of our scripts. You just need to download the sqljdbc4.jar from microsoft and then install.package("RJDBC") into your environment. I realized you are trying to get ODBC to work and I did get it to work on osx but I gave up due to time for linux.

library(RJDBC);  

drv <- JDBC('com.microsoft.sqlserver.jdbc.SQLServerDriver', '/usr/local/project/dataproviders/jdbc/sqljdbc4.jar', identifier.quote="'"); 

ch <- dbConnect(drv, 'jdbc:sqlserver://the.server.address.net;databaseName=DataWarehouse', 'USERNAME', 'PASSWORD'); 

allsupp_allprod_allproc <- dbGetQuery(ch, paste("SELECT  [Date], Sum([Fail]) as Numerator, Sum([Pass] + [Fail]) as Denominator,'' as Annotation,'True'  as 'Use for CL' FROM [PSU_YieldData] Group by [Date] Order by [Date]")); 

RODBC MS SQL access from Ubuntu using FreeTDS

I fixed this. TDS version was needed in odbc.ini file.

TDS_Version = 8.0

tsql reads version info from freeTDS.conf and worked. isql was failing with same error and it was also looking for this config in odbc.ini. So test with isql if you are configuring for R/Python.

R RODBC package under Ubuntu

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

RODBC on macOS Catalina

I had the same issue connecting with Impala on Mac after some upgrades. The workaround was to make sure RODBC used unixodbc instead of iODBC as follows:

• install unixodbc. I used brew.
• Remove RODBC with remove.packages("RODBC")
• Reinstall RODBC from source and specify the path to unixodbc lib and include
  with
  install.packages("RODBC", type = "source", configure.args = c("--with-odbc-include=/usr/local/include/","--with-odbc-lib=/usr/local/lib/") )

On my computer unixodbc is in /usr/local

---

Related Topics