Kbase P25258: error 2004 before close stored-proc when retrieving records
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/12/2003 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1C
FACT(s) (Environment):
MS SQL DataServer
SYMPTOM(s):
error 2004 before close stored-proc
CLOSE STORED-PROC <proc-name> was called before fetching all the results. (2004)
The table has more than 1000 records but the code only retrieves 100 of them
Code snippet
DEF TEMP-TABLE txyz LIKE xyz.
DEF VAR handle1 AS INT.
DEF VAR i AS INT.
DEF VAR istat AS INT.
FOR EACH txyz:
DELETE txyz.
END.
/* 1000 records from select */
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
("SELECT
x,
Y, /*2*/
z
FROM dbo.xyz").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
CREATE txyz.
ASSIGN txyz.x = proc-text-buffer.proc-text.
/*DISPLAY txyz.proc-text FORMAT "x(75)". by velu 01/28/02 */
i = i + 1.
IF i > 100 THEN LEAVE. /* at 101 leave and try to close */
END.
MESSAGE "i" STRING(i) VIEW-AS ALERT-BOX.
/* error 2004 here */
CLOSE STORED-PROC send-sql-statement iStat = PROC-STAT
WHERE PROC-HANDLE = handle1 .
MESSAGE "after close" VIEW-AS ALERT-BOX.
CAUSE:
Result set returned from stored procedure must be retrieved completely, can not partially retrieve records, i.e., 100 out of 1000.
FIX:
Modify SQL select statement in send-sql-statement to retrieve only top 100 records and send to dataserver.
DEF TEMP-TABLE txyz LIKE xyz.
DEF VAR handle1 AS INT.
DEF VAR i AS INT.
DEF VAR istat AS INT.
FOR EACH txyz:
DELETE txyz.
END.
/* 1000 records from select */
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
("SELECT top 100 /*change here*/
x,
Y, /*2*/
z
FROM dbo.xyz").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
CREATE txyz.
ASSIGN txyz.x = proc-text-buffer.proc-text.
/*DISPLAY txyz.proc-text FORMAT "x(75)". by velu 01/28/02 */
i = i + 1.
IF i > 100 THEN LEAVE. /* at 101 leave and try to close */
END.
MESSAGE "i" STRING(i) VIEW-AS ALERT-BOX.
/* error 2004 here */
CLOSE STORED-PROC send-sql-statement iStat = PROC-STAT
WHERE PROC-HANDLE = handle1 .
MESSAGE "after close" VIEW-AS ALERT-BOX.