0

I am trying to access MS SQL on a Mac 10.9.5 using the package RODBC in R.

I did the:

## install homebrew
ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)"
## install freetds
brew install freetds --with-unixodbc

(the --with-unixodbc option was used so the library /usr/local/lib/libtdsodbc.so is generated.) and follow the remaining instructions at: http://www.r-bloggers.com/guide-to-accessing-ms-sql-server-and-mysql-server-on-mac-os-x/

I edited the odbc.ini file so that it looks:

[sqlserver01]
Driver=/usr/local/lib/libtdsodbc.so
TDS_Version=4.2
Server=IP.Server
Port = 1433
Trace = Yes
Description=my description
Database="DB name"
# can't specify username and password for freetds

(where the IP.Server is the corresponding IP and "DB Name" is the name of the database). Then when I tried to run 

 ch1 <- odbcConnect(dsn="sqlserver01", uid="username", pwd="password")

(with my username and password, of course), I got the following error message: 

Warning messages:
1: In odbcDriverConnect("DSN=sqlserver01;UID=javier.zaurin;PWD=5R2fav3hgmG08zC_") :
  [RODBC] ERROR: state 01000, code 20013, message [FreeTDS][SQL Server]Unknown host     machine name.
2: In odbcDriverConnect("DSN=sqlserver01;UID=javier.zaurin;PWD=5R2fav3hgmG08zC_") :
  [RODBC] ERROR: state 08001, code 0, message [FreeTDS][SQL Server]Unable to connect to data source
3: In odbcDriverConnect("DSN=sqlserver01;UID=javier.zaurin;PWD=5R2fav3hgmG08zC_") :
  ODBC connection failed

Then I found this Installation of RODBC/ROracle packages on OS X Mavericks . I went to www.iodbc.org and downloaded the package for 10.6. Unzip it as as it is described there, put the header files (sql.h and sqlext.h) in the /usr/include directory. Since I could not find the file libiodbc.a file after unzipping the package I downloaded from here https://github.com/phracker/MacOSX-SDKs/blob/master/MacOSX10.6.sdk/usr/lib/libiodbc.a and put in the /usr/lib directory.

At this stage, sql.h and sqlext.h are in /usr/include and libiodbc.a in /usr/lib. Then I run:

install.packages("RODBC",type = "source")

and everything seemed to work. Then typed 

 ch1 <- odbcConnect(dsn="sqlserver01", uid="username", pwd="password")
 Warning messages:
 1: In odbcDriverConnect("DSN=sqlserver01;UID=javier.zaurin;PWD=5R2fav3hgmG08zC_") :
   [RODBC] ERROR: state 01000, code 20013, message [FreeTDS][SQL Server]Unknown host machine name.
 2: In odbcDriverConnect("DSN=sqlserver01;UID=javier.zaurin;PWD=5R2fav3hgmG08zC_") :
   [RODBC] ERROR: state 08001, code 0, message [FreeTDS][SQL Server]Unable to connect to data source
 3: In odbcDriverConnect("DSN=sqlserver01;UID=javier.zaurin;PWD=5R2fav3hgmG08zC_") :
   ODBC connection failed

If it is of any help, if I remove libtdsodbc.so or type something different at Driver=/usr/local/lib/libtdsodbc.so I got the error: 

 In odbcDriverConnect("DSN=sqlserver01;UID="username";PWD="password") :

[RODBC] ERROR: state IM003, code 1606396032, message [iODBC][Driver Manager]Specified driver could not be loaded

At this stage, I really don't know what to do next. Any help on how to get RODBC to work on Mavericks would he highly appreciated. Even if implies start from the beginning.

Community
  • 1
  • 1
Javier
  • 1,530
  • 4
  • 21
  • 48
  • Have you actually created a DSN using ODBC Manager? – joran Oct 20 '14 at 17:06
  • But is it not for MySQL? Do I need to do that even if I want to connect to a Microsoft SQL Server? – Javier Oct 20 '14 at 17:38
  • I have copied from `/usr/local/etc/` the freetds.conf file into .freetds.conf and change the content to `[sqlserver01]`; `host = 10.56.198.12#ntmachine.domain.com`; `port = 1433` `tds version = 7.0`. Now I get another error (at least something new): `In odbcDriverConnect("DSN=sqlserver01;UID=javier.zaurin;PWD=5R2fav3hgmG08zC_") : [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified ` – Javier Oct 20 '14 at 17:56
  • As far as I know, there should be no difference between iODBC or unixODBC as long as you pick one and set everything up around it from the beginning. It may be possible to set up and configure a DSN successfully without using ODBC Manager, but I have never done that. ODBC Manager is a utility for setting up and configuring _all_ DSNs to _all_ potential ODBC compliant db's. – joran Oct 20 '14 at 18:53
  • Personally, I have never used FreeTDS. I have always used the drivers from Actual Technologies (with both Oracle and MS SQL Server) with absolutely no problems. – joran Oct 20 '14 at 18:55

0 Answers0