topic Re: SAS connecting to Microsoft SQL Server using DataDirect in Administration and Deployment

SAS connecting to Microsoft SQL Server using DataDirect

woo — Fri, 16 Mar 2018 14:23:18 GMT

I am trying connecting to SQL server database.

Have unixODBC driver manager installed + ODBC DataDirector connector installed on linux machine (SAS 9.4).

I can connect using isql command, but not with SAS job,

$isql -v [data srouce name] [user] [pwd] <- can connect using this command.

I have ODBCINI /ODBCINST/LD_LIBRARY_PATH set up correctly under my home dir.

libname test odbc datasrc="datasourcenamefromodbcini" user="user1" pw="password";
ERROR: CLI error trying to establish connection: [iODBC][Driver Manager]Data source name not found and no default driver specified.
Driver could not be loaded
ERROR: Error in the LIBNAME statement.

Am I missing anything? or different libname statement needs to be used? Thank you....

Re: SAS connecting to Microsoft SQL Server using DataDirect

Selvaraj — Fri, 16 Mar 2018 14:36:50 GMT

Ensure LD_LIBRARY_PATH is correctly setup in the sasenv_local file under SAS Foundation directory.

Re: SAS connecting to Microsoft SQL Server using DataDirect

woo — Fri, 16 Mar 2018 14:43:44 GMT

Thanks Selvaraj. Do we need restart after adding it under sasenv_local file?

Re: SAS connecting to Microsoft SQL Server using DataDirect

Selvaraj — Fri, 16 Mar 2018 14:52:00 GMT

Did you mean restarting the services? That won't be necessary. A new session would suffice.

Re: SAS connecting to Microsoft SQL Server using DataDirect

woo — Fri, 16 Mar 2018 15:18:53 GMT

I am still receiving same error,

My LD_LIBRARY_PATH looks like this,

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/datadirect-install-dir/lib

I started fresh session and tested. may be libname statement defined different way for odbc datadirect?

Re: SAS connecting to Microsoft SQL Server using DataDirect

SimonDawson — Sun, 18 Mar 2018 12:02:37 GMT

The runtime linker is loading the iODBC driver manager instead of the DataDirect one because the search path for the linker has the iODBC location ahead of the DataDirect one.

For details on how the linker works and how it finds dynamic libraries this Linux man page covers it all.

http://man7.org/linux/man-pages/man8/ld.so.8.html

Putting your DataDirect path first on the LD_LIBRARY_PATH will likely get you a step further in this setup.

export LD_LIBRARY_PATH=/opt/datadirect-install-dir/lib:$LD_LIBRARY_PATH

Re: SAS connecting to Microsoft SQL Server using DataDirect

woo — Fri, 23 Mar 2018 20:03:27 GMT

Thanks Simon, worked fine.