Hi Friends
I am trying to call a SQL stored procedure from a SQL wizard and facing some issues.
This is a humungous stored procedure and I have no way to avoid calling it. The procedure calls some other things in the database and finally populates a temporary table. It is being used at other places also so I can not change it anyway.
The procedure is called in the following way:

database.dbo.csp_acct_trans_lvl2_ins &ACCT_ID,1,1, &rowcount, &retcode

The last 2 parameters are rowcount and return code which are returned from the stored proc. I am using amper variables and have also tried writing 0 or 1 there but to no avail.
The result of them will be nice to have but I will not need if that is the only way out.
When I click the run SQL button on the wizard, it gives me correct results by populating the temp table. But when I click next, it asks me for the parameters and then gives the error message:
"Error parsing master file SQLOUT(FOC 205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT(205))"
The .FEX file has the following text after that:
--------------------------------------------------
ENGINE SQLMSS SET DEFAULT_CONNECTION DBIN901
SQL SQLMSS PREPARE SQLOUT FOR
-MRNOEDIT EX ipsi_in901.dbo.csp_acct_trans_lvl2_ins &ACCT_ID,1,1,&rowcount,&retcode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
--------------------------------------------------
Please help me solve this puzzle.
My flow will be: call this procedure, print data from the populated temp table, call another delete stored proc.

I found some threads here which say that it is not possible to get anything from SQL server other than Resultset, I am wondering that is it possible to call the stored proc without last 2 output parameters or something like that? I tried calling without out params or with blank commas ,, but nothing worked. Thanks a ton in advance.
Ashish

DeadMan,

quote:

SQL SQLMSS PREPARE SQLOUT FOR
-MRNOEDIT EX ipsi_in901.dbo.csp_acct_trans_lvl2_ins &ACCT_ID,1,1,&rowcount,&retcode;

&rowcount and &retcode are being "PASSED" to to the stored procedure, they are not being returned.
Any AMPER variable passed to MSSQL sp must have the declared variable in the sp

@acct_id
@numeric1
@numeric2

etc..

To get what you are looking for:

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS XSQLOUT
END
-RUN
-IF &LINES EQ 0 GOTO NO_DATA;

If 0, then branch to the lable NO_DATA and display a message, or, something...

I've not seen &RETCODE function as designed when used after executing stored procs...

Hope this helps...

Tom

Is this working from your query analyzer (for SQL Server)?

Btw, which database you are using?

Hi Kamlesh
This works from Query Analyzer (SQL Server 2005). It also works on the Test SQL step from SQL Wizard when I pass the parameters to the query but fails when I click the next button to end the wizard and see the report.
I think that passing the output parameters as input is causing the problem.
What is the way to avoid it?
Thanks
Ashish

Hi Tom
Are you suggesting the following:
ENGINE SQLMSS SET DEFAULT_CONNECTION DBIN901
SQL SQLMSS PREPARE SQLOUT FOR
-MRNOEDIT EX ipsi_in901.dbo.csp_acct_trans_lvl2_ins &ACCT_ID,1,1,&rowcount,&retcode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
-RUN

I do not have any nodata yet in my report. But the above did not work either. I have made the rowcount and retcode same to what is declared in the database.
I agree with you that this way the variables are getting passed instead of received from the SP. Please suggest something that works.
I get the following error when I call it this way.
(FOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
: '.'. [42000] Statement(s) could not be prepared. [] Deferred prepare cou
: ld not be completed.
L (FOC1405) SQL PREPARE ERROR.


Thanks to you.
Ashish

quote:

ENGINE SQLMSS SET DEFAULT_CONNECTION DBIN901
SQL SQLMSS PREPARE SQLOUT FOR
-MRNOEDIT EX ipsi_in901.dbo.csp_acct_trans_lvl2_ins &ACCT_ID,1,1,&rowcount,&retcode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
-RUN

1. I don't know what &ACCT_ID,1,1,; are????

That is not the code I gave you:

This would be it:
-DEFAULT &rowcount=0, &retcode=0
ENGINE SQLMSS SET DEFAULT_CONNECTION DBIN901
SQL SQLMSS PREPARE SQLOUT FOR
-MRNOEDIT EX ipsi_in901.dbo.csp_acct_trans_lvl2_ins &ACCT_ID,1,1,&rowcount,&retcode;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS XSQLOUT
END
-RUN
-IF &LINES EQ 0 GOTO NO_DATA;
DEFINE FILE XSQLOUT
ETC.
END
TABLE FILE XSQLOUT
ETC.
END
-EXIT
-NODATA
"There were zero records found for Account: &ACCT_ID"
"Please try another Account"
-EXIT

OR

Something like this

This message has been edited. Last edited by: Tom Flynn,

Hi Tom
Thanks again for trying to solve my problem. Unfortunately it is still not working. When I put your code or modify my code, it still gives the same error.
At the click of next it asks for the Account ID but after giving the account ID it gives the same 205 error and the report icon disappears.
Strangely, it populates the table while clicking on the Test SQL link.
Any other thoughts?
The account Id is an integer which I will pass in an amper through drill down and rest 1,1 are constants. Thanks
Ashish

DeadMan,

For testing, add a default for &ACCT_ID:

-DEFAULT &rowcount=0, &retcode=0, &ACCT_ID = valid account number

ENGINE SQLMSS SET DEFAULT_CONNECTION DBIN901
SQL SQLMSS PREPARE SQLOUT FOR
-MRNOEDIT EX ipsi_in901.dbo.csp_acct_trans_lvl2_ins &ACCT_ID,1,1,&rowcount,&retcode;

Hi Tom
Sorry for bothering you again and again. It still does not work. The following errors comes on the Page:

(FOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
: '.'. [42000] Statement(s) could not be prepared. [] Deferred prepare cou
: ld not be completed.
L (FOC1405) SQL PREPARE ERROR.

I have tried so many things that I am out of my wits now. Please suggest something.

DeadPerson,

This means the variables being passed are out-of-sync with the SP @variables.
You have a coding error in the SP, OR, you are passing data that is not in the correct order.

Really can't assist anymore without you dumping the whole SP (please don't) on here.

I will give you an example of a MSSQL SP and the WebFOCUS call in an hour; I have a meeting...

OR, someone else can/will assist...


Tom

 
-*****************************
-*    Example WebFOCUS:
-*****************************

-DEFAULT &VAR1 = 'column_name', &VAR2 = 1234567890, &VAR3 = '01/01/2001', &VAR4 = '01/31/2001'

SQL SQLMSS SET SERVER &CONNECTION
SQL SQLMSS EX DataBase.DBO.StoredProcName '&VAR1', &VAR2, '&VAR3', '&VAR4';
?FF SQLOUT
-RUN
-*------------------------------------------------------------------------
-* Read the data into a hold file.
-*------------------------------------------------------------------------
TABLE FILE SQLOUT
PRINT *   
 ON TABLE HOLD AS XSQLOUT
END
-RUN
-*------------------------------------------------------------------------
-* Check the record count 
-*------------------------------------------------------------------------
-IF &LINES EQ 0 GOTO NO_DATA;
-*------------------------------------------------------------------------
etc.


-*****************************
-*   Stored Procedure:
-*****************************

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[StoredProcName]
 (
   @var1 varchar(nn),
   @var2 int,
   @var3 datetime,
   @var4 datetime
 )

etc., etc.

Hope this helps...

Tom

This message has been edited. Last edited by: Tom Flynn,

I had the same problem with DB2 AS400 DATABASE.

The problem was resolved when I add an alias to all the tables in the SQL order

Hope this will help you.
Catherine.

Thanks to all of you.
It worked when I changed the rowcount from 1 to 10. Thanks to Tom and everyone else for taking interest.
I am absolutely new to Webfocus so small problems also throw me off!!
Ashish