ADO does not always return Errors collection from SQL 2000 
Author Message
 ADO does not always return Errors collection from SQL 2000

Hi,

I've written a VB6 program that uses ADO 2.6 to access SQL Server 2000 SP2.

When ADO calls a stored procedure on the server that subsequently calls
other stored procedures, and one of the nested procedures raises an error,
ADO frequently returns an empty Errors collection. The only way I know the

procedure.

How do I get ADO to return errors from nested stored procedures?

Thank you,

Mark



Wed, 19 Jan 2005 05:28:27 GMT  
 ADO does not always return Errors collection from SQL 2000
Have you tried to step through all of the resultsets returned? Often the
errors are exposed in the resultset causing the error--not in the one(s)
that work. Use set rs = rs.nextrecordset

hth

--
William (Bill) Vaughn
Author, Trainer, Mentor
Microsoft Pacwest Regional Director
Beta V Corporation
www.betav.com

Quote:
> Hi,

> I've written a VB6 program that uses ADO 2.6 to access SQL Server 2000
SP2.

> When ADO calls a stored procedure on the server that subsequently calls
> other stored procedures, and one of the nested procedures raises an error,
> ADO frequently returns an empty Errors collection. The only way I know the

> procedure.

> How do I get ADO to return errors from nested stored procedures?

> Thank you,

> Mark



Thu, 20 Jan 2005 01:37:46 GMT  
 ADO does not always return Errors collection from SQL 2000

Quote:

>How do I get ADO to return errors from nested stored procedures?

Does Bill's suggestion work for you?

--
Peter Wu
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.



Fri, 21 Jan 2005 14:10:06 GMT  
 ADO does not always return Errors collection from SQL 2000
Hi Peter,

Any further suggestions on this?

Thanks,

Mark Berry



Mon, 31 Jan 2005 07:31:29 GMT  
 ADO does not always return Errors collection from SQL 2000
