RAISERROR() is invalidated by xp_fileexist function when run through ADO (example included)

Help! Does anyone know why (and how to work around) this situation

I have a stored procedure in MSSQL 2000 SP3 defined as follows:

CREATE PROCEDURE sp_mycheck
AS
BEGIN
declare @ret int
EXEC master.dbo.xp_fileexist 'C:\temp\bogus.txt', @ret OUTPUT
RAISERROR ('A fatal error occurred',16,1)
RETURN (16)
END
GO

In isqlw an 'exec sp_mycheck' operation will display the expected error:
Server: Msg 50000, Level 16, State 1, Procedure sp_mycheck, Line 6
A fatal error occurred

Executing the stored procedure via the ADO, however, does not cause an error.   If you comment out the xp_fileexist line in the stored procedure, however, the error will appear. Here is a simple javascript app to demonstrate. (The bahavior is the same as my regular programming language)

From the command line enter: cscript runstoredproc.js

// ***** MODIFY ME FOR YOUR ENVIRONMENT
database="northwind";
server="localhost";
// ***** END USER MODIFICATIONS
adStateOpen = 0x00000001;
connStr = "PROVIDER=SQLOLEDB;Server=" + server + ";database=" + database + ";Integrated Security=SSPI";
connection = new ActiveXObject("ADODB.Connection");
// at last ... execute the query
try {
result = connection.Open(connStr);
connection.Execute('EXEC sp_mycheck');
}
catch(e)
{
WScript.StdErr.WriteLine("Error occurred " + e);
cnt = connection.Errors.Count;
for(i=0;i<cnt;i++)
{
  WScript.StdErr.WriteLine(connection.Errors(i));
}
WScript.StdOut.Close();
WScript.Quit(1);
}

Essentially the question is: What do I have to do to allow raiserror to work in this case Additionally, are there other cases besides the xp_fileexist function that cause the RAISERROR to not function

Thanks in advance.

Answer this question

RAISERROR() is invalidated by xp_fileexist function when run through ADO (example included)

lpx
Just had some insight into this when working on something else. If you have a RAISERROR() with a severity level less than 16 it will render subsequent level 16 RAISERROR() calls innocuous.

As an example, I did the example in the original post and took the stored procedure and modified it thus:

CREATE PROCEDURE sp_mycheck
AS
BEGIN
declare @ret int
RAISERROR ('A non-fatal error occurred',10,1)
RAISERROR ('A fatal error occurred',16,1)
RETURN (16)
END

This will cause the same behavior. However, reversing the two RAISERROR lines will cause an exception to occur in ADO and be logged in by the script -- and the second level 10 raiserror will not be executed.

Any ideas why
RAISERROR() is invalidated by xp_fileexist function when run through ADO (example included)

Answer this question