20

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

How can I fix this?

cigien
  • 57,834
  • 11
  • 73
  • 112
cjacobso
  • 389
  • 2
  • 4
  • 12
  • Which OS are you on and what's the RDBMS? Do you know what drivers are available - is it just unixODBC? – Steph Locke Jun 12 '13 at 15:23
  • 1
    Have you tried setting up the database in the "Data Source (ODBC)" tool? – Señor O Jun 12 '13 at 15:59
  • This looks like an ODBC error, not an R error. It isn't finding the required driver/connection. Make sure that's set up first. – Tommy Levi Jun 12 '13 at 16:59
  • 1
    Hi @TommyLevi, sorry for the late response - could you clarify what it means to "set up" the driver/connection? Sorry - I'm a total noob. To give you some more details on the subject, I have a table (~10 million rows, which I am manipulating in SQLDeveloper) in an Oracle Database. I am trying to do some linear modeling + Classification and regression trees with it. Any advice on the subject would be great! – cjacobso Apr 03 '14 at 00:46
  • Is it possible that your IP address isn't allowed to connect? My (admittedly small) company has multiple MySQL databases stored through our host. Because my home ISP routinely changes my IP address, I continually have to add an access host. – Steven Dec 11 '14 at 22:22

8 Answers8

8

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.

Phil
  • 153
  • 5
  • 15
3

This is IM02 error which means name of the DSN is incorrect.

GO to ODBC and check the USER/System DSN that you should be using. Once your name of DSN is correct, you might get IM014 state error which is archtecture mismatch. In that case,

The simpler solution is IN r studio - go to tools and change the version of R to 32 bit.

It should be ready to work

JayPadhya
  • 65
  • 2
2

I was trying to access SQL Server database and got the same error. After using the correct format of db connection, I got access to my sql server database. 

dbhandle <- odbcDriverConnect("Driver={SQL Server};Server=mydbhost;Database=mydbname;Trusted_Connection=Yes")
sschale
  • 5,168
  • 3
  • 29
  • 36
Chad
  • 43
  • 3
2

I know this is old but also make sure that you remove the spaces around the '=' sign. That was my problem.

Danrex
  • 1,657
  • 4
  • 31
  • 44
1

What worked for me was a 32 bit connection instead of a 64 bit connection.

goldilocked3149
  • 97
  • 1
  • 5
0

I just spent days on this. If you are using a Microsoft Access database you have to use the full path to the database, it won't even find the file in the same folder. So from the above question,

myConn <-odbcConnect("NZSQL", uid="cejacobson", pwd="password")

Would need to be something like

myConn <-odbcConnect("c:\\NZSQL", uid="cejacobson", pwd="password")

Phil's link really helped: https://www.connectionstrings.com/

wittyalias
  • 35
  • 8
0

I had the same problem, what helped me was to add the driver manually:

  1. Search for ODBC Data Source Administrator and click the 64 bit option
  2. Click on "dBASE Files" and then "Add"
  3. Double click on "Oracle in XE"
  4. Fill Data Source Name (NZSQL in your example) and TNS Service Name fields.
  5. Click on Ok.

After that you may go to RStudio again and you will be able to connect using odbcConnect().

Meiz
  • 1
0

I just used R x64 (64-bit) instead of R i386 (32-bit) and it worked

AlexLois
  • 1