Trouble with stored procedures

I have written a stored procedure in SQL2005 that returns a resultset. I am calling this procedure from AX4 from a class that is used in different parts of the application

The code i’m using to call the resultset is

[code] connection = new UserConnection();
statement = Connection.createStatement();
sqlStatement = strfmt(‘exec usp_findTickets %1, %2, %3, ‘%4’, ‘%5’, ‘%6’’,
//parameters );
perm = new SqlStatementExecutePermission(sqlStatement );
perm.assert();

resultSet = statement.executeQuery(sqlStatement);

while ( ResultSet.next() )

{
// do something with results
}

CodeAccessPermission::revertAssert();

rcmAdvDiscTable = RCMAdvDiscTable::find(discountId);
[/code]

this seemed to work at first, but later on i discovered when called from a batchprocess there was an error thrown on the last line in above code-block:

[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt.

The obvious conclusion was the cursor for retrieving data through the executeQuery wasn’t closed correctly. I’ve tried several things resulting in adding

[code]
resultSet.close();
resultSet.notifyAll();

connection.notifyAll();[/code]

before the last line of code. It solved the problem for the first error message, but gives me a new error

[Microsoft][ODBC SQL Server Driver]Invalid cursor state. 

a little further in the process. This error is thrown on the line with resultset.next().

I’m getting a bit desperate as to the proper way to call a stored procedure, work with the resultset and close it properly again. How can I solve the issues that either cursors are open when they shouldn’t or closed when they should be open? Usable both when called from client or executed through batch.

It appears the above code wasn’t the cause of the problem. One of the parameters issued to the stored procedure is a date-field. The batchprocess runs under a different user then the other processes and apperently has a different localization setting.

Therefore one process runs without problems, the other process receives an error. I’ve altered my code to always supply dates in american format to the database and both processes run without problems. The error-message ‘Invalid cursor-state’ could have been a little more explanatory.

Thanks for posting the fix [:D] . I am sure this info will be of use to someone.

I know this post is somehow old but I have the same issue, however the SP doesn’t receive any date parameter.

The problem is quite similar to yours. Inside the “while” I’m creating a new record in the LedgerTrans, the first record is stored correctly, but the second time, at the very moment when it’s evaluating the resultSet.next() condition, this error pops out:

[Microsoft][SQL Native Client]Connection is busy with results for another command

Have you any idea what else could it be?

Thanks! [:)]

Hi,

Please can you post your code? And what version of SQL Server are you using?

Are you able to use this technique to use a stored procedure as the data source for a Dynamics report? How about just a SQL stateement as the data source? I’ve got a SQL statement that has subselects in teh select clause and the where clause and I want to use it as the data source for a report inside Dynamics.