0

I have downloaded and configured freeTDS and unixODBC as proof I have connected with tsql and isql. When I download RODBC from RStudio and try and connect with mssql server I get the following error 

[RODBC] ERROR: state IM002, code 1606386064, message [iODBC][Driver Manager]
Data source name not found and no default driver specified. Driver could not be loaded

From the error I note that iODBC is still being used instead of unixODBC. I then uninstall RODBC from RStudio and go to terminal to try and force RODBC to install using unixODBC by using

export DYLD_LIBRARY_PATH=/usr/local/lib:${DYLD_LIBRARY_PATH}
R CMD INSTALL /Users/<username>/Downloads/RODBC_1.3-13.tar.gz

So the computer knows to install in the location of unixODBC.

I get the same error as above. I am not sure if I am doing something wrong or if there is another way to make R use unixODBC?

I am also open to a suggestion on how to make iODBC to work. I am new at this whole connecting to servers business.

Andrie
  • 176,377
  • 47
  • 447
  • 496
Bryan
  • 9
  • 1
  • Without seeing the code you used, it's very difficult to know what advice to give you. Are you using a data source name with `odbcConnect` or a connection string with `odbcDriverConnect`? If a DSN, what is the configuration of the DSN? – Benjamin Jun 02 '16 at 23:20
  • I used this to try and connect `library("RODBC", lib.loc="/usr/local/lib/R/3.3/site-library")``con <- odbcConnect("xxx.xxx.x.xx", uid="", pwd="")`I named the DSN with ip address of the computer I am trying to connect to, it is a little funky but seemed to work when connecting from the terminal. – Bryan Jun 03 '16 at 14:16
  • Well there's something I've never tried to do. If you don't mind my asking, why not configure the DSN on your machine? Connecting to another computer in order to use it DSN seems like adding an unnecessary step. – Benjamin Jun 03 '16 at 14:29
  • I may be confused as I am a new to all of this but this is what I have on my laptop (mac). I installed freetds with homebrew in the freetds.confg file I have `# A typical Microsoft server [abc.afg.a.cf] host = abc.afg.a.cf port = xxxxx tds version = 7.4 client charset = UTF-8` I then installed unixODBC with homebrew and configured the odbc.ini and the odbcinst.ini , as follows – Bryan Jun 03 '16 at 14:57
  • odbc.ini `[abc.afg.a.cf] Description = Test to SQLServer Driver = FreeTDS Trace = Yes TraceFile = /tmp/sql.log Database = AAAA Servername = abc.afg.a.cf UserName = Password = Port = xxxxx Protocol = 7.4 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No` – Bryan Jun 03 '16 at 15:02
  • odbcinst.ini `[FreeTDS] Description = TD Driver (MSSQL) Driver = /usr/local/lib/libtdsodbc.so Setup = /usr/local/lib/libtdsodbc.so UsageCount=1` – Bryan Jun 03 '16 at 15:04
  • All in hopes to connect to a MS machine using SQLEXPRESS – Bryan Jun 03 '16 at 15:06

1 Answers1

0

For OS 10.13.3, I got r RODBC and python pymssql working with MS SQL using the following commands:

brew install homebrew/core/freetds091 --with-unixodbc.  
brew link --force freetds@0.91  
pip install pymssql

and then making the following edits to /Users//.odbc.ini 

[SQLServer]   
Description = "my server"  
Driver = /usr/local/lib/libtdsodbc.so  
Trace=No  
Server = "my server"  
Port=1433   
TDS_Version=8.0  
Database= "my db"
Bryan Prazen
  • 323
  • 3
  • 7