Hi there!
I've got a problem using ADO to execute a stored procedure (sp) on sql
server, which gets two dates and fills a table with some data on that
interval. Typical execution time is about a minute for a 30days
interval. Since the sp takes potentialy a long time to execute, I
executes it async., and give the user the option to cancel it. The C++
code below does not include that, but it doesn't work anyway:
mdbConnection->BeginTrans();
_CommandPtr cmd;
cmd.CreateInstance( __uuidof( Command ) );
cmd->ActiveConnection = mdbConnection;
cmd->CommandType = adCmdStoredProc;
cmd->CommandText = _bstr_t( SP_DDPOM );
cmd->Parameters->Append( cmd->CreateParameter( "@0", adVarChar,
adParamInput, 10, _bstr_t( dateod ) ) );
cmd->Parameters->Append( cmd->CreateParameter( "@1", adVarChar,
adParamInput, 10, _bstr_t( datedo ) ) );
cmd->Execute( &vtEmpty, &vtEmpty2, adAsyncExecute );
int state;
while((state=cmd->State) & adStateExecuting) {
TRACE("State = %ld\n", state );
// if cancel then mdb->RollbackTrans(); return;
}
mdbConnection->CommitTrans();
The thing is that the state is adStateExecuting for about 16 seconds
(30days interval), and afterwards drops to 0 (adStateClosed), while the
sp is still executing (which usually takes about a minute). The process
waits at the CommitTrans() until the sp is done. There aren't any errors
reported or exceptions thrown.
BUT: NO DATA IS FILLED IN THE TABLE.
I've tested that begin-commit works by adding a call to another sp, and
it works fine.
The weird thing is that the sp works if the execution takes less then
this 16 or so second limit. E.g. for 3days interval, it works fine.
The weirdest thing is that the whole thing worked at first, i.e. did not
produce erroneous state and filled the table, and that for 5 minutes
execution and more. Then I changed something in the sp (commented out a
single line!!), and ever since then it doesn't work anymore. I've got
the database backup from when it worked, but doesn't anymore.
The sp works fine when executed from Query Analyzer.
Has anyone got any ideas? Am I missing something?
Thanks,
Juhu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!