AX / D365FO – Error : “Connection is busy with results for another hstmt” while executing a stored procedure

With AX you have the ability to execute stored procedures directly from X++ code The benefit is greater speed in query execution and flexibility in building complex queries. See this post to learn more about the topic : https://dynamicsaxgyan.wordpress.com/2020/03/06/create-and-execute-stored-procedures-in-d365-fo-x/

I recently encountered this error in a procedure that executes a SQL stored procedure : “Connection is busy with results for another hstmt”

This was my method code before solve the issue

   public static server container execSP()
    {
        container               ret;
        Connection              connection;
        Statement               statement;
        str                     query;
        ResultSet               res;

        //Open SQL Connection 
        connection = new Connection();
        statement = connection.createStatement(ResultSetType::Dynamic);

        //My stored procedure with result set
        query = "EXEC dbo.mySP WITH RESULT SETS ((ExecutionId bigint, sysinfologlevel bigint,  result text)); ";
        new SqlStatementExecutePermission(query).assert();
        
        //Execute Stored procedure
        res = statement.executeQueryWithParameters(query, null);
        
        //Get results and store them in a container
        if(res.next())
        {
            ret = [res.getInt64(1), res.getInt(2), res.getString(3)];
        }
 
        CodeAccessPermission::revertAssert();

        return ret;
    }

After cursing for hours I finally found the solution to the problem.
The reason was that I didn’t close the database connection after running the command

The solution is simple. Just use statement.close() after executing stored procedure…like shown below

   public static server container execSP()
    {
        container               ret;
        Connection              connection;
        Statement               statement;
        str                     query;
        ResultSet               res;

        //Open SQL Connection 
        connection = new Connection();
        statement = connection.createStatement(ResultSetType::Dynamic);

        //My stored procedure with result set
        query = "EXEC dbo.mySP WITH RESULT SETS ((ExecutionId bigint, sysinfologlevel bigint,  result text)); ";
        new SqlStatementExecutePermission(query).assert();
        
        //Execute Stored procedure
        res = statement.executeQueryWithParameters(query, null);
        
        //Get results and store them in a container
        if(res.next())
        {
            ret = [res.getInt64(1), res.getInt(2), res.getString(3)];
        }
 
        CodeAccessPermission::revertAssert();
        
        // Close connection
        statement.close();


        return ret;
    }