How would you code a fex to accept parameters being sent from pass thru SQL, like success or failure?

This message has been edited. Last edited by: Kerry,

Capturing results from SQL has been asked before.

I think there are two options depening on what you wnat to achieve.

Have a look at PASSRECS.

Also there is a technique that will capture the SQL returned from the passthrough, using tracing.

There may be other options as well.

I may have not explained myself correctly. I have a fex that calls an insert procedure on the DB and I want to capture the success or failure code that comes back from the DB. Is there any way to capture the return code?

You may want to take a look at the "Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS" guide.

Look for the section corresponding to MS SQL Server adapter and you will hopefully find examples of what you can achieve with that adapter. As you don't mention which version of WF you're working with I cannot really provide a suitable DN#.

Would you please updte your profile to mention that? It will help others in providing some future advice.

I may still not understand your requirements.

I guess you want to see/check the returned statement like:
(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 1/INSERT

PASSRECS may get you the number of records into an & variable.

The second option that captures the output that you see from the job.

Just replace your SQL with the one in this code.
e.g.

-*******************************************************************************
-* Set the trace file

-SET &TEMPPATH = TEMPPATH(80,'A80');
-SET &TSCOM    = GETTOK(&TEMPPATH,80,-2,'/',8,'A8');
-SET &TRACEFL  = &TEMPPATH || ('cmd_op.trc');

-*******************************************************************************
-* Set Commands To Set Tracing

SET TRACEOFF  = ALL
SET TRACEON=R1H
SET TRACEUSER = ON
SET TRACEUSER = &TRACEFL

-RUN
SQL SQLMSS
INSERT INTO SOARS_Reporting.dbo.MyTable
VALUES(2,'MyData')
;
END
-RUN
-RUN
-* The previous two -RUN's must be there for this to work.
SET TRACEOFF  = ALL
SET TRACEUSER = OFF
-RUN

-*******************************************************************************
-* Allocate The Trace File
FILEDEF TRACEFIL DISK &TRACEFL

-RUN

-*******************************************************************************
-* Write out a master to read the TRACEFIL list
EX -LINES 4 EDAPUT MASTER,TRACEFIL,CV,FILE
FILENAME=TRACEFIL, SUFFIX=FIX,$
SEGNAME=TRACEFIL, $
  FIELD=LINE ,ALIAS=  ,A500 ,A500 ,$

-RUN

-*******************************************************************************
-* Process the trace file to get the results of the command.

DEFINE  FILE TRACEFIL
 Item3/A20     = GETTOK(LINE,500,3,' ',20,'A20') ;
 MSG_Item/A493 = SUBSTR(500,GETTOK(LINE,500,2,'"',500,'A500'),8,500,493,'A493') ;
 CMD_Flag/A1   = IF EDIT(MSG_Item,'9') EQ ':' THEN 'Y' ELSE 'N' ;
END

TABLE   FILE TRACEFIL
 PRINT  
        MSG_Item

 WHERE        Item3    EQ 'NGtrEvnt:'

-* ON     TABLE HOLD AS TMP_CHCK

END

-RUN