Greetings,

Here is my situation...I am hoping someone can provide a suggestion or two....

I have an ASP page that, via ADO, calls a SQL SERVER 2000 Stored Procedure. This stored procedure uses RaisERROR to raise my predefined error messages.

Given the parameters I supplied, the procedure should raise 2 errors ( I put it this way, because I am testing right now). When I execute this procedure in Query Analyzer I receive both error messages, as I am supposed to. Fine. Great. No Problem there.

However, when I execute the stored procedure via the ASP page, I only recieve one error. I verified that the input parameters are exactly the same when run through QA, as when run from the ASP page.

To exec the proc, and display the errors I do the following:
dim cmd, rs
set rs = server.createobject("ADODB.Recordset")
set cmd = Server.CreateObject("ADODB.Command")

with cmd
.NamedParameters = TRUE
.CommandType = adCmdStoredProc
.CommandText = "MyProcNameHere"
.ActiveConnection = conn
End with
(Conn has only the basics in the connection string - Provider, Initial Catalog, uid, pwd, and data source (IP Address))


Then I add the appropriate params and execute


'Then further down, to display the errors I do the following:
for each sError in conn.errors
sErrMessage = sErrMessage & sError.description "<BR><BR>"
next

The first error comes out just fine.....but the second error isn't even there.

I have searched for hours trying to find documentation on the subject, but have found nothing applicable. Anyone have suggestions?

Othe applicable info: Stored Proc has nocount on, severity level of error messages is 5, RaisError messages were added to sysmessages table, severity levels match in sysmessages and in raisError call.

Other than that everything is pretty straight forward
Thanks ahead of time,

Matt

P.S. I also looked at the err object (same as the error in error collection

You need to loop through the error collection using a for-each to retrieve all errors.

Here is an example:

Set cn =server.CreateObject("ADODB.Connection")

cn.Open "XXX"

... more code

i=0
For Each error_item in cn.Errors
response.write cn.Errors(i).Description &"<br>"
response.write cn.Errors(i).NativeError &"<br>"
i=i+1
Next

can't thins

i=0
For Each error_item in objConn.Errors
response.write cn.Errors(i).Description &"<br>"
response.write cn.Errors(i).NativeError &"<br>"
i=i+1
Next

be changed to something more elegant like....

For Each error_item in objConn.Errors
response.write error_item.Description &"<br>"
response.write error_item.NativeError &"<br>"
Next

??

Elegance versus performance - your choice.

Why should performance be altered due to the change??

Since ado is extremely flexible in how you can retrieve information (retrieve the same information in many different ways) - some methods are quicker than others.

I agree with what you are saying,... that is..

"some methods are quicker than others."

but.... you have already indexed into the collection with your for each...

reindexing into it using the integer you created is just a extra step...

if you did this....

For i = 0 to objConn.Errors.Count
response.write cn.Errors(i).Description &"<br>"
response.write cn.Errors(i).NativeError &"<br>"
Next

instead of

i=0
For Each error_item in objConn.Errors
response.write cn.Errors(i).Description &"<br>"
response.write cn.Errors(i).NativeError &"<br>"
i=i+1
Next

I would accept the increased speed claim....

I appreciate all of the help so far.

Actually I already have the code to loop through the erros collection. (And actually I tried using the For Each method as well as the index method).

The problem I am having is that there is only one error to loop through. The collection only shows one error. I did something like this:
Response.write "Num of errors=" & conn.errors.count & ".<BR>"

THere is only one error in the collection, so the output is as follows
Num of errors=1.
and the one error message is displayed.

However, I KNOW there are two errors encountered (via the dry run in SQL Query Analyzer), and both messages "bubble up" when run through the QA.

So I hope that clears things up a little....
I can loop through the errors collection just fine, it just has less errors than it is supposed to have.

A note that may be important*******
The error that IS returned is the first error that was encountered. the error that is not returned, is the second error encountered.

Again thanks for the suggetions so far!!!

Matt

This sounds like your stored procedure is returning multiple recordsets. If this is the case, then you would have to loop through the recordsets. Can you provide the code for the sp (or if it is too long then just the basic steps and what is returned).

Yes, the "Parent" stored procedure does return multiple recordsets. I did make sure that I am looping through all recordsets returned, though. Actually after a little research, that was one of the first few things I tried. (Would include code...but there is too much).

Also (maybe I left this out earlier), we ARE dealing with Nested procedures. Procedure A calls Procedures B & C....Procedure B calls Procedures D & E...etc....But for now...we will just stick to A, B & C (all other calls have been commented out)

Also...I found out a little more about what MAY be happening...this may spark some new ideas as well.

The stored procedure calls two other stored procedure...each of the called procedures are supposed to return an error. What I have noticed is as follows:

THE FIRST PROCEDURE TO RAISERROR WINS!...meaning that if procedure A calls procedure B and then procedure C. If Procedure B raises an error and procedure C raises an error....procedure B's error is returned, but it does continue processing, and does call procedure C (as it should), it just doesn't return the error to the ADO Conn.

If procedure B DOES NOT raise an error and procedure C DOES, then procedure C's error is returned.

Furthermore, if I start procedure A off with multiple RaisERROR statements(meaning the errors are raised before the calls to B & C)....and procedures B and C both raise an error....only procedure A's errors are returned.

SO what I have noticed...I guess... is the following:

Only the errors encountered inside of the FIRST PROCEDURE TO ENCOUNTER AN ERROR...get returned. I hope that makes sense to you all.

I can return multiple errors...but only if they are all encountered inside of the same stored procedure....and no errors were raised prior to that, in other procedures.

This leads me to believe that this may be more of a "settings" issue...meaning that i have to "set" something on or off...or include a parameter in the ADO connection object...or something like that. (I have "set nocount on", in all procedures).


Thanks for your help so far I really appreciate it..... and
Thanks in advance,

Matt

P.S. I hope all of that made sense....

Have you tried using the oledb odbc driver (not the sqloledb driver) ? And where do you have the set nocount on option set - in the stored procedure or in the asp page ?

The set nocount on statement is at the top of every stored procedure used.

And I may just be a little foggy this morning but what exactly do you mean by oledb odbc driver? Do you have an example connection string, I could look at? Right now...we are using the following:

"Provider=SQLOLEDB.1;DataSource=IPAddress;Init ial Catalog=MyDB;uid=UserName;pwd=PWord"

we also tried:

"Provider=MSDASQL;Driver=(SQL Server)Server=ServerName;Database=MyDB;uid=UserNam e;pwd=Pword"

However...that driver is not on the web server....so tht option was quickly thrown aside.

At this point....I do honestly think it IS a driver issue, simply because I think I have tried everything else that I have found documentation on, and none of that seemed to work.


Thanks for the help!

Install that msdasql driver - also use the "set nocount on" in your vb code before you call the stored procedure.