Erland

Coincidently - I searched google for "SQL Stored Procedure Errors" and found
a GREAT tutorial on the subject.  Happens that it is your (Erland) site !
http://www.sommarskog.se/error-handling-II.html

I made some mods to the failing SProc and zeroed in on INSERT statement.   I
used a few Blocks of code (from your tutorial) in the SProc to force errors
to see what happened.  The "Force Error" block works as expected when it is
before the "INSERT" - but when it's after the INSERT the app does not get
the error message and the recordset fails to OPEN. If I move the "-- BLOCK:
Trap Error"  Just After the INSERT - No error message is returned to the
app.

'---- The Test SProc:
SET NOCOUNT ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE spTestSproc11  AS

CREATE TABLE #MyTempTable
(
FinalReturnValue varchar(100)
)

DECLARE @err int
DECLARE @forceError int

INSERT INTO #MyTempTable (FinalReturnValue) VALUES ('spTestSproc11 with Temp
table')

-- BLOCK:  Force Error to see where this fails.
--   If this block is above the INSERT the client app gets it
--   If After the INSERT client does NOT see it
SELECT @forceError = 0
IF @forceError = 0
  BEGIN
     RAISERROR ('Forced Error ', 16, 1)
     RETURN 50000
  END
-- END BLOCK

-- BLOCK: Trap Error
SELECT @err = @@error
IF @err <> 0
 BEGIN
     RAISERROR ('INSERT Error ', 16, 1)
     --RETURN @err
RETURN 50001
  END
-- END BLOCK

SELECT  FinalReturnValue FROM #MyTempTable
SELECT @err = @@error
IF @err <> 0
 BEGIN
     RAISERROR ('SELECT Error ', 16, 1)
     --RETURN @err
RETURN 50002
  END

DROP TABLE #MyTempTable

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---------------------------------------------------
Here is the VB Code:  - Note: This works on 3 dev PCs , the OldServer (SQL
2000 SP3) and ONLY Fails on NewServer (SQL 2000 SP4)
The recordset remains State= Closed  after the OPEN statement.

Private Sub cmdDevtest_Click(Index As Integer)
'--  Test  some simple SPROCs
   On Error GoTo Handler

   Dim sSql As String
   Dim objRsDetails    As ADODB.Recordset
   Dim objConn         As ADODB.Connection
   Dim tracer As String

   Set objConn = New ADODB.Connection
   objConn.Open CONNECTION_STRING, "", ""
   Set objRsDetails = New ADODB.Recordset

   If TextSpName.Text <> "" Then
       sSql = " exec " & IpDatabaseOwner & TextSpName.Text
   Else
       If Index = 0 Then
          sSql = " exec " & IpDatabaseOwner & "spTestSprocSimple "
       Else
          sSql = " exec " & IpDatabaseOwner & "spTestTempTable "
       End If
   End If

   tracer = "1"
   objRsDetails.Open sSql, objConn

   tracer = "2 RS State = " & objRsDetails.State
   If Not (objRsDetails.EOF Or objRsDetails.BOF) Then
       tracer = "3"
       objRsDetails.MoveFirst
   End If

   tracer = "4"
   frmMain.txtPreview.Text = Now() & "  " & objRsDetails.Fields(0).Value

ExitMe:
   On Error Resume Next
   objRsDetails.Close
   Set objRsDetails = Nothing
   objConn.Close
   Set objConn = Nothing
   Exit Sub

Handler:
   MsgBox "ERROR: " & Err.Description & vbCrLf & "SQL = " & sSql & vbCrLf &
" OConn = " & objConn & vbCrLf & " Tracer = " & tracer
   Resume ExitMe
End Sub