Hi, all.
In case you all were not able to get DSN-less connections working and need additional clarification, I have included a section from an email which I recently sent to a customer.
I think what may throw some folks off is that Progress/Data Direct does DSN-less connections a bit differently than when using a unixODBC driver manager. Whereas you can do DSN-less connections when using a unixODBC driver manager and not reference any INI files at all, Progress/Data Direct requires that you at least reference an odbcinst.ini file entry.
**
When doing DSN-less connections with Progress/Data Direct ODBC client components (we bundle these in with SAS ACCESS TO MS SQL Server), you do not have to have an odbc.ini file with DSN entries for SQL Server data bases but you must create an entry in an odbcinst.ini and you must set the ODBCINST environment to point to this odbcinst.ini file.
I have set up a test environment on a Linux server here and the following shows how I got this to work. I have set things up so that I can use either reference DSNs in an odbc.ini file or do DSN-less connections.
My odbc.ini file looks like this:
[ODBC Data Sources]
sqlserver=SAS Institute, Inc 7.1.5 SQL Server Wire Protocol
[ODBC]
IANAAppCodePage=4
InstallDir=/users/sasswl/acc_mssql_dsnless/sqls715
Trace=0
TraceFile=/tmp/odbctrace.out
TraceDll=/users/sasswl/acc_mssql_dsnless/sqls715/lib/S0trc27.so
[sqlserver]
Driver=/users/sasswl/acc_mssql_dsnless/sqls715/lib/S0sqls27.so
Description=SAS Institute, Inc 7.1.5 SQL Server Wire Protocol
AnsiNPW=1
AuthenticationMethod=1
Database=users
EnableQuotedIdentifiers=0
HostName=blackfin.na.sas.com
Language=
PortNumber=1433
My odbcinst.ini looks like this:
[ODBC_Drivers]
sqlserver_dsnless=SAS Institute, Inc 7.1.5 SQL Server Wire Protocol
[ODBC]
#This section must contain values for DSN-less connections
#if no odbc.ini file exists. If an odbc.ini file exists,
#the values from that [ODBC] section are used.
[sqlserver_dsnless]
Driver=/users/sasswl/acc_mssql_dsnless/sqls715/lib/S0sqls27.so
I set ODBC-related environment variables in the sasenv_local file in the $SASROOT/bin directory on Linux like this:
export ODBCHOME=/users/sasswl/acc_mssql_dsnless/sqls715
export ODBCINI=$ODBCHOME/odbc.ini
export ODBCINST=$ODBCHOME/odbcinst.ini
export LD_LIBRARY_PATH=$ODBCHOME/lib:$LD_LIBRARY_PATH
The following excerpt from my SAS Log shows how I can successfully use SAS ACCESS TO MS SQL SERVER to do either DSN or DSN-less connections in the same SAS session.
1? libname sqltest1 sqlsvr user=xxxx pw=xxxx dsn=sqlserver;
NOTE: Libref SQLTEST1 was successfully assigned as follows:
Engine: SQLSVR
Physical Name: sqlserver
2? libname sqltest2 sqlsvr noprompt="Driver=sqlserver_dsnless;AnsiNPW=1;AuthenticationMethod=1;Database=users;EnableQuotedIdentifiers=0;HostName=blackfin.na.sas.com;PortNumber=1433;UID=xxxx;PWD=xxxx";
NOTE: Libref SQLTEST2 was successfully assigned as follows:
Engine: SQLSVR
Physical Name:
NOTES:
Again, I set things up so that I could do either DSN or DSN-less connections in the same SAS session.
If you are only doing DSN-less connections, you could do away with the odbc.ini file altogether and only reference an odbcinst.ini file by only setting the ODBCINST environment variable...BUT the Progress/Data Direct driver manager relies on an [ODBC] stanza and if you are not referencing an odbc.ini (where you normally include this stanza) you must include this [ODBC] stanza in your odbcinst.ini file.
... View more
