Failure to connect to odbc database in R

r rodbc
cjacobso picture cjacobso · Jun 12, 2013 · Viewed 51.5k times · Source

I've been trying to connect my company's DMS to R using the odbcConnect command, but get the following message:

myConn <-odbcConnect("NZSQL", uid="cejacobson", pwd="password")
Warning messages:
1: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
  ODBC connection failed

The thing is, I'm positive the Data source name is NZSQL and my uid and password are correct as well. Any insight as to why R may not be finding my data source / driver (the driver is, by the way, specified and working).

Thanks!

Answer

Phil picture Phil · Mar 9, 2015

I ran across this same problem when I was first trying to connect to an Oracle database. In the end what worked for me was using odbcDriverConnect and a connection string instead of odbcConnect.

myConn <-odbcDriverConnect("Driver={Oracle in OraClient11g_home1};Dbq=NZSQL;Uid=cejacobson;Pwd=password;")

You can check on https://www.connectionstrings.com/ for your specific connection string for your database. Mine happened to be this one.

Hope this helps.

Related questions

SQL Server RODBC Connection

Does anyone have a connection string example for using RODBC and connecting to MS SQL Server 2005 or 2008. Thank you.

Read More
How to connect R with Access database in 64-bit Window?

When I tried to connect R with Access database I get an error odbcConnectAccess is only usable with 32-bit Windows Does anyone has an idea how to solve this? library(RODBC) mdbConnect<-odbcConnectAccess("D:/SampleDB1/sampleDB1.mdb")

Read More
How to insert a dataframe into a SQL Server table?

I'm trying to upload a dataframe to a SQL Server table, I tried breaking it down to a simple SQL query string.. library(RODBC) con <- odbcDriverConnect("driver=SQL Server; server=database") df <- data.frame(a=1:10, b=10:1, …

Read More