Capturing SQL Stored Procedure Return Codes

[Rod Behr]

An SQL stored procedure returns 0 if it completes correctly and a negative
number to reflect an error.

When calling a stored procedure from Access, how do you capture this return
integer in order to capture whether the called stored procedure has succeeded?

My VB syntax would be:

Set cn = New ADODB.Connection
cn.ConnectionString =
"DSN=TheFactory;DATABASE=TheFactory;Uid=Factory;Pwd=1mpact"
cn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_name"
intReturnValue = cmd.Execute
cn.Close

This is all lovely, but I cannot then manipulate/use intReturnValue. It
should be an integer, but it isn't. I cannot execute a print.debug
intReturnValue as this returns error "Wrong number of arguments or invalid
property assignment".

How do I capture return codes from a SQL Stored Procedure?

 

[Paolo]

Hi Rod,
to trap the error do as follows

on error goto err_handler

Set cn = New ADODB.Connection
cn.ConnectionString =
"DSN=TheFactory;DATABASE=TheFactory;Uid=Factory;Pwd=1mpact"
cn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_name"
intReturnValue = cmd.Execute
cn.Close

exit sub

err_handler:

'this is to display the error number and exit the sub
msgbox "Occurred error #" & err, vbOKOnly, "Warning"
end sub

HTH Paolo

 

[Rod Behr]

Hi Paolo

I'm not talking about a standard error handler to manage the VB error event.
That I have later in the code similar to your example.

My point is that I can force the SQL stored procedure to return a negative
value (by simply telling it to return (-1) GO). It thus returns -1 to the VB
script but this is ignored.

 

[Paolo]

I don't think cmd.Execute could return 0 or -1. The Access help states as
follows:

Execute Method (ADO Command)
Executes the query, SQL statement, or stored procedure specified in the
CommandText property.

Syntax
For a Recordset-returning Command:

Set recordset = command.Execute( RecordsAffected, Parameters, Options )
For a non–recordset-returning Command:

command.Execute RecordsAffected, Parameters, Options

Return Value
Returns a Recordset object reference or Nothing.

Cheers Paolo

 

[Sylvain Lafontaine]

In your case, cmd.Execute probably returns a recordset and as you didn't put
a SET command before the assignation, some default value has gone into the
intReturnValue variable. If you want to capture the return value of a
procedure, the easiest way would be to use the Parameters collection when
calling your SP. If the SP return a value, its value will always be in the
first parameter of the Parameters collection, with a name of "Return_Value"
(or "@Return_Value" (???) if you have used the .Refresh method to create
your parameters collection).

Search the internet for "parameter return_value". Don't forget that this
parameter must always be the first parameter added to the parameters
collection if you create it yourself. Here's an exemple on how to use the
refresh method used to determine the properties of each parameter:

' cmd should be a valid ADODB.Command object with a valid connection:
cmd.Parameters.Refresh


Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

For a description of the constant values for the Type field, see the file
adovbs.inc (there is also a version for javascript) that you will find on
your machine or at the following reference or in various place on the
internet:

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122