About Domino
Domino Data LabKnowledge BaseData Science BlogTraining

Access Data Sources from SAS workspaces

You can access Microsoft SQL Server and Oracle Data Sources from your SAS Workspaces. Domino uses the SAS/ACCESS interface to ODBC to access these Data Sources.

Scope limitations

  • Domino only supports Microsoft SQL Server and Oracle Data Sources through the SAS/ACCESS interface to ODBC.

  • Parameterized queries aren’t supported.

Prerequisites

  • Your Data Sources must be created before you launch your Workspace. Restart your Workspace to see new Data Sources.

  • The environment must have the Arrow Flight SQL ODBC driver installed.

    To install the Arrow Flight SQL ODBC driver, add the following lines to your Dockerfile.

    USER root

# ODBC Driver for Arrow Flight SQL

RUN curl -s -o arrow-flight-sql-odbc-driver.rpm https://download.dremio.com/arrow-flight-sql-odbc-driver/0.9.1/arrow-flight-sql-odbc-driver-0.9.1.168-1.x86_64.rpm

RUN dnf clean packages
RUN dnf config-manager --add-repo https://repo.almalinux.org/almalinux/8/BaseOS/x86_64/os/
RUN dnf install -y --nogpgcheck --repo "repo.almalinux.org_almalinux_8_BaseOS_x86_64_os_" libnsl
RUN sudo dnf -y --nogpgcheck localinstall arrow-flight-sql-odbc-driver.rpm

RUN dnf clean all && rm arrow-flight-sql-odbc-driver.rpm

# Configure SAS odbc manager
RUN echo "export ODBCSYSINI=/etc" >> /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh && \
    echo "export ODBCINI=odbc.ini" >> /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh && \
    echo "export ODBCINSTINI=odbcinst.ini" >> /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh && \
    echo "export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib64" >> /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh

USER domino

Code snippet

The following SAS snippet shows you how to access Domino Data Sources using the SAS/ACCESS interface to ODBC and SQL pass-through to connect and send statements directly to the DBMS. Enter your Domino Data Source name in the datasrc field.

options set=EASYSOFT_UNICODE=YES;

proc sql;
connect to odbc as mycon(datasrc="sqlserver");

select *
   from connection to mycon
      (select top 10 * from yellow_cab);

disconnect from mycon;
quit;

Troubleshooting

Troubleshoot SAS errors by viewing the error message and stack trace in the SAS Studio UI log.

Here are some common errors and solutions:

  • If a Data Source is created after launching the Workspace, it won’t generate an ODBC configuration causing the error, "Data source name not found and no default driver specified". To resolve the error, restart the Workspace.

  • The error "Flight returned internal error" indicates issues with the SQL statement or the use of an invalid SQL dialect.

Next steps