Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Command Code 224

Status
Not open for further replies.

ice78991

Programmer
Nov 20, 2006
216

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

Code:
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?
 
I get a "not a recognized function" from ERROR_SEVERITY(). That's not a T-SQL function.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK
-----------
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.
 
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
 
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
 
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
 
Thanks for that, Bill; I had the wrong BOL and IDE (and it's obvious I haven't worked with the function yet %-) )
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top