how do you connect to mssql server using RODBC?

Question

I had an Oracle db and I used RODBC connection to connect to oracle as below:

library(RODBC)
new_sql<-c("select * from table")
tryCatch({
            ch=odbcConnect("odbc_con",pwd = "!passwd!")
            res<-sqlQuery(ch,new_sql)
            },error = function(e) {
            print(odbcGetErrMsg(ch))          
        })

The database is mirgrated to mssql, and I've created a new odbc connection to mssql server, called new_odbc_con using DATA SOURCES(ODBC), using administrator tools.

When I updated the code to this:

tryCatch({
            ch=odbcConnect("odbc_con",pwd = "!password!")
            res<-sqlQuery(ch,new_sql)
            },error = function(e) {
            print(odbcGetErrMsg(ch))          
        })

I am not able to connect to the sql server and execute the sql. What am I doing wrong?

Answer 1

In case it comes up again, this is what I did to fix this problem.

1. I created a system odbc connection using Admin Tools (Data Sources (ODBC))
2. I have update this line to ch=odbcConnect("odbc_con",pwd = "!password!") to ch=odbcConnect("odbc_con",uid="userid",pwd = "!password!")

worked like a charm.