February 26, 2003 at 7:29 am
Hi
Does anybody have a solution for this? I want to retrieve the ErrorDescription from an SQL statement:
example
Server: Msg 107, Level 16, State 2, Line 63
The column prefix 'TaskLogRecord.Err' does not match with a table name or alias name used in the query.
So I want this: "The column prefix 'TaskLogRecord.Err' does not match with a table name or alias name used in the query."
Its possible to retrieve the error number with this:
DECLARE @Result INTEGER
EXEC @Result = <sp-call>
IF @Result<>0
BEGIN
<Do somthing>
END
Problably somthing can be done with @@ERRIR but still no error-message!
I know its possible within VB but I dont know how to do this in SQL.
Thanks for the help Jay and Tim.
OK my problem now is that I call an SP within SQL. In the SP I raise some errors like:
<<RAISERROR('Invalid ...: "%s"', 16, 1, @Myvar)>> And I want to log this (description) to a table within my SQL
example:
EXEC @Return=DB.dbo.MYTABLE <statements>
IF @Return<>0
BEGIN
DECLARE @ins_error INT, @err_desc NVARCHAR(500)
-- INSERT STATEMENT
-- SELECT @ins_error = @@ERROR
SELECT @ins_error = @Return
IF @ins_error <> 0
BEGIN
SELECT @ErrorMessage=description
FROM master.dbo.sysmessages
WHERE error = @ins_error
END
INSERT INTO DB.dbo.Log (Error_Message)
VALUES (@ErrorMessage)
END
So if anybody has an idea its welcome
Regards
Edited by - ghysens on 02/27/2003 12:42:46 AM
Filip
February 26, 2003 at 9:38 am
Best advice is to handle errors in client program, however, if you really want to know the description within the stored procedure, you can do something like:
DECLARE @ins_error INT, @err_desc NVARCHAR(500)
-- INSERT STATEMENT
SELECT @ins_error = @@ERROR
IF @ins_error <> 0 BEGIN
SELECT description
FROM master.dbo.sysmessages
WHERE error = @ins_error
RAISERROR('ERROR: %s', 16, 1, @err_desc) WITH LOG
-- The WITH LOG will write to the application event log...
END
Hope this gets you started...
Jay
February 26, 2003 at 4:25 pm
You can use FORMATMESSAGE, however :
When trying to call RAISERROR with 107 you get :
RAISERROR (107, 16, 1)
Server: Msg 2732, Level 16, State 1, Line 1
Error number 107 is invalid. The number must be from 13000 through 2147483647
When calling FORMATMESSAGE with 107 it returns null. I could find no documentation that stated FORMATMESSAGE worked only with 13000 or greater, yet when I did run it with numbers higher than that it worked fine.
SELECT FORMATMESSAGE(107, N'TaskLogRecord.Err') --returns null
SELECT FORMATMESSAGE(14027, 'TaskLogRecord.Err') --returns "TaskLogRecord.Err does not exist in the current database."
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply