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
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'
