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.

and what is in your stored procedure? Does it include a PREPARE SQLOUT?

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.

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?

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.

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.

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

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?

Hi,

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

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.

I just now caught it, but your BudgetEntityID should probably be an amper variable. &BudgetEntityID

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.

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.

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).