Error Message

[barmatt80]

I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).

In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.

IF @SQLCode <> 0 
BEGIN 
   SELECT description = @ErrMsg
   FROM master.dbo.sysmessages 
   WHERE error = @SQLCode 
END

Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.

Thanks

[ck9663]

I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).

In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.

IF @SQLCode <> 0 
BEGIN 
   SELECT description = @ErrMsg
   FROM master.dbo.sysmessages 
   WHERE error = @SQLCode 
END

Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.

Thanks

Try these functions

    
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

-- CK

[barmatt80]

Try these functions

    
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

-- CK

I have been wrapped up in meetings, hopefully I can test it this evening and I will report back then. I have another question. How would I execute this stored procedure to make sure that it is returning the error message and error code before I send it over to the application team. Thanks!

[ck9663]

I have been wrapped up in meetings, hopefully I can test it this evening and I will report back then. I have another question. How would I execute this stored procedure to make sure that it is returning the error message and error code before I send it over to the application team. Thanks!

That's not a stored proc. If you're using a sql2005, you're in luck. Read more here .

-- CK