Re: ADO seemingly terminating batch

From: Dennis Forbes (DennisForbes_at_discussions.microsoft.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 07:25:04 -0700

I'm really hoping someone has some information on this:

-We are executing a procedure with ADO and the SQL Server OLEDB provider
using a command object with the adExecuteNoRecords option. It appears that
ADO is abandoning the batch when it sees a severity 16 error - this is
inconsistent because depending upon the output of the procedure, and the size
of the TDS packet, ADO usually doesn't see the error until the procedure
finishes, however if it does see the error (small TDS packet size or lots of
output from the procedure), including simple warnings such as a divide by
zero, it appears to be uncerimoniously aborting the batch, sometimes rolling
it back and other times promoting the intra-procedure begun transaction to a
connection transaction.

This does not occur if adExecuteNoRecords is not specified, and it can be
avoided by setting such a large TDS packet size that the server clutches onto
all of the error output until the procedure is complete. Either are hacks and
imply that this isn't the correct behaviour.

Can anyone offer any hints or tips on this? Is this what it is supposed to
be doing/

"Dennis Forbes" wrote:

> As one additional, and perplexing, bit of information, not only does
> the batch abort not occur if adExecuteNoRecords is not specified, the
> abort also doesn't occur if the TDS packet size has been set to an
> arbitrary larger value, for instance 16384 (va Packet Size=16384 on
> the OLEDB connection string).
>
> So to recap we call a procedure in SQL Server 7 from COM+ using
> ADO/OLEDB (MDAC 2.8) and a command object with adExecuteNoRecords
> turned on, and inexplicably the batch is terminating midstep,
> apparently on a trivial warning message such as a Divide by zero
> warning. Removing adExecuteNoRecords, or increasing the TDS packet
> size, and the batch no longer aborts. Adding some sort of output at
> the outset of the procedure, such as PRINT 'Test', also stops the
> batch from being aborted.
>
> We have several work arounds, but as this is a critical production
> system we don't want to shoehorn in a workaround without understanding
> this fault - is this a fault in ADO perhaps? (Is ADO on the client
> side pulling data from a TDS stream during the execution of a long
> running procedure, and if it saw something it didn't like would it
> terminate the batch?)
>

Relevant Pages

  • Re: ADO seemingly terminating batch
    ... -We are executing a procedure with ADO and the SQL Server OLEDB provider ... ADO is abandoning the batch when it sees a severity 16 error - this is ... of the TDS packet, ADO usually doesn't see the error until the procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: INSERT Delay
    ... While SQL Server may see them as ... > batch before) and causes them to handled as such. ... ADO does not do this unless you explicitly use a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: How can I force a pause in program execution?
    ... The problem is that the table creation isn't complete before ... > insert command fires. ... It is a batch delimiter recognized by SQL ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: testing @@error in SP
    ... Some errors in SQL Server cause the abortion of the T-SQL batch they are. ... the batch and can be handled with an error handler. ...
    (microsoft.public.sqlserver.programming)