Sql-server – Fail to execute SQL command asynchronously

sql server

I want to invoke a SQL command INSERT INTO for 3,000,000 times to insert 3,000,000 records via ADO.

Other than the BULK INSERT command, I also want to utilize the ADO.Command object to execute the SQL command asynchronously.

So I do as follows:

  CString strSQLChange;

  _ConnectionPtr pConnection = NULL;
  _CommandPtr pCmdChange = NULL;

  // Open connection.
  pConnection.CreateInstance(__uuidof(Connection));
  pConnection->Open (strCnn, "", "", adConnectUnspecified);

  // Create command object.
  pCmdChange.CreateInstance(__uuidof(Command));
  pCmdChange->ActiveConnection = pConnection;


  for (UINT uIndex = 0; uIndex < 3000000, uIndex ++)
  {
     strSQLChange.Format(_T("INSERT INTO MyTable VALUES (%u, %u, %u);"), uIndex, uIndex *2, uIndex *3); 
     pCmdChange->CommandText = _bstr_t(strSQLChange);
     pCmdChange->Execute(NULL, NULL, adCmdText | adAsyncExecute | adExecuteNoRecords);
  }

The first execution is OK. But the second one will cause the following error

Operation cannot be performed while connecting asynchronously

Does that means the ADO command object cannot execute the SQL command asynchronously?

I am also thinking about using BULK INSERT command, but since there are multiple tables, I also need to execute BULK INSERT command multiple times. It is better to execute them asynchronously so executing SQL commands asynchronously is also a solution I need to seek.

Best Answer

You need create a separate pCmdChange object for each INSERT:

for (UINT uIndex = 0; uIndex < 3000000, uIndex ++)
{
     // Create command object.
     pCmdChange.CreateInstance(__uuidof(Command));
     pCmdChange->ActiveConnection = pConnection;
     pCmdChange->CommandText = strSQLChange;

     pCmdChange->Execute(NULL, NULL, adCmdText | adAsyncExecute | adExecuteNoRecords);
}

Keep in mind that your server probably won't like 3,000,000 near-concurrenct commands. You may run into SQL server dead-locks and or other connectivity issues.

Related Question