how do you connect to MS SQL server from SUSE linux via RODBC

Question

my odbc.ini file looks like this:

[dsn_name]
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0
Description=metics DB
Trace=Yes
Server=sql.server123
Port=1433
Database=database_name

I can connect to this sql server using isql command. But I have to connec to it using RODBC as follows:

library(RODBC)
sql<-c("select * from <table_name>")
tryCatch({
  ch=odbcConnect("dsn_name",uid="username",pwd = "password")
  res<-sqlQuery(ch,sql)
},error = function(e) {

  print("unable to connect")
})
odbcClose(ch)

Not able to connect and execute the sql query. Any ideas what might be going on here?

Answer 1

In case somebody else runs into this problem. These are the steps I did to resolve the problem:

1. I remove RODBC library since I installed RODBC and install unixODBC driver last.
2. I reinstalled RODBC library.

3. This is the sample script to use RODBC in linux:

   library(RODBC)
   sql<-c("SELECT * FROM [dbo].[view_name]")
   tryCatch({
        ch=odbcDriverConnect("driver=ODBC Driver 11 for SQL Server;server=<server name>;Database=db_name;Uid=<username>;Pwd=<password>")
   
        res<-sqlQuery(ch,sql)
        print("success")
        },error = function(e) {
       print(e)
          print(odbcGetErrMsg(ch))
               print("error")
        })
    odbcClose(ch)