Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

R: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

setwd("/mnt/mountpoint/abc/")
sqlServerConnString <- "SERVER=server;DATABASE=sqldwdb;UID=xyz;PWD=abc;"
sqlServerDataDS <- RxSqlServerData(sqlQuery = "SELECT * FROM xyz",
                               connectionString = sqlServerConnString)
sqlServerDataDF <- rxImport(sqlServerDataDS)

This is my code. I am getting the followin error in R

[unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

[unixODBC][Driver Manager]Connnection does not exist ODBC Error in SQLDisconnect Could not open data source.Error in doTryCatch(return(expr), name, parentenv, handler) : Could not open data source.

I have installed MSSQL and unixODBC driver on my linux machine and it is getting refelected in /etc/odbc.ini file too

Can someone please help me in this?

like image 798
Sonal Avatar asked Apr 25 '17 09:04

Sonal


People also ask

How do I setup an ODBC connection in R?

“Data Source Name”) can simplify your code configuration in R. STEP 1: Search “ODBC” in the Start Menu search and open “ODBC Data Source Administrator (64-bit)”. Step 2: Select “Add” under the “User DSN” tab. Step 3: Select the corresponding ODBC driver for which you wish to set up a data source and Click “Finish”.

How do I check my ODBC driver version?

To check the ODBC SQL Server driver version (32-bit ODBC)In the ODBC Data Source Administrator, click the Drivers tab. Information for the Microsoft SQL Server entry is displayed in the Version column.


2 Answers

I got the same error when i put below code for connection to MSSQLSERVER

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=SQL Server;Server=192.168.76.60; Database=kaggle;Uid=sa; Pwd=1234")

It throws to me

[unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

why this Error thrown? Answer: when we fail to put proper ODBC version name on Driver value.

From where we can get Driver ODBC version name

inside "/etc" folder you will find "odbcinst.ini" file open it and check the version name

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
UsageCount=1

so i got ODBC Driver name from here , it will be "ODBC Driver 17 for SQL Server" Then i modify my connection string

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 17 for SQL Server;Server=192.168.76.60; Database=kaggle;Uid=sa; Pwd=1234")

And it works fine

like image 103
Zaynul Abadin Tuhin Avatar answered Oct 17 '22 15:10

Zaynul Abadin Tuhin


In my case Zaynul answer didn't worked, unfortunately. But it helped me to find another way. I am using sqlalchemy and I could sucessfully create a connection passing the driver path:

engine = create_engine('mssql+pyodbc://sa:******@localhost:1433/dbCVServer?driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1', echo=True)

engine.execute('select 1')

EDIT: I was not satisfied with that solution, since the file name of driver could change, so I managed to change the library used to connect do pymssql.

engine = create_engine('mssql+pymssql://sa:******@localhost:1433/dbCVServer')

and it worked well too. =]

EDIT 2: Another discovery, connecting to a named instance, for those using sql express, for instance.

engine = create_engine('mssql+pymssql://mssql+pymssql://localhost\SQLEXPRESS/dbCVServer')

like image 29
Wagner Bertolini Junior Avatar answered Oct 17 '22 17:10

Wagner Bertolini Junior