topic Re: CLI execute error: Server communication failure., error in libname statement when export to Acce in SAS Viya

CLI execute error: Server communication failure., error in libname statement when export to Access

Ryanb2 — Wed, 20 Mar 2024 16:00:01 GMT

I've been regularly able to export a SAS dataset into an Access database located on our local network from SAS Viya using PC file server. However, now I'm getting the following error messages when I export the usual SAS dataset (MYDATA):

"ERROR: CLI disconnect failed: Server communication failure."

"ERROR: Error in the LIBNAME statement."

The strange thing is I can successfully export sashelp.class into the same Access database without error. Originally I thought the issue was that I converted string into numeric and it wasn't able to overwrite the Access table with different data types for the same column names but I compared the proc contents from the exported dataset to the imported dataset and variable types aligned. Also, I'm not able to export MYDATA into a new table (i.e., no overwrite) in the Access database like I was able to do with sashelp.class. Meaning the issue appears to have something to do with MYDATA. The only change to MYDATA from working to not working was new records and some new values in existing records, but I'm not sure how to investigate the problem here. Any ideas?

Re: CLI execute error: Server communication failure., error in libname statement when export to Acce

SASKiwi — Wed, 20 Mar 2024 19:05:33 GMT

Can you please post the complete SAS log of your program, not just the errors. Also please post the complete SAS log of the export that works.

Re: CLI execute error: Server communication failure., error in libname statement when export to Acce

Ryanb2 — Wed, 20 Mar 2024 19:31:26 GMT

Here's the log. I redacted some of it but please keep in mind I'm able to export a different SAS dataset to the same Access database using the same redacted information. I appreciate your help with this!

1 %studio_hide_wrapper;

83 PROC EXPORT DATA=NAT_CERT_EXPORT

84 OUTTABLE='VV_NAT'

85 DBMS=ACCESSCS REPLACE LABEL;

86 DATABASE="<path redacted but works when exporting other SAS datasets>\DIRPS_birth_&YOI..accdb";

87 SERVER="<server redacted but works when exporting other SAS datasets>";

88 SERVERUSER='<user redacted but works when exporting other SAS datasets>';

89 SERVERPASS=XXXXXXXXXXXXXXXXXX;

90 DBDSOPTS='INSERTBUFF=25';

91 RUN;

ERROR: A socket routine returned error: The connection was reset by a peer.

ERROR: A socket routine returned error: Broken pipe

ERROR: CLI execute error: Server communication failure.

ERROR: A socket routine returned error: Broken pipe

ERROR: CLI disconnect failed: Server communication failure.

ERROR: A socket routine returned error: Broken pipe

ERROR: CLI disconnect failed: Server communication failure.

ERROR: Error in the LIBNAME statement.

ERROR: Export unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 3.21 seconds

cpu time 0.10 seconds

93 %studio_hide_wrapper;

104

105

Re: CLI execute error: Server communication failure., error in libname statement when export to Acce

Ryanb2 — Wed, 20 Mar 2024 19:55:29 GMT

And here's log output for the code that works, again redacted.

1 %studio_hide_wrapper;

83 PROC EXPORT DATA=SASHELP.Class

84 OUTTABLE='test'

85 DBMS=ACCESSCS REPLACE LABEL;

86 DATABASE="<redacted but same as one that doesnt work >\DIRPS_birth_&YOI..accdb";

87 SERVER="<redacted but same as one that doesnt work>";

88 SERVERUSER='<redacted but same as one that doesnt work>';

89 SERVERPASS=XXXXXXXXXXXXXXXXXX;

90 DBDSOPTS='INSERTBUFF=25';

91 RUN;

NOTE: "test" table was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 2.01 seconds

cpu time 0.06 seconds

93 %studio_hide_wrapper;

104

105

Re: CLI execute error: Server communication failure., error in libname statement when export to Acce

SASKiwi — Wed, 20 Mar 2024 20:53:46 GMT

This SAS Note is a close match: https://support.sas.com/kb/46/541.html

Check what version of the MS Access Database Engine you have installed on your SAS PC Files Server. Going to a more recent version may help. We started trying the 2016 version recently and haven't had any problems although we weren't using it with the SAS PC Files Server.