Raising Errors from SQL Server 7

[bbosley]

I am developing with VB 6 SP4, MDAC 2.5, SQL server 7.

I am currently having difficulty with raising errors back to VB from a SQL 7 sproc. Within the sproc I call &quot;sub&quot; sprocs to lookup different values (factors) and return 0 if success and 1 if the factor is not found. The main sproc works fine with the sub sproc. The problem lies when a 1 is returned from the sub sproc (no record found) I check the return value and IF @intRetVal <> 0 I raise an error with the following code:

EXEC @intRetVal = sproc @intVar1, @intVar2
IF @intRetVal <> 0
BEGIN
SET @chvErrorMessage = 'Error in sproc'
RAISERROR (@chvErrorMessage,11,1)
GOTO ErrorHandler
END

ErrorHandler:
RETURN (@intRetVal)

The following code is how I call the sproc from VB:

Set cmd3 = New ADODB.Command

With cmd3

.ActiveConnection = conRating
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter(&quot;QuoteID&quot;, adInteger, adParamInput, 4)
.Parameters.Append .CreateParameter(&quot;PlanID&quot;, adInteger, adParamInput, 4)

.Parameters.Item(0).Value = frmRate.txtQuote
.Parameters.Item(1).Value = frmRate.txtPlan

.CommandTimeout = 0 'Override default of 60 seconds

.Parameters.Append .CreateParameter(&quot;RequestUserID&quot;, adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter(&quot;RequestDateTime&quot;, adDate, adParamInput, 8)

.Parameters.Item(2).Value = fMDIMain.pstrLogin
.Parameters.Item(3).Value = dteRatingStart

.CommandText = &quot;dbo.spr_BasicLifeRating&quot;
.Execute , , adAsyncExecute

End With

GoTo Cleanup
errHandler:

'error routine

Cleanup:

End Sub
--------------------------

The conRating connection object is declared WITHEVENTS and that seems to be working fine.

I have tried the RAISERROR with severity 11, 16, 18 and state 127 but nothing will trigger the VB error handler. When I return from a sproc that has an error I have even tried checking the COUNT of the errors collection and it is 0. I have tried returning the value from the .EXECUTE to a recordset and checking that rs.fields(0).value which returns one of the SELECT '*** information for debugging ***' values instead of the 0 or 1 that is in the RETURN(0) at the end of the sproc.

Any ideas would be greatly appreciated.

 

[cdukes]

As far as I am aware, RAISEERROR will not raise a trappable error in VB, but will internally raise an error in SQLServer as can be seen in SQLServer Logs ( you should find your errors here ).

You are not getting a return value from your SP as you have not defined one. You should add as the first paramter:

.Parameters.Append .CreateParameter(&quot;RETURN_VALUE&quot;, adInteger, adParamReturnValue, 4)

Alternatively so that you do not have to define all parameters you can use:

.Parameters.Refresh

Which will Check with SQL Server what the parameters that the SP expects.

You must also be aware that you are executing the stored procedure Asyncronously, so any return value will not be available until the SP has completed the processing.

You should find that once the stored procedure has finished processing, any RAISERRORS will be put into the Connection.Errors collection, but NOT until the SP has finished processing.

Hope this helps,


Chris Dukes