r - RODBC to connect to SQL Server on Mavericks

Question

Can I use RODBC on OSX 10.9.2 Mavericks to connect to a SQL Server (for free)?

specifically I want to:

    dB <- odbcConnect("dataBase",pwd="password",uid="userID")
    t <- sqlQuery(dB,"SELECT * FROM myTable")

but odbcConnect throws this warning over and over and never returns...

    50: In odbcDriverConnect("DSN=dataBase;UID=userID;PWD=password") : [RODBC] ERROR: state IM002, code 1408279968, message [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

Now to be honest, I haven't set up the DSN with iODBC with the right DSN because I don't have a free SQL Server driver.

The few people who report using RODBC to connect to SQL Server under OSX 10.9.2 Mavericks have been a bit obscure. Not naming drivers, or mentioning that they compiled RODBC or iODBC by hand.

Update: thread now on these questions: Installation of RODBC on OS X Yosemite and Installation of RODBC/ROracle packages on OS X Mavericks

Answer 1

The only reason this requires more legwork at the moment than usual is that there isn't (yet) an RODBC binary for Mavericks.

Even so, you will need a driver and to set up a DSN. I have always used the drivers from Actual Technologies (not free) and have been very happy with them. Otherwise you'll have to download and compile iODBC or unixODBC from source.

However, you'll still need to compile RODBC form source, and that will require the iODBC header files, which Apple apparently no longer supplies.

If you download the latest iODBC source and then either set your search paths to the location of the sources, or simply place the header files in /usr/include and the libiodbc.a file in /usr/lib. Then you should be able to compile RODBC from source.

Finally, aside from the temporary hiccup regarding the lack of a Mavericks binary for RODBC, all of this is explained in incredible detail in the RODBC vignette.