Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL Server Store Procedures

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SQL Server Store Procedures
 Login/Join
 
MariaD
Member
posted
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,


WebFOCUS 7.6
Windows, All Outputs

 
Posts: 6 | Registered: May 29, 2012Report This Post
FrankDutch
Virtuoso
posted Hide Post
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
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
MariaD
Member
posted Hide Post
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


WebFOCUS 7.6
Windows, All Outputs

 
Posts: 6 | Registered: May 29, 2012Report This Post
Waz
Expert
posted Hide Post
I don't think the RUN is needed.

Also use -SET &ECHO=ALL;


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Tom Flynn
Expert
posted Hide Post
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 Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
MariaD
Member
posted Hide Post
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;


WebFOCUS 7.6
Windows, All Outputs

 
Posts: 6 | Registered: May 29, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
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


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
MariaD
Member
posted Hide Post
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


WebFOCUS 7.6
Windows, All Outputs

 
Posts: 6 | Registered: May 29, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
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 Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
MariaD
Member
posted Hide Post
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


WebFOCUS 7.6
Windows, All Outputs

 
Posts: 6 | Registered: May 29, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
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...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL Server Store Procedures

Copyright © 1996-2020 Information Builders