Using the SQL Report Wizard

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, 2007

Ignored post by Darin Lee posted

Leah

Virtuoso

posted

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, 2004

Ignored post by Leah posted

Silver Member

posted

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, 2007

Ignored post by Robab posted

Platinum Member

posted

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, 2005

Ignored post by Trav posted

Majid Jeddi

Guru

posted

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, 2007

Ignored post by Majid Jeddi posted

Majid Jeddi

Guru

posted

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, 2007

Ignored post by Majid Jeddi posted

Silver Member

posted

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, 2007

Ignored post by Robab posted

Majid Jeddi

Guru

posted

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, 2007

Ignored post by Majid Jeddi posted

Platinum Member

posted

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.

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, 2005

Ignored post by Trav posted

Platinum Member

posted

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, 2005

Ignored post by Trav posted

Silver Member

posted

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, 2007

Ignored post by Robab posted

Platinum Member

posted

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, 2005

Ignored post by Trav posted

Silver Member

posted

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, 2007

Ignored post by Robab posted