Hello,
I am trying to extract big amount of data and I am getting this error:
ERROR: CLI open cursor error: Could not receive the response, communication down ??
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
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.