ERROR: CLI prepare error: [SAS][ODBC Redshift Wire Protocol driver]Socket closed.

[Mohan_Rang]

Hi SAS experts, We are working on SAS 9.4 for ETL and Redshift for data warehousing. When we sometimes read data in SAS from Redshift or write data to Redshift for small tables, we get the following error. This happens even for small tables when we attempt to get row count using PROC SQL in SAS. But for large tables (>10 M), this is always the case for both reading and writing. Can you please help us troubleshoot the issue?

ERROR: CLI prepare error: [SAS][ODBC Redshift Wire Protocol driver]Socket closed.
SQL statement: SELECT * FROM "schema".tblname.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

[ballardw]

It may help to show the code submitted.

The quotes around schema in the from look a bit suspect and may indicate an improper libname creation/connection setting.

[Mohan_Rang]

It doesn't fail all the time. Sometimes the query goes through successfully and sometimes it fails with the socket error. Please see the query used below.

PROC SQL;
SELECT COUNT(*) FROM SCHEMA.TBLNAME
;
QUIT;

[SASKiwi]

You would be best to progress this in a track with SAS Tech Support. Perhaps a more recent Redshift ODBC driver version might help.

There's a few Redshift ODBC driver errors documented on SAS Support but none reference the error you are getting.