May 29, 2014 at 2:02 am
Hey all,
I have a try catch block. In the catch i have this (along with other stuff!):
DECLARE
@ErrorMessage NVARCHAR(4000) ,
@ErrorMessageShort NVARCHAR(4000) ,
@ErrorNumber INT ,
@ErrorSeverity INT ,
@ErrorState INT ,
@ErrorLine INT ,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE() ,
@ErrorLine = ERROR_LINE() ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ,
@ErrorMessageShort = ERROR_MESSAGE();
INSERT dbo.Audit_Errors
( ErrorTime ,
JobID ,
JobStepID ,
StepDescription ,
ErrorNumber ,
ErrorSeverity ,
ErrorState ,
ErrorProcedure ,
ErrorLine ,
ErrorMessage
)
SELECT
GETDATE() AS ErrorTime ,
@JobID AS JobID ,
@JobStepID AS JobStepID ,
@StepDescription AS Process_Step ,
@ErrorNumber AS ErrorNumber ,
@ErrorSeverity AS ErrorSeverity ,
@ErrorState AS ErrorState ,
@ErrorProcedure AS ErrorProcedure ,
@ErrorLine AS ErrorLine ,
@ErrorMessageShort AS ErrorMessage
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine) WITH LOG
Now in the table the ErrorProcedure, State, line and message are all populated correctly. However the raise error is coming back with stuff like:
Msg 50000, Level 14, State 1, Line 302
Error 2627, Level 93, State (null), Procedure (null), Line (null), Message: Violation of PRIMARY KEY constraint.....
So no proc, and two sets of level etc.
My audit table is logging (for this error) Errornum 2627, Severity 14, state 1 and the correct name of the error proc.
What am i doing wrong to make the raiserror do this?
Many thanks
Dan
May 29, 2014 at 4:08 am
Please check the table definition may by primary key on below columns.
State (null), Procedure (null), Line (null)
i tested it the with out primary key and it is working fine.The results looks like this.
[p]DECLARE
@ErrorMessage NVARCHAR(4000) ,
@ErrorMessageShort NVARCHAR(4000) ,
@ErrorNumber INT ,
@ErrorSeverity INT ,
@ErrorState INT ,
@ErrorLine INT ,
@ErrorProcedure NVARCHAR(200),
@JobID INT = 100,
@JobStepID INT = 10,
@StepDescription VARCHAR(100) = 'NEW JOB';
SELECT
@ErrorNumber = ERROR_NUMBER() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE() ,
@ErrorLine = ERROR_LINE() ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ,
@ErrorMessageShort = ERROR_MESSAGE();
INSERT dbo.Audit_Errors
( ErrorTime ,
JobID ,
JobStepID ,
StepDescription ,
ErrorNumber ,
ErrorSeverity ,
ErrorState ,
ErrorProcedure ,
ErrorLine ,
ErrorMessage
)
SELECT
GETDATE() AS ErrorTime ,
@JobID AS JobID ,
@JobStepID AS JobStepID ,
@StepDescription AS Process_Step ,
@ErrorNumber AS ErrorNumber ,
@ErrorSeverity AS ErrorSeverity ,
@ErrorState AS ErrorState ,
@ErrorProcedure AS ErrorProcedure ,
@ErrorLine AS ErrorLine ,
@ErrorMessageShort AS ErrorMessage
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine) WITH LOG[/p]
ErrorTimeJobIDJobStepIDStepDescriptionErrorNumberErrorSeverityErrorStateErrorProcedureErrorLineErrorMessage
2014-05-29 05:59:43.67010010NEW JOBNULLNULLNULL-NULLNULL
2014-05-29 05:59:51.77310010NEW JOBNULLNULLNULL-NULLNULL
2014-05-29 06:00:07.92010010NEW JOBNULLNULLNULL-NULLNULL
2014-05-29 06:00:09.89010010NEW JOBNULLNULLNULL-NULLNULL
May 29, 2014 at 4:53 am
Hi,
Sorry i am a little confused. What primary key? The table insert is fine.... its the raiserror that is showing nulls.
Sorry if i was unclear.
Dan
May 29, 2014 at 5:23 am
can u paste the table structure of Audit_Errors.
Then i will show u the issue.
May 29, 2014 at 7:05 am
CREATE TABLE [dbo].[Audit_Errors](
[AuditLogErrorsID] [int] IDENTITY(1,1) NOT NULL,
[ErrorTime] [datetime] NULL,
[JobID] [int] NULL,
[JobStepID] [int] NULL,
[StepDescription] [varchar](250) NULL,
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](200) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) NULL,
CONSTRAINT [PK_Audit_Errors] PRIMARY KEY CLUSTERED
(
[AuditLogErrorsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
May 29, 2014 at 7:06 am
So just to clarify - the insert into the audit table is perfect. It has the correct information. The raiserror has the nulls.
May 29, 2014 at 9:43 am
@ErrorMessage doesn't get populated in the code shown. Can you show how that gets built?
May 30, 2014 at 1:55 pm
danielfountain (5/29/2014)
Hey all,I have a try catch block.
<snipped>
What am i doing wrong to make the raiserror do this?
Many thanks
Dan
Unfortunately, RAISERROR in a CATCH block doesn't work like you want it to work (as I understand your scenario):
1. An error occurs in a TRY block and passes control to the CATCH block.
2. Some processing occurs in the CATCH block.
3. A RAISERROR statement raises an error message to the calling application that reflects the original error and processing halts.
From Books Online:
[RAISERROR] [g]enerates an error message and initiates error processing for the session. . . . The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.
That's how RAISERROR works in a TRY block (or in code without a TRY...CATCH construct) - it just forces an error situation.
Once you're in the CATCH block, though, RAISERROR behaves differently depending on how you use it.
Here's the basic code that is used to demonstrate three scenarios for using RAISERROR in a CATCH block:
BEGIN TRY
SELECT 1/0 AS col1
END TRY
BEGIN CATCH
DxECLARE @ErrorMessageLong NVARCHAR(4000) = N'Msg% 0.0d, Level% 0.0d, State% 0.0d, Line% 0.0d
% 1.255s'
,@ErrorMessageShort NVARCHAR(4000) = ERROR_MESSAGE()
,@ErrorSeverity INT = ERROR_SEVERITY()
,@ErrorState INT = ERROR_STATE()
,@ErrorNumber INT = ERROR_NUMBER()
,@ErrorLine INT = ERROR_LINE()
-- RAISERROR statements will go here --
END CATCH
The string value assigned to the @ErrorMessageLong variable includes a bunch of codes that instruct the RAISERROR statement to substitute the arguments that begin after the [state] argument for the "%" signs, in the order they're listed. The characters after the "%" sign provide formatting instructions. For example, the code "% 0.0d" means substitute the appropriate argument from the list in this position("%"), pad it with spaces if necessary (" "), print the value with no padding if it is longer than this width ("0"), print all digits of the value (".0"), and format it as a signed integer ("d"). It usually takes me some trial and error to get the string just right to produce the output I expect.
Here are the three different ways you can use RAISERROR in the CATCH block.
Scenario 1 - Raise an error with Msg 50000, the severity level and state of the original error, the line number of the RAISERROR statement, and the description only of the original error message and continue processing. Using this RAISERROR statement:
RAISERROR(@ErrorMessageShort, @ErrorSeverity, @ErrorState)
with the code above will return this error message:
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.
and processing will continue (add some code after the RAISERROR statement in the CATCH block to see this is true).
Scenario 2 - Raise an error with Msg 50000, a specified severity level and state, the line number of the RAISERROR statement, and the full text of the original error message and stop processing if warranted by the specified severity level. Using this RAISERROR statement:
RAISERROR(@ErrorMessageLong, 17, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorMessageShort)
raises this error:
Msg 50000, Level 17, State 1, Line 21
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
Processing will stop if the specified severity level is greater than 16 (only sysadmins can specify a severity level higher than 18 and only when using the WITH LOG option).
Scenario 3:
Print a message that looks like the original error message and continue processing. Using this RAISERROR statement:
RAISERROR(@ErrorMessageLong, -1, -1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorMessageShort)
returns this message (it's not a true error message, just a message like the output of a PRINT statement):
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
and processing will continue.
That's supremely frustrating, but unfortunately, there doesn't seem to be a good workaround to achieve the desired result in SQL Server 2008R2 and earlier versions.
Starting with SQL Server 2012, the THROW statement provides this functionality. When executed in a CATCH block, THROW returns the error that caused control to drop to the CATCH block and halts processing if warranted by the severity level. On a SQL Server 2012 instance, executing the THROW statement in place of the RAISERROR statement in the code above, like this (it requires that the previous statement be terminated by a semicolon):
;
THROW
raises this error message:
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
and processing halts. It's a shame that something this simple isn't available in earlier versions!
Jason Wolfkill
May 30, 2014 at 2:40 pm
Instead of throw, you could use a simple condition.;-)
DECLARE @ErrorMessage NVARCHAR(4000) ,
@ErrorMessageShort NVARCHAR(4000) ,
@ErrorNumber INT ,
@ErrorSeverity INT ,
@ErrorState INT ,
@ErrorLine INT ,
@ErrorProcedure NVARCHAR(200);
BEGIN TRY
SELECT 1/0 AS col1
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE() ,
@ErrorLine = ERROR_LINE() ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ,
@ErrorMessage = ERROR_MESSAGE()
INSERT dbo.Audit_Errors
(
ErrorTime ,
JobID ,
JobStepID ,
StepDescription ,
ErrorNumber ,
ErrorSeverity ,
ErrorState ,
ErrorProcedure ,
ErrorLine ,
ErrorMessage
)
SELECT
GETDATE() AS ErrorTime ,
@JobID AS JobID ,
@JobStepID AS JobStepID ,
@StepDescription AS Process_Step ,
@ErrorNumber AS ErrorNumber ,
@ErrorSeverity AS ErrorSeverity ,
@ErrorState AS ErrorState ,
@ErrorProcedure AS ErrorProcedure ,
@ErrorLine AS ErrorLine ,
@ErrorMessageShort AS ErrorMessage
END CATCH
IF @ErrorMessage > ''
BEGIN
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine) WITH LOG
SET @ErrorMessage = NULL
END
May 30, 2014 at 2:55 pm
Luis Cazares (5/30/2014)
Instead of throw, you could use a simple condition.;-)
DxECLARE @ErrorMessage NVARCHAR(4000) ,@ErrorMessageShort NVARCHAR(4000) ,
@ErrorNumber INT ,
@ErrorSeverity INT ,
@ErrorState INT ,
@ErrorLine INT ,
@ErrorProcedure NVARCHAR(200);
BEGIN TRY
SELECT 1/0 AS col1
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE() ,
@ErrorLine = ERROR_LINE() ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ,
@ErrorMessage = ERROR_MESSAGE()
INSERT dbo.Audit_Errors
(
ErrorTime ,
JobID ,
JobStepID ,
StepDescription ,
ErrorNumber ,
ErrorSeverity ,
ErrorState ,
ErrorProcedure ,
ErrorLine ,
ErrorMessage
)
SELECT
GETDATE() AS ErrorTime ,
@JobID AS JobID ,
@JobStepID AS JobStepID ,
@StepDescription AS Process_Step ,
@ErrorNumber AS ErrorNumber ,
@ErrorSeverity AS ErrorSeverity ,
@ErrorState AS ErrorState ,
@ErrorProcedure AS ErrorProcedure ,
@ErrorLine AS ErrorLine ,
@ErrorMessageShort AS ErrorMessage
END CATCH
IF @ErrorMessage > ''
BEGIN
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine) WITH LOG
SET @ErrorMessage = NULL
END
The problem is that RAISERROR used like that will return the error message but will NOT halt processing unless you ensure that the severity level is set to 17 or higher. Notice that the SELECT I added after the first RAISERROR executes and returns a result set while the SELECT after the second RAISERROR does not:
DxECLARE @ErrorMessage NVARCHAR(4000) ,
@ErrorMessageShort NVARCHAR(4000) ,
@ErrorNumber INT ,
@ErrorSeverity INT ,
@ErrorState INT ,
@ErrorLine INT ,
@ErrorProcedure NVARCHAR(200);
BEGIN TRY
SELECT 1/0 AS col1
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE() ,
@ErrorLine = ERROR_LINE() ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ,
@ErrorMessage = ERROR_MESSAGE()
END CATCH
IF @ErrorMessage > ''
BEGIN
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine) WITH LOG
SELECT 'xxxxxxx' AS junkColumn
RAISERROR (@ErrorMessage, 17, @ErrorState, @ErrorNumber,@ErrorLine) WITH LOG
SET @ErrorMessage = NULL
SELECT 'yyyyyyy' AS junkColumn
END
Jason Wolfkill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply