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

Earlier today hit an error when trying to do an insert while looping through a ResultSet returned from stored procedure executed from Dynamics AX. The error was "[SQL Native Client]Connection is busy with results for another command".

A quick fix to it is to close the connection before the INSERT.

============================================================================

Connection       sQLConnection;
Statement        sQLStatement;
str              sql;
TestTableA       testTableA;
RecordInsertList rilTestTableA  = new RecordInsertList(tableNum(TestTableA));
boolean          recordInserted = false;
;

sql = strFmt("EXEC testStoredProc '%1'", dataAreaId);

new SqlStatementExecutePermission(sql).assert();

sQLConnection = new Connection();
sQLStatement  = sQLConnection.createStatement();
resultSet     = sQLStatement.executeQuery(sql);

CodeAccessPermission::revertAssert();

while(resultSet.next())
{
    recordInserted = true;

    testTableA.clear();
    testTableA.Field1 = resultSet.getInt(1);
    testTableA.Field2 = resultSet.getString(2);

    rilTestTableA.add(testTableA);
}

sQLStatement.close(); //Close the connection before the insert else it will
                      //cause the "SQL Native Client]Connection is busy with
                      //results for another command" error

if(recordInserted)
{
    rilTestTableA.insertDatabase();
}
==============================================================================

You might want to consider turning off the row count in stored procedure as well else you'll get an extra result which is the row count.

Field1      Field2
----------- ----------
100001      TestData1
100002      TestData2

(2 row(s) affected)


Use "SET NOCOUNT ON" to turn it off in your stored procedure.
Eg.

CREATE PROCEDURE [dbo].[testStoredProc]

    @DATAAREAID NVARCHAR(4)
AS


SET NOCOUNT ON

SELECT Field1, Field2 FROM SourceTableA

GO

*Updated 02/10/2012
One of my co-worker told me if this issue occur due to running stored procedure, run it without EXEC keyword will resolve it as well. I haven't test this yet but thought I should just update this post.

Eg. EXEC sp_testStoredProc 'param'