[R-SIG-Mac] Query MS SQL Server DB using RODBC from OS X

[Simon Urbanek]

David,

On Jun 4, 2009, at 8:40 , David Marra wrote:

> Everyone,
>
> I would like to access from R running in OS X 10.5 a Microsoft SQL  
> database that is running in Windows on a Parallels virtual machine.
>
> The Parallels VM and the OS X are on the same Mac.

Technically, that's irrelevant, because they act as two separate  
computers - for all practical purposes you're connecting a Mac to a  
database on another PC over a network.


> A DSN is set up on Windows. Running queries on the VM itself with  
> the Windows version of R using the RODBC package commands  
> odbcConnect and sqlQuery works just fine.
>
> Does anyone know how this can be done? How to make R in OS X aware  
> of the Windows DSN? What ODBC driver works for OS X to get access to  
> the Windows VM?
>

You'll need an ODBC driver for OS X capable of using Microsoft SQL.  
It's been a while since I used one (if I recall correctly it was the  
Actual one), but here are some links:

http://www.actualtechnologies.com/product_sqlserver.php
http://uda.openlinksw.com/odbc/express/odbc-sqlserver-ee/

Once the driver is installed, you can use ODBC Administrator utility  
to create a DSN on the Mac. (However, I have never really used ODBC in  
R since there are native R drivers for all databases I'm using ...).

Cheers,
Simon