Re: SQL 2000 Stored Procedure Problem

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











.