ERROR: CLI open cursor error: Could not receive the response, communication down ??

AJDELACRUZ0220 — Wed, 05 Jul 2017 14:16:37 GMT

Hello,

I am trying to extract big amount of data and I am getting this error:

Is there any option that I need to add on my SQL pass through?

proc sql;

connect to odbc as hindsight (datasrc='PostgreSQL30' user=xxxxxxx password="xxxxxx" dbmax_text=12000);

create table temp as

select * from connection to hindsight

(SELECT fact_event.fact_id,fact_event.region_cd,fact_event.transaction_dte,fact_event.roll_num,

fact_event.property_cd,fact_event.property_desc,fact_event.cty_mun,value_dim.value_rounded, comp_dim.value_component_id,

comp_dim.transaction_dte, comp_dim.unadjusted, comp_dim.value, comp_dim.valuation_type, comp_dim.priority,

comp_dim.property_adjusted, comp_dim.component_adjusted, comp_dim.component_id, comp_dim.building_id_number,

comp_dim.variables, comp_dim.active

FROM hindsight_3.fact_event fact_event

INNER JOIN

(SELECT roll_num, max(transaction_dte) as max_transaction_dte

FROM hindsight_3.fact_event fact_event

WHERE fact_event.event_type='CMV' and fact_event.status_cd='A'

GROUP BY roll_num) transaction_dte on fact_event.roll_num=transaction_dte.roll_num and fact_event.transaction_dte=transaction_dte.max_transaction_dte

LEFT JOIN hindsight_3.property_dim property_dim ON fact_event.fact_id = property_dim.fact_id

LEFT JOIN hindsight_3.value_component_dim comp_dim ON fact_event.fact_id = comp_dim.fact_id

LEFT JOIN hindsight_3.value_dim value_dim ON fact_event.fact_id = value_dim.fact_id

WHERE ((substring(fact_event.region_cd,1,1) = '0' and substring(fact_event.region_cd,2,1) = '09') or fact_event.region_cd = '09')

and fact_event.event_type='CMV' and fact_event.status_cd='A' and property_dim.status_cd='A'

ORDER BY fact_event.roll_num);

quit;

Thank you,

Ave

Re: ERROR: CLI open cursor error: Could not receive the response, communication down ??

JBailey — Fri, 14 Jul 2017 18:15:37 GMT

Hi @AJDELACRUZ0220

Try adding the conopts= option in your connection string.

It would look like this:

connect to odbc as hindsight (datasrc='PostgreSQL30' user=myuser password="mypassword" dbmax_text=12000 conopts="UseDeclareFetch=1;Fetch=1000;UseServerSidePrepare=1;")

If this doesn't work simplify the CONOPTS= option, like this:

connect to odbc as hindsight (datasrc='PostgreSQL30' user=myuser password="mypassword" dbmax_text=12000 conopts="UseDeclareFetch=1;")

Hopefully one of these will help you.

Best wishes,
Jeff

topic Re: ERROR: CLI open cursor error: Could not receive the response, communication down ?? in SAS Programming

ERROR: CLI open cursor error: Could not receive the response, communication down ??

Re: ERROR: CLI open cursor error: Could not receive the response, communication down ??