R: [unixODBC] [Driver Manager] Unable to open lib 'SQL Server': file not found

Question

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 get the following error in R

[unixODBC] [Driver Manager] Unable to open lib 'SQL Server': file not found

[unixODBC] [Driver Manager] Connection does not exist ODBC error in SQLDisconnect Failed to open data source. doTryCatch (return (expr), name, parentenv, handler): Failed to open the data source.

I installed the MSSQL and unixODBC driver on my Linux machine, and it is also redisplayed in the /etc/odbc.ini file.

Can someone please help me with this?

Answer 1

I got the same error when I put below code to connect to MSSQLSERVER

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

It throws me

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

why did they throw this error? Answer: when we cannot specify the correct ODBC version name in the driver value.

Where can we get the name of the ODBC driver version

inside the "/ etc" folder you will find the file "odbcinst.ini", 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 the ODBC driver name from here, it will be "ODBC Driver 17 for SQL Server" Then I change the 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

Answer 2

In my case, the answer Zaynul did not work, unfortunately. But it helped me find another way. I use sqlalchemy and can successfully create a connection by passing the path to the driver:

 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 this solution, as the driver file name may 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 too. =

EDIT 2: Another discovery, connecting to a named instance, for example, for those using SQL Express.

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