Well, I've discovered one new piece to this puzzle:  iIf SQL uses raiserror
with a severity level of 10 or less, that should cause an ADO _InfoMessage
event (similar to a SQL print statement). However, an old ADO bug keeps this
from working if CursorLocation = adUseClient (see KB #Q294178).

Since I need disconnected recordsets, I have to use adUseClient. So I've now
gone through all my stored procedures and set the raiserror severity level
to 16 for all messages. This should cause a "normal" ADO Error and populate
the Errors collection.

Unfortunately, this did not solve the problem. I still have the behavior
described below:  the error only appears after issuing .NextRecordset, and
SecondRs still = Nothing.

Any ideas?

Thanks,

Mark Berry


Quote:
> Peter,

> My stored procs call many other stored procs, but in the end, one stored
> proc call from VB is supposed to return exactly one rowset to VB.  (I use

> I am also aware that each stored proc must begin with "set nocount on" and
> may not contain any "print" statements or produce any non-critical SQL
> errors (e.g. about NULL being ignored).

> Of course it's possible that I missed something, but I did a fair amount
of
> tracing and I can't see any problems in the procs.

> However, when I issue the statement

>   set SecondRS = FirstRS.NextRecordset

> the errors are raised and the Errors collection is populated. What I don't
> understand is why SecondRS is still Nothing after running that statement.
> Does the SET statement actually fail if an error is raised? Even when I
set
> "On Error Resume Next", I see the same behavior:  SecondRS is still
Nothing.

> If in fact two recordsets are being returned, I need to see the contents
of
> both so I can determine where each one is coming from.

> Thank you,

> Mark Berry



> > >Wait a sec - the Errors collection is part of the Connection object.
Even
> > if
> > >there are multiple result sets, they all come off one connection. Are
you
> > >guys saying the Errors collection gets repopulated with each
> > >rs.nextrecordset?



Tue, 01 Feb 2005 02:56:13 GMT  
 ADO does not always return Errors collection from SQL 2000
Simply put...No. Once ADO calls the stored proc it has no clue what is going on on SQL server.  If the stored proc calls another which calls
another it is up to the procedures themselves (And the code written by the programmer) to raise the errors back to the initial stored proc for it to
then let ADO know what happened. there are some situations coverred by KB Articles (Like multiple resultsets) where the RaiseError does not
rais the error and ADO misses it. The best solution I ever saw outlined (Keep in mind I am NOT well versed in TSQL Programming and how to
do this would best be answered on the microsoft.public.sqlserver.programming newsgroup) was to have each stored procedure pass back an
output parameter to teh previous stored proc which in turn passes it back to the root SP. Then pass the data back to the calling program (Not
the ADO Errors collection but via Output parameters) and have the program deal with what happened.

Scot Rose, MCSD
Microsoft Visual Basic Developer Support

Want to know more? Check out the MSDN at msdn.microsoft.com or the Microsoft Knowledge Base at support.microsoft.com

This posting is provided AS IS, with no warranties, and confers no rights.

--------------------

Quote:

>Subject: ADO does not always return Errors collection from SQL 2000
>Date: Fri, 2 Aug 2002 14:28:27 -0700
>Lines: 17
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000

>Newsgroups: microsoft.public.vb.database.ado
>NNTP-Posting-Host: 64.130.220.213
>Path: cpmsftngxa07!tkmsftngxs01!tkmsftngp01!tkmsftngp12
>Xref: cpmsftngxa07 microsoft.public.vb.database.ado:73437
>X-Tomcat-NG: microsoft.public.vb.database.ado

>Hi,

>I've written a VB6 program that uses ADO 2.6 to access SQL Server 2000 SP2.

>When ADO calls a stored procedure on the server that subsequently calls
>other stored procedures, and one of the nested procedures raises an error,
>ADO frequently returns an empty Errors collection. The only way I know the

>procedure.

>How do I get ADO to return errors from nested stored procedures?

>Thank you,

>Mark



Tue, 01 Feb 2005 22:38:01 GMT  
 ADO does not always return Errors collection from SQL 2000
Scot,

Thanks for your message. Just to confirm:  it's impossible to return
non-fatal messages from SQL to a disconnected recordset:

- if I use raiserror with a severity <= 10, it doesn't fire the _InfoMessage
event because CursorLocation is adUseClient (Q294178).

- if I use raiserror with a severity >= 11, it raises an error in VB as
desired, but it returns a closed recordset so the VB program cannot procede
(Q313861).

Yes, I'm sure I could return custom parameters to do the error handling,
though that's a lot of extra work considering the provider is already making
the messages and recordset available, it's just ADO that is preventing me
from seeing them. Am I missing something here? Is there another alternative,
e.g. .NET, that works differently?

Mark


Quote:
> Simply put...No. Once ADO calls the stored proc it has no clue what is

going on on SQL server.  If the stored proc calls another which calls
Quote:
> another it is up to the procedures themselves (And the code written by the

programmer) to raise the errors back to the initial stored proc for it to
Quote:
> then let ADO know what happened. there are some situations coverred by KB

Articles (Like multiple resultsets) where the RaiseError does not
Quote:
> rais the error and ADO misses it. The best solution I ever saw outlined

(Keep in mind I am NOT well versed in TSQL Programming and how to
Quote:
> do this would best be answered on the

microsoft.public.sqlserver.programming newsgroup) was to have each stored
procedure pass back an
Quote:
> output parameter to teh previous stored proc which in turn passes it back

to the root SP. Then pass the data back to the calling program (Not
Quote:
> the ADO Errors collection but via Output parameters) and have the program

deal with what happened.
Quote:

> Scot Rose, MCSD
> Microsoft Visual Basic Developer Support




Wed, 02 Feb 2005 06:21:11 GMT  
 ADO does not always return Errors collection from SQL 2000
In all honesty, I have not gone down this path in .Net yet, I do not know how the error might be passed to the dataset, but with ADO, the couple of
KB articles you pointed out seem to neatly box you in with ADO, so pretty much the only way there is to pass the info back to VB in parameters.
You might pose the question in one of the DOTNET forums and see if anyone else has run into any problems with this in .net.

Scot Rose, MCSD
Microsoft Visual Basic Developer Support

Want to know more? Check out the MSDN at msdn.microsoft.com or the Microsoft Knowledge Base at support.microsoft.com

This posting is provided AS IS, with no warranties, and confers no rights.

--------------------

Quote:


>Subject: Re: ADO does not always return Errors collection from SQL 2000
>Date: Fri, 16 Aug 2002 15:21:11 -0700
>Lines: 44
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000

>Newsgroups: microsoft.public.vb.database.ado
>NNTP-Posting-Host: 64.130.220.213
>Path: cpmsftngxa06!tkmsftngp01!tkmsftngp12
>Xref: cpmsftngxa06 microsoft.public.vb.database.ado:75042
>X-Tomcat-NG: microsoft.public.vb.database.ado

>Scot,

>Thanks for your message. Just to confirm:  it's impossible to return
>non-fatal messages from SQL to a disconnected recordset:

>- if I use raiserror with a severity <= 10, it doesn't fire the _InfoMessage
>event because CursorLocation is adUseClient (Q294178).

>- if I use raiserror with a severity >= 11, it raises an error in VB as
>desired, but it returns a closed recordset so the VB program cannot procede
>(Q313861).

>Yes, I'm sure I could return custom parameters to do the error handling,
>though that's a lot of extra work considering the provider is already making
>the messages and recordset available, it's just ADO that is preventing me
>from seeing them. Am I missing something here? Is there another alternative,
>e.g. .NET, that works differently?

>Mark



>> Simply put...No. Once ADO calls the stored proc it has no clue what is
>going on on SQL server.  If the stored proc calls another which calls
>> another it is up to the procedures themselves (And the code written by the
>programmer) to raise the errors back to the initial stored proc for it to
>> then let ADO know what happened. there are some situations coverred by KB
>Articles (Like multiple resultsets) where the RaiseError does not
>> rais the error and ADO misses it. The best solution I ever saw outlined
>(Keep in mind I am NOT well versed in TSQL Programming and how to
>> do this would best be answered on the
>microsoft.public.sqlserver.programming newsgroup) was to have each stored
>procedure pass back an
>> output parameter to teh previous stored proc which in turn passes it back
>to the root SP. Then pass the data back to the calling program (Not
>> the ADO Errors collection but via Output parameters) and have the program
>deal with what happened.

>> Scot Rose, MCSD
>> Microsoft Visual Basic Developer Support




Fri, 04 Feb 2005 06:16:38 GMT  
 ADO does not always return Errors collection from SQL 2000
Scot,

Thanks for the follow-up.

Mark



Sat, 05 Feb 2005 01:07:23 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. MS SQL raiseerror not returned to ADO error collection

2. MS SQL raiseerror not returned to ADO error collection

3. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

4. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

5. ADO 2.6 Error Collection and SQL Server 2000

6. SQL Server 2000 stored procedure not returning rows

7. SQL Server 2000 stored procedure not returning rows

8. doing updating from grid to sql server with stored procedure sql 2000

9. Error when returning from SQL Server 2000

10. Unable to return float, always returning int with ADO, IIS and SQLServer

11. ADO Not releasing memory on SQL Server 2000

12. ADO error trapping in VB + SQL Server 2000

 

 
Powered by phpBB® Forum Software