topic Re: REQEXHAUST error mesage in proc sql in SAS Programming

REQEXHAUST error mesage in proc sql

EmDy — Wed, 22 Mar 2017 22:19:04 GMT

I am using proc sql to pull records from Teradata into a SAS data set. Sometimes, I get this error, sometimes, I do not. What does the following message mean?

ERROR: Teradata row not delivered (trget): CLI2: REQEXHAUST(307): Request data exhausted. .
NOTE: Compressing data set MYTESTDATASET decreased size by 30.91 percent.
Compressed is 1817527 pages; un-compressed would require 2630728 pages.

ERROR: ERROR: Commit: Session forced off by PMPC or gtwglobal or security violation.
ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

Here is a snippet of the code:

proc sql;
connect to TERADATA (user = "XXXXXXX" password = "&password" server=XXXX mode=teradata);

create table MYTESTDATASET as

select patron_id,
,join_date format=monyy7. as membership_date
,(case when member_age LE 18 then 1 else 0 end) as youth_discount
from abcd.GYM_MEMBERSHIP
where join_date between '01jan2010'd and '30jun2010'd
;

disconnect from TERADATA;

Even with the error message, a data set is created, with approximately 1.5 billion records.

I am using SAS v9.3.

Please advise. Thank you.

Re: REQEXHAUST error mesage in proc sql

Patrick — Thu, 23 Mar 2017 08:41:02 GMT

Looks to me like you best raise a SAS Tech Support track for this one.

https://support.sas.com/techsup/contact/

Let us know how you go.

Re: REQEXHAUST error mesage in proc sql

Peter_C — Fri, 24 Mar 2017 00:10:32 GMT

In the code provided I don't see where the connection is used (I see only where it is made)

Re: REQEXHAUST error mesage in proc sql

Deyxon — Thu, 22 Aug 2019 00:09:05 GMT

I have the same problem, had you can solved this issue?

i got this error:

1 Sistema SAS martes, 20 de agosto de 2019 13:40:00

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Programa';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='C:\Users\eb6265\OneDrive - Cencosud\Proyectos\3952 - Forecast Ventas\Bck_forecast\Archivos
5 ! SAS\Alisado\Project_Suavizado.egp';
6 %LET _CLIENTPROJECTPATHHOST='G500603NT1529';
7 %LET _CLIENTPROJECTNAME='Project_Suavizado.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26
27
28 proc sql;
29
30 /*Obtiene los datos de venta a nivel semanal desde teradata
31 (acumula la venta de 61 semanas) y los aloja en la tabla repo.item_qty_week_sales en SAS */
32
33 connect to teradata (user="user1@LDAP" pass=XXXXXXXXXXXXXX tdpid='10.10.10.11' mode=teradata ) ;
34
35 create table repo.item_qty_week_sales_2 as select * from connection to teradata
36 ( select top 100000000 * from DL_CS_BI.ITEM_QTY_WEEK_SALES
37 WHERE calendar_week_id
38 BETWEEN 201824
39 AND 201925
40 ORDER BY 1);
ERROR: Teradata row not delivered (trget): CLI2: REQEXHAUST(307): Request data exhausted. .

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41
42 GOPTIONS NOACCESSIBLE;
43 %LET _CLIENTTASKLABEL=;
44 %LET _CLIENTPROCESSFLOWNAME=;
45 %LET _CLIENTPROJECTPATH=;
46 %LET _CLIENTPROJECTPATHHOST=;
47 %LET _CLIENTPROJECTNAME=;
48 %LET _SASPROGRAMFILE=;
49 %LET _SASPROGRAMFILEHOST=;
50
51 ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDIMIENTO SQL used (Total process time):
2 Sistema SAS martes, 20 de agosto de 2019 13:40:00

real time 4:03:53.59
cpu time 1:12.43

51 ! run;
52 ODS _ALL_ CLOSE;
53
54
55 QUIT; RUN;

Re: REQEXHAUST error mesage in proc sql

Peter_C — Thu, 22 Aug 2019 19:25:22 GMT

On balance, looks like it might be an issue with the database server (teradata), which often/ normally resolves at the server - but it passes msgs back which sas doesn't always understand.