[Migrated content. Thread originally posted on 02 September 2011]

I am doing a prof of concept for using stored procedures for my account. I create a cursor and call a stored procedure that get the top 25 rows from a table.
I then open the cursor and then do a fetch. I get a SQL error 000000001. Our error checking routine corrects the sql error. I can see that the first row is returned in my working storage fields.
I then try to insert into another table using embedded SQl in my cobol program and when I do the insert I get an error returned 000000100.
No knowing that this is a not found error I am not sure how I could be getting this error returned from an insert.

I will provide some code to show what I am doing.

My stored procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







ALTER PROCEDURE dbo.spDAL_TESTPID_SEL_OR_HEADER
@COMPANY CHAR(2),
@DC_ID CHAR(4)

AS
BEGIN
SELECT TOP 5 DC_ID, SO_ID, CUST_ORDER_ID, CUST_SHIP_TO_ID, CUST_SHIP_TO_SUFF
FROM CSI.OR_HEADER
WHERE (COMPANY = @COMPANY) AND (DC_ID = @DC_ID)

IF @@error <> 0
BEGIN
BEGIN TRANSACTION t1
INSERT INTO CSI.SY_ACTIVITY_LOG
VALUES( ' ',
'SEL_OR_HEADER',
substring(Convert(CHAR,getdate(),120), 1, 10),
substring(Convert(CHAR,getdate(),120), 12, 2) '.'
substring(Convert(CHAR,getdate(),120), 15, 2) '.'
substring(Convert(CHAR,getdate(),120), 18, 2),
'SPDAL',
'SYSTEM',
'A',
'Error:' @@error,
0,
' ',
' ')
COMMIT TRANSACTION t1
RETURN('E') -- Ends running the stored procedure
END
ELSE
RETURN(0)
END







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

My cobol code.

210-GET-OR-HEADER.

EXEC SQL
DECLARE C1 CURSOR FOR
CALL spDAL_TESTPID_SEL_OR_HEADER
(
:WTMS-COMPANY-ID
,:WTMS-DC-ID
)
END-EXEC .

EXEC SQL OPEN C1 END-EXEC.
PERFORM UNTIL EXIT
EXEC SQL
FETCH NEXT C1 INTO
:WSOH-COMPANY-ID,
:WSOH-SO-ID,
:WSOH-CUST-ORDER-ID,
:WSOH-CUST-SHIP-TO-ID,
:WSOH-CUST-SHIP-TO-SUFF
END-EXEC
MOVE 'FETCH ' TO WS-STD-SQL-COMMAND
PERFORM 11020-SYHDB-ADJ-SQLCODE THRU 11020-SYHDB-EXIT
IF SQLCODE NOT = WS-SQLCODE-NORESP
IF SQLCODE = WS-SQLCODE-NOTFND
EXEC SQL CLOSE C1 END-EXEC
MOVE 'CLOSE ' TO WS-STD-SQL-COMMAND
PERFORM 11020-SYHDB-ADJ-SQLCODE THRU 11020-SYHDB-EXIT
EXIT PERFORM
GO TO 210-EXIT
ELSE
IF SQLCODE NOT = WS-SQLCODE-NORESP
MOVE SPACES TO WS-MESSAGE-AREA
MOVE 'E' TO WHALG-RECORD-TYPE
MOVE '0203' TO WS-MESSAGE-ID
MOVE 'C_OR_LINE' TO WHALG-TABLE-NAME
MOVE SQLCODE TO WHALG-SQLCODE
MOVE '660' TO WHALG-DEBUG-REF-ID
PERFORM 999-ABEND
END-IF
END-IF
END-IF
PERFORM 216-WRITE-RESULTS THRU 216-EXIT
END-PERFORM.

216-WRITE-RESULTS.
ADD 1 to COUNTER-REC.
MOVE COUNTER-REC TO WHALG-DEBUG-REF-ID.

EXEC SQL
INSERT INTO CSI.SO_INDUCT_TEMP

(
ROW_NUM,
COMPANY_ID,
DC_ID,
CNTL_TYPE,
CNTL_ID,
CUST_SHIP_TO_ID,
CUST_SHIP_TO_SUFF,
WEIGHT_FACTOR,
CUBE_FACTOR,
QTY_PICK,
GROUP_CODE,
SEQ_LOCATION_ID,
PRIORITY,
PICK_PASS_GROUP,
PICK_PASS_PRIORITY,
SUB_ZONE_CODE,
SCANNER_NUMBER,
SCANNER_DIRECTION,
ROW_ID)
VALUES(
'1',
'OM',
:WTMS-DC-ID,
' ',
' ',
:WSOH-CUST-SHIP-TO-ID ,
:WSOH-CUST-SHIP-TO-SUFF,
0,
0,
0,
' ',
' ',
0,
' ',
0,
' ',
' ',
' ',
:WHALG-DEBUG-REF-ID
)
END-EXEC.
MOVE 'INSERT ' TO WS-STD-SQL-COMMAND
PERFORM 11020-SYHDB-ADJ-SQLCODE THRU 11020-SYHDB-EXIT
IF SQLCODE = WS-SQLCODE-NORESP
GO TO 216-EXIT
ELSE
MOVE SPACES TO WS-MESSAGE-AREA
MOVE '0204' TO WS-MESSAGE-ID
MOVE 'SO_INDUCT_TEMP' TO WHALG-TABLE-NAME
MOVE SQLCODE TO WHALG-SQLCODE
MOVE '221' TO WHALG-DEBUG-REF-ID
PERFORM 999-ABEND
END-IF.
216-EXIT.
EXIT.



thanks for any help provided.