Focal Point
Using the SQL Report Wizard

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1991060862

January 25, 2008, 03:46 PM
Robab
Using the SQL Report Wizard
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
January 25, 2008, 04:11 PM
Darin Lee
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
January 25, 2008, 04:15 PM
Leah
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
January 25, 2008, 04:16 PM
Robab
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
January 25, 2008, 05:40 PM
Trav
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
January 26, 2008, 04:49 AM
Majid Jeddi
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
January 26, 2008, 04:52 AM
Majid Jeddi
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
January 26, 2008, 03:20 PM
Robab
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
January 27, 2008, 10:33 AM
Majid Jeddi
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
January 28, 2008, 09:12 AM
Trav
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
January 28, 2008, 09:14 AM
Trav
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
January 28, 2008, 10:33 AM
Robab
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
January 28, 2008, 12:05 PM
Trav
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
January 30, 2008, 11:55 AM
Robab
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