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

[Marc Schwartz]

On Jun 4, 2009, at 7:40 AM, 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. 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?
>
> Appreciate the help.
>
> Regards,
> David



I would urge you to contact Parallels and talk to their tech folks on  
this.

The challenge here is that the Windows VM essentially functions in a  
'sand box'. You can configure things like Coherence and shared folders  
and applications for convenience, but a Windows application is running  
in a logically separate Windows machine. With the exception of the  
aforementioned sharing at the file level, you really need to think of  
the scenario as being two separate computers.

This means that you would likely need to configure the Parallels  
networking configuration in such a fashion that the Windows VM is  
effectively seen on the local network as a separate computer with a  
separate IP address, on which MSSQL is running. I don't use Parallels  
(or VMWare or VirtualBox), but from a brief review of the Parallels  
documentation at:

   http://download.parallels.com/desktop/v4/docs/en/Parallels_Desktop_Users_Guide.pdf

there are options under Networking that would seem to perhaps  
facilitate the above. So in effect, OSX would see the Windows VM as a  
separate server on the network running MSSQL, to which you can connect  
as if you were connecting to a physically separate server.

If you can achieve that configuration, then you would need an ODBC  
driver for MSSQL that runs on OSX. I don't know if MS offers that or  
if you need to go to a third party. If the latter, Actual Technologies  
offers an ODBC driver for MSSQL for OSX:

   http://www.actualtechnologies.com/product_sqlserver.php

I use their Oracle ODBC driver to connect R on my MacBook Pro using  
RODBC, to our Oracle server and it works fine and was easy to configure.

Talk to the Parallels folks and they can give you more definitive  
guidance.

HTH,

Marc Schwartz