topic Re: ERROR: CLI prepare error: [SAS][ODBC Redshift Wire Protocol driver]Socket closed. in Administration and Deployment

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

Mohan_Rang — Fri, 15 Oct 2021 18:55:08 GMT

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

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

ballardw — Fri, 15 Oct 2021 19:26:41 GMT

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.

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

Mohan_Rang — Fri, 15 Oct 2021 19:36:33 GMT

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;

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

SASKiwi — Fri, 15 Oct 2021 23:12:53 GMT

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.