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     Using the SQL Report Wizard

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Using the SQL Report Wizard
 Login/Join
 
Silver Member
posted
Hi everybody,

Has anyone used the SQL Report Wizard? I'm trying to use a stored Procedure that has one In variable from Sql server for a webfocus report and I get an error.
In the last step, this is what the Wizard says:

You have chosen to create an SQL report by
executing a stored procedure call.

The SQL database engine you have chosen is
SQLMSS
The predefined connection is
TheNameOftheConnection
The procedure name is
spComboFidCode
The parameters are
BudgetEntityID.

and then when I run the report this is the error message that I get:




Error parsing Master file SQLOUT(foc1400) SQLCode is -1(HEX FFFFFFFF)XOPEN:22018 L (FOC 1405 SQL Prepare Error.
(FOC205) The Description cannot be found for File Named:SQLOUT(1400).


This is the sql Editor that it creats:

ENGINE SQLMSS SET DEFAULT_CONNECTION NameoftheConnection
SQL SQLMSS
EX spComboFidCode BudgetEntityID
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END

any input is appreciated.


WebFocus 7.1
Developer Studio
 
Posts: 35 | Location: Tallahassee,Florida | Registered: March 16, 2007Report This Post
Virtuoso
posted Hide Post
and what is in your stored procedure? Does it include a PREPARE SQLOUT?


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
Haven't ever used a stored procedure, SQLOUT is the default in the wizard, what does your procedure return for a 'file' as Darin asks.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Silver Member
posted Hide Post
Hi Darin,

No my stored procedure does not include PREPARE SQLOUT. It's a very basic procedure, just a simple select statement with on In parameter. What does "PREPARE SQLOUT" mean?


WebFocus 7.1
Developer Studio
 
Posts: 35 | Location: Tallahassee,Florida | Registered: March 16, 2007Report This Post
Platinum Member
posted Hide Post
We use stored procs frequently. I don't see anything wrong with the WF code you've shared. Specifically the SQLOUT file is a WebFOCUS thing. When you run a stored proc, the results are placed into a temporary file called SQLOUT. Your stored procedure doesn't need any sort of reference to it. In fact, the only thing your procedure needs to do is return results.

So if you execute the stored procedure directly, do you get good results? Do you get one and only one result set? Also you might make sure to do a "SET NOCOUNT ON" at the very beginning of your stored procedure -- that will supress the messages from the server indicating "x rows affected". If all of those things check out, then it should work. No prepare SQLOUT necessary on the stored proc side.



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Guru
posted Hide Post
Hi,

Prior to build the report based on data returned by the stored proc I would test first if it returns data in webfocus. When you type exec storedprocname this should return data.

Majid.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Guru
posted Hide Post
quote:
BudgetEntityID

Hi,

When you run the report, does it ask you to supply the BudgetEntityID parameter or it is hard coded in your code.
Please verify that you the parameter is well intialized.

Thanks


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Silver Member
posted Hide Post
Thank you everybody for responding.

I have tested my stored procedure and I know for sure it works and returns records.

Majid,

I actually never get to runnig a report, when I press the finish button of the wizard that's when I get the error. Then when I try to use the sql file that the wizard has created for me to create the report I get the error again. It never asks me for any parameter which is strange.



Trav

Do you use stored procedures with Parameters in webfocus? Do you use SQL Report Wizard or do use other techniques to build the resulting extract file?


WebFocus 7.1
Developer Studio
 
Posts: 35 | Location: Tallahassee,Florida | Registered: March 16, 2007Report This Post
Guru
posted Hide Post
Hi,

Before finishing the wizard there is a step that allow to test your query : simple query or stored proc. Was that successfull?
Regards.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Platinum Member
posted Hide Post
Yes, in fact I can think of only 1 or 2 instances where we don't use parameters on our stored procs.

Here's an example:
ENGINE SQLMSS SET DEFAULT_CONNECTION kmssqlprod1-bireportprod
-*ENGINE SQLMSS SET DEFAULT_CONNECTION kmssqldev1-bireportdev
SQL SQLMSS
-MRNOEDIT EX JTC_Gross_Margin_Report '&BUID', '&ITEM_GROUP_CODE', '&ITEM_CODE', '&ORIGIN_ID', '&STKHLDR_ID', '&FDTE', '&TDTE', '&RESP_CENTER', '&DTPE';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS GROSS_MARGIN FORMAT FOCUS
END


Kind of an extreme example, but you get the idea of using parameters shouldn't be a problem. Smiler Also, in this example you can see that although it does produce the SQLOUT file, I've actually held those results into a different file. You don't have to do this -- I could have easily left that "ON TABLE HOLD" completely off to just return the results.



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Platinum Member
posted Hide Post
I just now caught it, but your BudgetEntityID should probably be an amper variable. &BudgetEntityID



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Silver Member
posted Hide Post
Trav,

You were right, I added & infront of my variable, now at least I'm prompted for the value of the parameter, this is what my file looks like now:
ENGINE SQLMSS SET DEFAULT_CONNECTION DCAEnterprise
SQL SQLMSS
EX spComboFidCode '&BudgetEntityID'
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END


but I still get this error after I provide the value:

(FOC1400) SQLCODE IS 2812 (HEX: 00000AFC) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Could not find stored
: procedure 'spComboFidCode'.
L (FOC1405) SQL PREPARE ERROR.


I'm sure the connection is right, I don't know why it says that it cannot find the Stored Procedure.


WebFocus 7.1
Developer Studio
 
Posts: 35 | Location: Tallahassee,Florida | Registered: March 16, 2007Report This Post
Platinum Member
posted Hide Post
Hmm, you might just double-check the seemingly obvious things:
spelling of the connection name
spelling of the stored procedure
make sure your connection is actually connecting to the db you think it is.
You can also try running the stored procedure directly (like in query analyzer or ss management studio).

Beyond that, it really should work. Hate to say it, but there is something in your config or connection or spelling or something that isn't right. My bet is that it will be something silly that you'll slap yourself on the forehead about. If all else fails, go ahead and log a case and see if they can help you with a remote assist session or something. Sometimes it just takes another pair of eyes.



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Silver Member
posted Hide Post
Just wanted to thank everybody for their help and let you know that what ended up fixing the problem was using a three part name (database.owner.procedure_name).


WebFocus 7.1
Developer Studio
 
Posts: 35 | Location: Tallahassee,Florida | Registered: March 16, 2007Report 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     Using the SQL Report Wizard

Copyright © 1996-2020 Information Builders