RAISERROR() is invalidated by xp_fileexist function when run through ADO (example included)
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.

