4

I'm trying to connect to a microsoft sql database through R on a VM with ubuntu 16.04 as the OS. I have installed DBI, RMySQL, RODBC and odbc packages. I can connect to an oracle database.

Here is the command i tried using:

con <- dbConnect(odbc::odbc(), .connection_string = 'driver={SQL Server};
                          server=******;database=******;uid=*****;pwd=*******')

This is the error I get:

Error in odbc_connect(connection_string, timezone = timezone) : 
  nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

I don't know if my paths are setup correctly which may be the issue and I have the above command working correctly on a windows machine. Help is very muc appreciated! Thanks!

sessionInfo()
R version 3.2.3 (2015-12-10)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.1 LTS

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8       
 [4] LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C              
[10] LC_TELEPHONE=C             LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] tidyr_0.6.1       dplyr_0.5.0       ROracle_1.3-1     RODBC_1.3-14      zoo_1.7-14       
 [6] lubridate_1.6.0   reshape2_1.4.2    data.table_1.10.4 gdata_2.17.0      RMySQL_0.10.9    
[11] DBI_0.6          

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.10    lattice_0.20-33 gtools_3.5.0    assertthat_0.1  R6_2.2.0        grid_3.2.3     
 [7] plyr_1.8.4      odbc_1.0.1.9000 magrittr_1.5    stringi_1.1.2   blob_1.0.0      tools_3.2.3    
[13] stringr_1.1.0   tibble_1.2
Nikolay Nenov
  • 547
  • 1
  • 8
  • 27
tonyk
  • 348
  • 5
  • 22
  • Have you installed the ODBC Driver for SqlServer for Linux and do you have a corresponding entry in odbcinst.ini? – erg Mar 21 '17 at 12:39
  • I think I have installed the driver via: https://blogs.msdn.microsoft.com/sqlnativeclient/2017/02/04/odbc-driver-13-1-for-linux-released/. How do I create an entry in odbcinst.ini? – tonyk Mar 23 '17 at 00:34
  • Can you show us the content of `/etc/odbcinst.ini` and `/etc/odbc.ini`? And btw, I think the title is wrong: You do not want to connect to MySql, but to SqlServer (?) – erg Mar 23 '17 at 09:19
  • Yes, it's sql server I want to connect to, sorry for the confusion. Both of those files are empty for me – tonyk Mar 23 '17 at 17:08

0 Answers0