sasodbcconnection-stringamazon-redshiftmsdasql

ODBC Connection String to Amazon Redshift

Is there a way to specify in a connection string the settings of 'Additional Options' ?

I am trying to create a connection string for accessing Amazon Redshift from SAS with SAS/Access to OleDb installed.

Because I only have access to OleDb I will have to use OleDb provider MSDASQL to connect to ODBC.

libname outhere oledb schema=ProjectXYZ init_string=
"
Provider=msdasql;
Driver={Amazon Redshift (x64)};
Server=blah.blah.blah.redshift.amazonaws.com;
Database=minions;
UID=bigboss;
PWD=boomboom;
Port=5439;
";

This mostly works and SAS Explorer lists all the tables in the database (even the ones not in ProjectXYZ {weird}). Data step can run through the data.

The problem is that when I use ViewTable to look at the data there is an error

NOTE: Table has been opened in browse mode.
ERROR: Open cursor error: ICommand::Execute failed. : [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a que

ViewTable can be made to work by using ODBC Admin to create a UserDSN based on {Amazon Redshift (x64)} with the drivers additional option "Single Row Mode" selected in the Setup dialog box.

I couldn't find any documentation on what the name=value pairs should be to specify the selection of any of these

Additional Options:

Solution

  • Here is the most comprehensive list of options I could find: http://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-options.html

    Here are some of the ODBC options mentioned in the document:

    When UseDeclareFetch=0 and SingleRowMode=0, then the driver retrieves the entire query result into memory.