ADO seemingly terminating batch
From: Dennis Forbes (dennis.forbes_at_gmail.com)
Date: 09/28/04
- Next message: Arnie Mauer: "ADO _ConnectionPtr in a DLL"
- Previous message: mffl: "XP SP2 and ADODB Stream Object"
- Next in thread: Dennis Forbes: "Re: ADO seemingly terminating batch"
- Reply: Dennis Forbes: "Re: ADO seemingly terminating batch"
- Messages sorted by: [ date ] [ thread ]
Date: 28 Sep 2004 07:52:46 -0700
Good day to you.
We are currently experiencing a serious problem with a production
system relating to SQL Server and ADO - This is a batch processing
system running against SQL Server using ADO/OLEDB MDAC 2.8 to
communicate.
In essence the batch processing system is a COM+ component that is
provided with a set of ids, upon which it sequentially calls a worker
procedure in the SQL Server 7.00.961 hosted database for each id using
ADO/OLEDB, and that procedure then performs all of the processing
steps for that id. Each per id procedure in SQL Server begins with a
begin tran if the @@trancount is 0, and ends with a commit or a
rollback if it opened one itself (otherwise presuming that the caller
is managing the transaction), acting as atomic operations. Within each
procedure if an error is discovered RAISERROR is called with a
severity of 16 using custom errors and programmatic flow continues,
usually returning out to the per id procedure where the transaction is
rolled back and execution continues on the next id.
The issue we have encountered, and this was only observed within the
past few months (thus may relate to recent patches), is that some
batches are terminating in midstream, occasionally being rolled back
atomically, while other times it is leaving a transaction open on the
connection. For instance in one process we have identified a divide by
zero warning message as being the point at which the batch apparently
terminates - Note that the settings are such that a divide by zero
does not abort the batch, and furthermore the paragraph following
proves that this should not be the expected behaviour.
To make matters more complex this batch termination only occurs if
adExecuteNoRecords is passed as an option to the Execute method of the
command object. If this option is not set the batch continues as
expected. Furthermore even with adExecuteNoRecords if either a PRINT
or RAISERROR with a severity of 10 is executed before the point at
which the batch abort is occurring, the batch continues to completion.
Does anyone have any ideas of what could be causing this behaviour? Is
this a bug in TDS/ADO/OLEDB when a certain sequence of events occur
and it aborts the batch from the client end? Anyone have any tips on
how to better diagnose this? Unfortunately we are experiencing this in
highly complex procedures including many levels of calls, triggers,
DBCCs or bcps, etc, and thus far I have been unable to artificially
duplicate this in test cases, so there is some factor that has yet
been unidentified.
- Next message: Arnie Mauer: "ADO _ConnectionPtr in a DLL"
- Previous message: mffl: "XP SP2 and ADODB Stream Object"
- Next in thread: Dennis Forbes: "Re: ADO seemingly terminating batch"
- Reply: Dennis Forbes: "Re: ADO seemingly terminating batch"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
