topic Re: How to set up buffersize - Vertica odbc connection in SAS Programming

How to set up buffersize - Vertica odbc connection

Yurie — Mon, 28 Aug 2017 16:59:20 GMT

When I run a proc contents command, I received the following error message:

Code:

libname dw odbc datasrc="Vertica" user=myusername password=mypassword schema=ods_abcd;

proc contents data=dw.table; run;

Error message:

Error: CLL prepare error: [Vertica] [Vertica DSII] (10) An error occurred during query preparation: Multiple commands cannot be active on the same connnection. Cionsider increasing resultbuffersize or fetching all results before initiating another command. SQL statement: select * from ods_abcd.table.

I used the Vertica connected to my database through ODBC (I use SAS 9.4 SAS server). However, after I get the all the tables, I even could not open the tables in the library and give me the above error message. Any suggestions will be very appreciate!

Regards,

Yurie

Re: How to set up buffersize - Vertica odbc connection

Oligolas — Tue, 29 Aug 2017 13:12:48 GMT

Hi,

DBMAX_TEXT=32767 maybe?

libname dw odbc datasrc="Vertica" user=myusername password=mypassword schema=ods_abcd DBMAX_TEXT=32767;

Re: How to set up buffersize - Vertica odbc connection

Yurie — Fri, 01 Sep 2017 19:56:10 GMT

Thanks! I tried add DBMAX_text=32767. The libref name was successfully assigned with Engine: ODBC and phsycial Name: Vertica. However, when I test the table using proc freq, I still get the same error message.

Re: How to set up buffersize - Vertica odbc connection

ChrisBrooks — Fri, 01 Sep 2017 20:50:27 GMT

Have you tested if the SAS/ACESS Connector to Vertica is installed as in this guide http://my.vertica.com/docs/Ecosystem/SASConnectionGuide.pdf

Re: How to set up buffersize - Vertica odbc connection

richardmgb — Fri, 13 Aug 2021 21:46:10 GMT

Did anyone ever find a solution to this issue?

Re: How to set up buffersize - Vertica odbc connection

jimbarbour — Fri, 13 Aug 2021 23:50:37 GMT

@richardmgb,

If you have an issue, let's please open a new topic. This topic from 2017 is dead, dead, dead. 🙂

thanks,

Jim