• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1294
  • Last Modified:

Catching RaiseError messages in VB

Given this stored procedure in MS SQL 2000 server:

CREATE PROCEDURE SG_Admin.usp_GetSG
@SG_ID int,
@Records int OUTPUT
 AS
set nocount on
Select * from SG where SG_ID = @SG_ID

Select @Records = Count(*) from SG where SG_ID = @SG_ID
If @Records > 1
begin
  RAISERROR ('There cannot be two of these!' , 14 , 1)
  return -1
end
return 0
GO

How can I get the text of the error that is being raised?  I'm going through the errors collection of the connection object but it doesn't appear in it.  I know it getting there because I'm getting the return value of -1.  Here's the VB code I'm using to put the errors into a portion of an XML document.

  For Each error_item In con.errors
    strResult = strResult & "<Error Num='" & error_item.NativeError & "'>"
    strResult = strResult & error_item.Description
    strResult = strResult & "</Error>"
  Next

If I move my RaiseError to the first line of the stored procedure the VB code sees it but that isn't to useful for a production system.  I did get other errors for example if I put an invalid table name in one of the selects.

Thanks,
Allen
0
allenstoner
Asked:
allenstoner
  • 8
  • 3
  • 3
  • +3
1 Solution
 
allenstonerAuthor Commented:
I'm using VB6 sp5 with Ado 2.6 in case it matters.

Allen
0
 
Dave_GreeneCommented:
Are we talking...

Err.Raise ??
0
 
allenstonerAuthor Commented:
I don't think so.  It has more to do with the Errors collection of an ADO connection.  The RaiseError is a t-sql command.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Dave_GreeneCommented:
Gotcha.  Interesting
0
 
allenstonerAuthor Commented:
I don't think so.  It has more to do with the Errors collection of an ADO connection.  The RaiseError is a t-sql command.
0
 
allenstonerAuthor Commented:
I don't think so.  It has more to do with the Errors collection of an ADO connection.  The RaiseError is a t-sql command.
0
 
Dave_GreeneCommented:
Use Reload Question in the upper right instead of refresh  :)  It will stop the duplicate posts
0
 
allenstonerAuthor Commented:
 Yeah, I figured out a little too late that the refresh was adding my comment again and again.

Thanks
0
 
ibroCommented:
try to replace
RAISERROR ('There cannot be two of these!' , 14 , 1)
with
RAISERROR ('There cannot be two of these!' , 16 , 1)

16 level of severity is most commonly used in raiseerror.
Maybe VB interprest severity of 14 just as warning.
 hope this helps
0
 
allenstonerAuthor Commented:
Doesn't make a difference.  I even tried 24 which is to be a fatal error although it may not be allowed to be generated by user processes.  VB does see the 14 IF it's the first line in the stored procedure.

Allen
0
 
nigelrivettCommented:
Does the VB go to the error handler.
I notice that you produce a recordset before raising the error - have you tried dealing with that first or disconnecting the recordset - the error info may be waiting to be passed after that.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Try this:
RAISERROR ('There cannot be two of these!' , 16 , 1) WITH SETERROR

CHeers

0
 
allenstonerAuthor Commented:
Here is the last part of the VB code.  I build the command object above this then this code processes what is returned.  So the recordset is being closed before I'm accessing the error collection of the connection.  I also tried the 'With SetError' but that didn't seem to do anything either.  Has anyone else seen this problem or am I the only one?

Thanks

  On Error GoTo BuildResult
  cmd.ActiveConnection = con
  Set rs = cmd.Execute
 
  '  Save the recordset to a stream for returning
  rs.Save stmRs, adPersistXML
  intRecords = rs.RecordCount
  rs.Close             '  Close and delete the recordset
  Set rs = Nothing
   
  '  Put output parameters include return value in an xml string for returning
BuildResult:
  strResult = "<SQLResults Records='" & intRecords & "' Errors='" & con.Errors.Count & "'>"
  strResult = strResult & "<Returns>"
  For Each Param In cmd.Parameters
    If (Param.Direction = adParamOutput) Or _
       (Param.Direction = adParamInputOutput) Or _
       (Param.Direction = adParamReturnValue) Then
      strResult = strResult & "<" & Param.Name & ">"
      strResult = strResult & Param.Value
      strResult = strResult & "</" & Param.Name & ">"
    End If
  Next
  strResult = strResult & "</Returns>"
  '  If there were errors put them in the xml file for returning
  strResult = strResult & "<Errors>"
  For Each error_item In con.Errors
    strResult = strResult & "<Error Num='" & error_item.NativeError & "'>"
    strResult = strResult & error_item.Description
    strResult = strResult & "</Error>"
  Next
  strResult = strResult & "</Errors>"
  strResult = strResult & "</SQLResults>"
  GoTo Clean_Up

'  Build an error response
errorHandler:
  strResult = "<SQLResults Records='0' Errors='1'>"
  strResult = strResult & "<Returns/>"
  strResult = strResult & "<Errors>"
  strResult = strResult & "<Error Num='" & Err.Number & "'>"
  strResult = strResult & Err.Description
  strResult = strResult & "</Error>"
  strResult = strResult & "</Errors>"
  strResult = strResult & "</SQLResults>"
 
Clean_Up:
  On Error Resume Next
  If Not (con Is Nothing) Then
    con.Close
  End If
  Set con = Nothing
  GetData = strResult                 '  Send strResult back as function result
  On Error GoTo 0
End Function
0
 
nigelrivettCommented:
Have heard of other people having this problem.
As it is OK when you raise the error without the recordset then I guess it is the recordset that is causing the problem.
Why not return this as an output parameter.
0
 
allenstonerAuthor Commented:
How do I return a recordset as an output parameter?
0
 
nigelrivettCommented:
I meant the @Records or the erroer message.
0
 
mironCommented:
I saw rs.NextRecordset for retrieving Error record. Is it possible that there are some select statements, that failed prior to the RAISERROR?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hi allenstoner.
I can confirm that the .nextrecordset will solve your problem.
As you have in fact 2 select statements prior to your RAISERROR, you need to use the .nextrecordset to catch the error...

BTW, you might replace the
Select @Records = Count(*) from SG where SG_ID = @SG_ID
by
SET @Records = @@rowcount

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 8
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now