Hello,

I am trying to call a SQL stored procedure using the following code (as a side note, this is my first time trying to call a stored procedure from FEX code):

-*---(FEX)----
ENGINE SQLMSS SET DEFAULT_CONNECTION MyDataSource

SQL SQLMSS PREPARE ALLDATA FOR
EXEC [dbo].[WF_GetData]
END

TABLE FILE ALLDATA
PRINT *
END


The body of the stored procedure I am calling is below:

CREATE PROCEDURE [dbo].[WF_GetData]
AS
BEGIN

DECLARE @someVariable CHAR = 'A'

SELECT 1 AS TempData
END


The error I am getting is below:

(FOC1400) SQLCODE IS 16937 (HEX: 00004229) XOPEN: 42000
: Microsoft SQL Native Client: [42000] A server cursor cannot be opened on
: the given statement or statements. Use a default result set or client c
: ursor. [] Multiple-step OLE DB operation generated errors. Check each OL
: E DB status value, if available. No work was done.
L (FOC1406) SQL OPEN CURSOR ERROR. : ALLDATA


Any ideas on what I am doing wrong?
Greg.

This message has been edited. Last edited by: <Kathryn Henning>,

Take a look at how I call store procedure and see if you can use my example.

  
-DEFAULTS &TNK_STR='599';
-*VARCHAR
-DEFAULTS &SITE_STR=10;
-*NUMBER
-DEFAULTS &FR_DATE_STR='07/01/2011';
-*VARCHAR
-DEFAULTS &TO_DATE_STR='09/30/2011';
-*VARCHAR
 
ENGINE SQLORA SET VARCHAR OFF
ENGINE SQLORA SET DEFAULT_CONNECTION dev07
ENGINE SQLORA
 
 
EX WMGAPPL.INV_SORT.SORT_INV_FOR_WMG0183 '&TNK_STR',&SITE_STR,'&FR_DATE_STR','&TO_DATE_STR';
 
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS SEQ_NUM
END

prodrigu,

Do any of the stored procedures you are calling have a declare statement (or any business logic that requires more than a single select statement)?

Greg.

Looked through some of my old code. Here is alternative syntax for stored procedure of MS SQL Server.
Your stored procedure has no required input parameters or they have default values, right?
MyDataSource needs to be the name of the data adapter you are going to use.
If it is not pointing to the database where dbo.WF_GetData lives.
Then you will need to fully qualify the stored procedure. ie Change dbo.WF_GetData to DatabaseName.dbo.WF_GetData.

  
SET SQLENGINE = SQLMSS
SQL SQLMSS SET SERVER MyDataSource ;
SQL SQLMSS EX  dbo.WF_GetData ;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HDATA
END
-RUN

Mighty Max,

Correct, there are no input parameters to the stored procedure.

I can get the stored procedure to execute as long as the body of the stored procedure is a single select statement. If I declare a variable inside the stored procedure I get the error about a server cursor not being able to be opened.

I can execute this stored procedure through SQL Server Management Studio, but not through FEX code (unless I remove the variable declared in the stored procedure).

MSSQL automatically create the SQLOUT file, no need to PREPARE. Use SQLOUT, then hold as ALLDATA...

This is how we've done it:

Stored Proc:

  
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[PS_WF_GetPaidAmountByShipperByCountry]
(
@FromDate datetime,
@ToDate datetime,
@DateType varchar(100),
@CustKey int
)
/****************************************************************************************
 * Description:                                                                         *
 *	@custkey=2134, @fromdate='20070201', @todate ='20080221' , @datetype='ship_date'*										
 * Status:										*
 *											*
 * Change History:									*
 *	Author	       Date	   Description of Change				*
 *	------	       ---------   -------------------				        *
 *  TF                 03/01/2008  PROD                                                 *  
 *  TF                 03/27/2008  Add counts, carrier, amounts                         *  
 ****************************************************************************************/

AS

Declare @cmd nvarchar(4000)
Declare @parm nvarchar(4000)

SET NOCOUNT ON

set @cmd = N'SELECT 
                    sum (isnull(b.paid_amount,0)) PAID_AMOUNT,
                    sum (isnull(b.billed_amount,0)) BILLED_AMOUNT,
                    sum (isnull(b.adjustment_amount,0)) ADJUSTMENT_AMOUNT,
                    COUNT(pro_no) CNTPRONO,
			        b.carrier_key,
			        d.carrier_name,
                    isnull(c.country_code,''US'') ORIG_CODE,
                    isnull(e.country_name,''UNITED STATES'') ORIG_NAME  
               FROM Impact.dbo.branch a WITH(NOLOCK)
         INNER JOIN Impact.dbo.freight_bill b WITH(NOLOCK)
                 ON a.branch_key = b.branch_key
         INNER JOIN Impact.dbo.origin c WITH(NOLOCK)
                 ON b.origin_key = c.origin_key
		 INNER JOIN IMPACT.dbo.carrier d WITH(NOLOCK)
				ON b.carrier_key = d.carrier_key
         INNER JOIN Impact.dbo.countries e WITH(NOLOCK)
                 ON c.country_code = e.country_code
              WHERE a.customer_key = @CustKey
                AND b.' + @datetype + ' between @fromdate and @todate  
                AND b.freight_bill_status_code in ( ''A'',''D'',''P'')
				AND d.status = ''A'' AND a.status = ''Active'' 
           GROUP BY isnull(c.country_code,''US''),
                    isnull(e.country_name,''UNITED STATES''),
                    b.carrier_key,
                    d.carrier_name'

set @parm = N'@custkey integer, @fromdate datetime, @todate datetime'

exec master.dbo.sp_executesql @cmd, 
                              @parm, 
                              @custkey=@custkey, @fromdate=@fromdate, @todate=@todate   

Focexec:

  
SQL SQLMSS SET SERVER &CONNECTION
SQL SQLMSS EX XXXXXX.dbo.PS_WF_GetPaidAmountByShipperByCountry '&FROMDATE.EVAL', '&TODATE.EVAL', '&DATETYPE.EVAL', &CUSTKEY.EVAL;
-*------------------------------------------------------------------------
-* Hold the data into a hold file.
-*------------------------------------------------------------------------
TABLE FILE SQLOUT
SUM
PAID_AMOUNT
&TRANDIR.EVAL
BY &TRANDIR_CD.EVAL
WHERE PAID_AMOUNT NE 0;
ON TABLE HOLD AS RPTDATA
END
-RUN
?FF RPTDATA
-RUN
-*------------------------------------------------------------------------
-* Check the record count, database error and focus error and display
-* relevant message.
-*------------------------------------------------------------------------
-IF &LINES EQ 0 OR &RETCODE NE 0 GOTO DISPMSG;

This works for us...
hth...

I've declared variables inside stored procedures before so that is not your issue. Its been awhile since I have written TSQL.
But aren't you suppossed to DECLARE and then SET.

  
DECLARE @myVariable char(1)
SET @myVariable = 'A'

Also try logging into SQL Management Studio using the same credentials that the data adapter is using. Then try to run the stored procedure.

Tom,

That fixes my problem. Thanks.

Greg.