July 2, 2003 at 5:02 am
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.
July 2, 2003 at 6:00 am
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.
July 10, 2003 at 7:54 am
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
July 10, 2003 at 8:29 am
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.
July 11, 2003 at 4:20 am
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
September 29, 2004 at 12:42 pm
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
September 29, 2004 at 1:22 pm
Did you try the NextRecordset as jpipes mentioned? You may have 4 recordsets with one error on each.
September 29, 2004 at 1:35 pm
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
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply