I'v known for a while that ado has it's limits when it comes to dealing with errors... but this one hurts:
I have a StoredProcedure (SP) that calls a sub procedure.
In the sub procedure an error is raised.
The error does not reach ADO for some reason.
After some research I find that if the sub procedure returns a result set before it raises the error this problem occurs, else, the error reaches ADO just fine.
CREATE PROCEDURE sp_test
CREATE PROCEDURE [dbo].[sp_error]
/* IF THIS LINE IS HERE, ERROR DO NOT SHOW UP IN ADO */
SELECT 'test' AS value
RAISERROR ('this is an error', 16, 1)
VBSCript (ASP) code:
dim oConn, oRS
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionString = ConnString
oConn.CommandTimeout = 180
Set oRS = Server.CreateObject("ADODB.recordset")
oRS.activeconnection = oConn
oRS.cursortype = 3
oRS.locktype = 4
oRS.CursorLocation = 3
oRS.source = "EXEC test.dbo.sp_error"
SET oRS = Nothing
SET oConn = Nothing
Now, I understand that I could work around this, by testing for the error in the main procedure etc... BUT THIS SHOULD JUST WORK!!!