I'm having trouble calling an Store Procedure from a fex. The store procedure has 3 parameters and based on those I prepare the data in SQL in the store procedure.
I have tried several ways but I'm not getting any results.
This is what I have:
ENGINE SQLMSS SET DEFAULT_CONNECTION ent-vocdvdig01\QA
SQL SQLMSS PREPARE SQLOUT FOR
SQL SQLMSS SET SERVER ENT-VOCDVDIG01/QA
SQL SQLMSS EX usp_VD_VIDEO_REPORT @STARTING='20120103',@REP_INT=1,@IN_CPSOURCE='NL';
RUN
TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS RPTHLD
END

Could somebody let me know what is wrong with this code?
Thanks,
MMD

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

Looks rather good, what does it give you as result?

Did you try adding some error handling code?
Maybe add the first line of report with
-&ECHO=ALL;

And REMOVE the line that says
ON TABLE PCHOLD AS RPTHLD
And add instead
WHERE RECORDLIMIT EQ 100

Tel me what you get

Frank,
Thanks for responding to my message.
This is what I have now and the output is as follows:
&ECHO=ALL;
SQL SQLMSS SET SERVER ENT-VOCDVDIG01/QA
SQL SQLMSS
EX usp_VD_VIDEO_REPORT @STARTING=20120103,@REP_INT=1,'@IN_CPSOURCE'='VPC';
RUN
TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT EQ 100
END
And the Output message is:
0 ERROR AT OR NEAR LINE 13 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

I have 3 parameters: @STARTING - should be a date
@REP_INT - should be an integer and @IN_CPSOURCE which should be a character string.
Do I need to declare those variables before calling the store procedure?
Thanks,
Maria

I don't think the RUN is needed.

Also use -SET &ECHO=ALL;

MariaD,
Use &, not @, to send variables to SQLMSS Stored Procs; we also use DATABASE.DBO.STORED_PROC. This is currently in Production:

  
SQL SQLMSS SET SERVER &CONNECTION
SQL SQLMSS EX Impact.DBO.PS_WF_GetTotalFuelSpent '&FROMDATE', '&TODATE', '&DATETYPE', &CUSTKEY;
-RUN

The variables must be DECLARED in order:

  
ALTER PROCEDURE [dbo].[PS_WF_GetTotalFuelSpent]
(
@FromDate datetime,
@ToDate datetime,
@DateType varchar(100),
@CustKey int
)

hth...

Tom,
Thank you for replying.
How do I declare those parameters in WF proc?
I get the following message:
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE mynewsptest2.fex
(FOC295) A VALUE IS MISSING FOR: &STARTING
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE mynewsptest2.fex
(FOC295) A VALUE IS MISSING FOR: &REP_INT
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE mynewsptest2.fex
(FOC295) A VALUE IS MISSING FOR: &IN_CPSOURCE

and my code is as follows:
ENGINE SQLMSS SET DEFAULT_CONNECTION sqlserverid\QA
-*DECLARE @STARTING DATE
-*DECLARE @REP_INT INT
-*DECLARE @IN_CPSOURCE varchar(10)
SET @STARTING = '01/01/2013'
SET @REP_INT = 1
SET @IN_CPSOURCE = 'NL'

SQL SQLMSS PREPARE SQLOUT FOR
SQL SQLMSS SET SERVER sqlserverID\QA
SQL SQLMSS
EX VIDEO.DBO.usp_VD_VIDEO_REPORT '&STARTING','&REP_INT','&IN_CPSOURCE';
RUN
TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT EQ 100
END;

SQLMSS automatically PREPAREs SQLOUT, commented out that line...

-SET @STARTING = '01/01/2013';
-SET @REP_INT = '1';
-SET @IN_CPSOURCE = 'NL';

-* SQL SQLMSS PREPARE SQLOUT FOR
SQL SQLMSS SET SERVER sqlserverID\QA
SQL SQLMSS
EX VIDEO.DBO.usp_VD_VIDEO_REPORT '&STARTING',&REP_INT,'&IN_CPSOURCE';
-RUN
TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT EQ 100;
END

I get the following error messages:
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE mynewsptest2.fex
(FOC295) A VALUE IS MISSING FOR: &STARTING
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE mynewsptest2.fex
(FOC295) A VALUE IS MISSING FOR: &REP_INT
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE mynewsptest2.fex
(FOC295) A VALUE IS MISSING FOR: &IN_CPSOURCE

Copy the code the way I have it, with a DASH and semi-colon.
Those are Dialogue Manager variables.

To see if your proc works, just hard-code, for now...

  
SQL SQLMSS SET SERVER sqlserverID\QA
SQL SQLMSS
EX VIDEO.DBO.usp_VD_VIDEO_REPORT '01/01/2013',1,'NL';
-RUN
TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT EQ 100;
END

Tom,
I'm getting closer but still I'm getting this message that I don't know where it is coming from. The store procedure runs and updates the staging tables as planned. But still this SET error will not go away.

ENGINE SQLMSS SET DEFAULT_CONNECTION SERVERID\QA
SQL SQLMSS
EX VIDEO.DBO.USP_VD_VIDEO_REPORT '01/01/2013','3','NL';
RUN
TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT EQ 100

(FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND:
0 ERROR AT OR NEAR LINE 6 IN PROCEDURE SQLOUT FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: SET
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

MariaD,
Because you are "not" copying THE EXAMPLE correctly, and, I do not know what the PROC is doing. But, your example is invalid:

ENGINE SQLMSS SET DEFAULT_CONNECTION SERVERID\QA
SQL SQLMSS
EX VIDEO.DBO.USP_VD_VIDEO_REPORT '01/01/2013','3','NL';
-RUN
TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT EQ 100;
END
-EXIT

Not here to train, IBI does that, and/or, the programmer challenges themselves and opens the manual...