Both SAS/ACCESS Interface to Greenplum and SAS/ACCESS Interface to Microsoft SQL Server deliver a branded DataDirect ODBC driver manager and driver. If you have both of these products, you might need to combine the configuration files into a single file for better management. The following steps and examples are based on the 7.0.1 DataDirect ODBC Drivers, which can be downloaded from the SAS Support Website by clicking the Downloads tab at the top of this note.

The instructions below use sample directories and configuration file entries. Modify these to your environment.

1. Install the driver components for SQL Server into a directory called /opt/sqlserver.
2. Install the driver components for Greenplum into a directory called /opt/greenplum.
3. Create a third directory called /opt/odbcconfig.
4. Copy the odbc.ini and odbcinst.ini files from /opt/greenplum into the /opt/odbcconfig directory.
5. To avoid confusion as to which files are the master files, move odbc.ini to odbc.ini.orig and odbcinst.ini to odbcinst.ini.orig in both the /opt/sqlserver and /opt/greenplum directories.
6. Edit the /opt/odbcconfig/odbc.ini by copying in the entries from the /opt/sqlserver/odbc.ini file.
7. Edit the /opt/odbcconfig/odbcinst.ini by copying in the following two entries from the /opt/sqlserver/odbcinst.ini and renaming them as below:
   • Copy and rename "SAS Institute Inc. 7.0 SQL Server Legacy Wire Protocol" to "SQLServer_Legacy."
   • Copy and rename "SAS Institute Inc. 7.0 SQL Server Wire Protocol" to "SQLServer."
8. Set your environment variables (normally in the sasenv_local file) to reflect these changes:
   • export ODBCINI=/opt/odbcconfig/odbc.ini
   • export ODBCINST=/opt/odbcconfig/odbcinst.ini
   • export LD_LIBRARY_PATH=/opt/sqlserver/lib:/opt/greenplum/lib:$LD_LIBRARY_PATH

Click the Full Code tab in this note to see examples of the odbc.ini and odbcinst.ini files showing what the /opt/odbcconfig files might look like after completing the steps listed above.

Listed below are some connection strings that can be used based on the configuration files:

/* SQL Server Wire Protocol Driver with a defined DSN in the odbc.ini */
libname sqwirdsn sqlsvr dsn=MyDSN1 user=myuser password=mypass schema=myschema;

/* SQL Server Wire Protocol Driver DSN-less connection */
libname sqwireno sqlsvr noprompt='Driver=SQLServer;Address=machine1.reg.company.com,1433;Database=users;
       UID=myuser;PWD=mypass;' schema=myschema;

/* SQL Server Legacy Driver with a defined DSN in the odbc.ini*/
libname sqlegdsn sqlsvr dsn=MyDSN2 user=myuser password=mypass schema=myschema;

/* SQL Server Legacy Driver DSN-less connection */
libname sqllegno sqlsvr noprompt='Driver=SQLServer_Legacy;Address=machine2.reg.company.com,1433;Database=users;
       UID=myuser;PWD=mypass;' schema=myschema;
       
/* Greenplum Driver with a defined DSN in the odbc.ini */
libname gpdsn greenplm dsn=MyDSN3 user=myuser password=mypass schema=myschema;

/* Greenplum Driver DSN-less connection */
libname gpdsnles greenplm server="machine3.reg.company.com" port=5432 db=test user=myuser password=mypass
       schema=myschema;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

ODBC.INI
========
[ODBC Data Sources]
MyDSN3=SAS ACCESS to Greenplum
MyDSN1=SQLServer
MyDSN2=SQLServer_Legacy

[ODBC]
IANAAppCodePage=4
InstallDir=/opt/greenplum
Trace=0
TraceFile=odbctrace.out
TraceDll=/opt/greenplum/lib/S0trc26.so

[MyDSN3]
Driver=/opt/greenplum/lib/S0gplm26.so
Description=SAS Institute Inc. 7.0 Greenplum Wire Protocol
AlternateServers=
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=test
DefaultLongDataBuffLen=2048
EnableDescribeParam=0
EnableKeysetCursors=0
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=machine3.reg.company.com
InitializationString=
KeyPassword=
KeysetCursorOptions=0
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=5432
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
XMLDescribeType=-10

[MyDSN1]
Driver=/opt/sqlsvr/lib/S0sqls26.so
Description=SAS Institute Inc. 7.0 SQL Server Wire Protocol
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationName=
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadFieldDelimiter=
BulkLoadOptions=2
BulkLoadRecordDelimiter=
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=users
EnableBulkLoad=0
EnableQuotedIdentifiers=0
EncryptionMethod=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=machine1.reg.company.com
HostNameInCertificate=
InitializationString=
Language=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
PacketSize=-1
Password=
Pooling=0
PortNumber=1433
QueryTimeout=0
ReportCodePageConversionErrors=0
SnapshotSerializable=0
TrustStore=
TrustStorePassword=
ValidateServerCertificate=1
WorkStationID=
XMLDescribeType=-10

[MyDSN2]
Driver=/opt/sqlsvr/lib/S0msss26.so
Description=SAS Institute Inc. 7.0 SQL Server Legacy Wire Protocol
Address=machine2.reg.company.com, 1433
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=users
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
LoadBalancing=0
LogonID=
Password=
QuotedId=No
ReportCodepageConversionErrors=0
SnapshotSerializable=0


ODBCINST.INI
============
[ODBC Drivers]
SAS ACCESS to Greenplum=Installed
SQLServer=Installed
SQLServer_Legacy=Installed

[ODBC Translators]
OEM to ANSI=Installed

[Administrator]
HelpRootDirectory=

[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.

[SAS ACCESS to Greenplum]
Driver=/opt/greenplum/lib/S0gplm26.so
Setup=/opt/greenplum/lib/S0gplm26.so
APILevel=0
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=
SQLLevel=0

[SQLServer]
Driver=/opt/sqlsvr/lib/S0sqls26.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=
SQLLevel=0

[SQLServer_Legacy]
Driver=/opt/sqlsvr/lib/S0msss26.so
Setup=/opt/sqlsvr/lib/S0msss26s.so
APILevel=2
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=
SQLLevel=0

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

You can download DataDirect's ODBC drivers from the following page:

ftp.sas.com/techsup/download/hotfix/datadirect_sqlserver.html

ftp.sas.com/techsup/download/hotfix/datadirect_greenplum.html

Operating System and Release Information