Command Code 224

[ice78991]

I am trying to detect if a user name already exists in a table in a stored procedure. If it does, I want to throw an error message back to my coldfusion page. My coldfusion page calls this stored procedure within a try catch block

However,my coldfusion catch block is receiving the following error from SQL Server 2005


[Macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224.

I think the problem is somewhere in the following code which is designed to exit the stored procedure if a User Name already exists

IF EXISTS (SELECT UserName FROM Users WHERE UserName = @UserName)

BEGIN
			
SELECT @ErrMsg = 'UserName already exists',@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
RETURN
		
END

Can anyone see a problem here?

 

[philhege]

I get a "not a recognized function" from ERROR_SEVERITY(). That's not a T-SQL function.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[PatriciaObreja]

I think that ERROR_SEVERITY() in new in SQL Server 2005.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[ice78991]

Could the problem be that I am using ERROR_SEVERITY() when there is no actual error, I'm just using RAISERROR to switch back to my Coldfusion catch block

 

[SQLBill]

I think you are using ERROR_SEVERITY wrong. Per the example in the BOL for SS2005.

USE AdventureWorks;
GO

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
GO

BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
GO

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

Thanks for that, Bill; I had the wrong BOL and IDE (and it's obvious I haven't worked with the function yet %-) )