Returning stored procedure messages via ADO

Question

Returning stored procedure messages via ADO

alanacp

SSC Veteran

Points: 221
More actions
July 2, 2003 at 5:02 am

#81933

I am programming an application in VB that uses ADO to run a stored procedure to back up a selected database. How can i return the messages that appear if i run the query in query analyser to my VB application so i can populate an application log.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

You can use the Errors Collection of the ADODB.Connection object to obtain error information returned from SQL Server. Be sure, however, that you stored procedures properly issue a RAISERROR statement if they are doing their own error-checking. For instance, if your procedure contained the following:



-- Do some update stuff here

IF @@ERROR <> 0 BEGIN

  RETURN 1

END

The ADODB.Connection Error Collection would not contain any error information, since you essentially suppressed it with the RETURN 1 statement. The following will populate the Errors Collection:



-- Do some update stuff here

IF @@ERROR <> 0 BEGIN

  RAISERROR('My error description',16,1)

END

For more information, look up Books Online for RAISERROR.

HTH, jay.

alanacp SSC Veteran Points: 221 More actions · Answer 2

It is not an error that I am trying to return, it is a message. I am using the stored procedure to backup a copy of a database to another folder and i want to be able to note the work carried out by the query. If I do this in query analiser it notes how many pages have been copied etc. It is this inmformation that i want to return

jpipes SSChampion Points: 12230 More actions · Answer 3

Anything returned in the Query analyzer is available in the Recordsets collection in ADO. Just issue a call to Recordset.NextRecordset to move to the next set of returned data in your procedure. You can look up on MSDN for more info; just be sure to wrap everything into a stored procedure and investigate using PRINT and SET NOCOUNT ON to control the way the procedure returns resultsets. HTH.

alanacp SSC Veteran Points: 221 More actions · Answer 4

Thanks for that. I've used the ADO errors collection to return the messages as they all had a severity of 10 and were not returned by the @@ERROR functionality.

Cheers

quentinjs SSC Enthusiast Points: 180 More actions · Answer 5

I'm using Delphi 7, SQL Server 7, and have raised 4 errors within a stored proc. The Query analyzer shows them fine. When I ask the ADO connection for the errors, it says I have only 1 error, and shows only the first one.

Tracing through delphi source - all its doing is accessing a com interface for the error collection.

so my question is 3 fold.

1. Is there a way to get the entire error collection?

2. Is there another way to get all this information?

3. Is there a driver issue I should be looking at?

I can be reached at quentinjs at canada dot com as well as this forum.

Thanks

Quentin

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 6

Did you try the NextRecordset as jpipes mentioned? You may have 4 recordsets with one error on each.

quentinjs SSC Enthusiast Points: 180 More actions · Answer 7

Just did. The catch is, the stored proc in question doesn't return any result sets normally. I've also tried just referencing the record count as well.

Create PROCEDURE q_test

AS

declare @e int, @a int

begin transaction

Set @a = 0

RAISERROR ('AAAAAAAAAA', 17, 1)

RAISERROR ('BBBBBBBBBB', 17, 1)

Select 4 / @a

Select @e = @@ERROR

if @e <> 0 begin

RaisError('The variable A should not be zero', 16, 1)

Rollback

Return(@e)

end

RAISERROR ('CCCCCCCCCCC', 17, 1)

commit

return(0)

GO