call sql stored procedure with multiple select statements

Finance and Operations/AX Technical Forum
1

Hello,

I have created a sql2005 stored procedure. In this procedure different
select statements and update statements will create a result table. I
discovered that on the moment that there is more than one statement in the
stored procedure I get an error in AX:
Message (14:08:07)
Cannot select a record in ().
The SQL database has issued an error.
SQL error description: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
SQL statement: exec dbo.QURSchoberExportTest ‘des’

For testing purpose I simplified the stored procedure and even 2 simple
statements gives the same error.
The stored procedure is now (just for testing)

USE [AX40-Pabo-Test]
GO
SET NOCOUNT ON
GO

ALTER PROCEDURE [dbo].[QURSchoberExportTest]
@company AS varchar(3)
AS
BEGIN

SELECT top 10 *
into QURExportSchoberTmp
FROM custtable (nolock)
WHERE dataareaid = @company

SELECT top 10 *
FROM QURExportSchoberTmp a (nolock)

END

The code in AX is:

public void executeSchoberExport()
{
Connection con;
Statement stat;
ResultSet result;
SqlStatementExecutePermission _perm;
str sqlStr,r1;

;

con = new Connection();
sqlStr = strfmt( ‘exec dbo.QURSchoberExportTest ‘’+curExt()+’’’);
stat = con.createStatement();
_perm = new SqlStatementExecutePermission(sqlstr);
_perm.assert();
result = stat.executeQuery(sqlstr);

while(result.next())
{
print result.getString(1);
print result.getString(2);

}

CodeAccessPermission::revertAssert();

}

Can somebody please help me with this. I also tried several set options but
no luck.
If you only do the second query you get a result set in ax. I think it has
to do that you get more result sets back but I don’t know how to prevent
that. I only need the last result set, or even no result set. In my original
sp routine I have one output parameter which gives back if the sp-routine has
run ok.

2

Is this about Navision? Or Axapta?

3

IN ADO you need to execute result.Open(“SET NOCOUNT ON”); I’m guessing you have to do the same in AX recordset.

4

By your answer Rashed I expect it’s an AX question. Moved to the AX Technical Forum.

5

Pabo,

In your SP example (you wrote its generic, not the real one you need) first SELECT doesn’t return a recordset, only the second one does - so there can’t be a problem with multiple recordsets, but first SELECT still generates “10 records affected”, which causes the error you qouted.

Put SET NOCOUNT ON in the body of SP (in the beginning, and set it back OFF as last statement)

6

Hello Modris,

sorry but this doesn’t works. I use now the code:

USE [AX40-Pabo-Test]

GO

SET NOCOUNT ON

GO

ALTER PROCEDURE [dbo].[QURSchoberExportTest]

@company AS varchar(3)

AS

BEGIN

SET NOCOUNT ON;

SELECT top 10 * INTO QURExportSchoberTmp FROM custtable (nolock) WHERE dataareaid = @company

SELECT TOP 10 * FROM QURExportSchoberTmp a (nolock)

SET NOCOUNT OFF;

END

The error occurs on the moment of the ax statement : while(result.next()) . error message is : Message (11:00:54)
Cannot select a record in ().
The SQL database has issued an error.
SQL error description: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
SQL statement: exec dbo.QURSchoberExportTest ‘des’

I’m using Microsoft Dynamics AX4.0 Kernel + application version 4.0.2501.116 and MS SQL Server 2005

At the end the goal is to start-up the sp_routine which creates and flatfile. When the sp_routine is completed I want to have an answer back that the routine run ok. I don’t need a result set back. Is it possible to just get back an output parameter and if yes how do I do this in AX?

If you want I can send you (or post here) the real procedure so you get an idea what I want?

Already thanks for the support.

Bennie

7

Bennie;

This seems to be not SQL, but Axapta problem - and in that case I can’t help you at all, as I am Navision guy, and even there not a programmer…
I tried to help you in TSQL scripting part, which I know, but regarding Axapta part - wait what other forum members have to say.

8

Modris,

No problem, thanks anyway for your effort.

regards,

Bennie

9

I have a program in Ax where i was using select top 10 and it was giving me Timeout error. I then tried using slect top 5, top 6…

It seems to me that Select top has some issues as when i removed the select top and executed the whole dataset which had as many as 100 records, I did not get the timeout error.

Remove select top 10 and check it may work fine