Try to build a SQL error string, but

Question

Try to build a SQL error string, but

Digs

SSCertifiable

Points: 7333
More actions
December 13, 2010 at 6:56 pm

#229578

DECLARE @Desc VARCHAR(5000)
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SET @Desc = 'ERROR:'+ CAST(ERROR_NUMBER() AS VARCHAR(20))+' '+
CAST(ERROR_SEVERITY() AS VARCHAR(3))+' '+
CAST(ERROR_STATE() AS VARCHAR(3))+' '+
CAST(ERROR_PROCEDURE() AS VARCHAR(25))+' '+
CAST(ERROR_LINE()AS VARCHAR(10))+' '+
CAST(ERROR_MESSAGE() AS VARCHAR(210))
SELECT @Desc
END CATCH;
All I get is NULL, when I want a string showing error details, for my log entry into a VARCHAR(500) field...any ideas???:-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

ERROR_PROCEDURE() returns null since, in the contect you are using, there is no procedure that is relevant. You either need to remove this function from you SET statement or use ISNULL COALESCE

WayneS SSC Guru Points: 95460 More actions · Answer 2

Now to handle what HappyCat mentioned with your code:

DECLARE @Desc VARCHAR(5000)

BEGIN TRY

-- Generate a divide-by-zero error.

SELECT 1/0;

END TRY

BEGIN CATCH

SET @Desc = 'ERROR:'+ CAST(ERROR_NUMBER() AS VARCHAR(20))+' '+

CAST(ERROR_SEVERITY() AS VARCHAR(3))+' '+

CAST(ERROR_STATE() AS VARCHAR(3))+' '+

CAST(IsNull(ERROR_PROCEDURE(),'') AS VARCHAR(25))+' '+

CAST(ERROR_LINE()AS VARCHAR(10))+' '+

CAST(ERROR_MESSAGE() AS VARCHAR(210))

SELECT @Desc

END CATCH;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Digs SSCertifiable Points: 7333 More actions · Answer 3

Digs

SSCertifiable

Points: 7333

December 13, 2010 at 9:07 pm

#1262021

thanks 😀

Ken McKelvey SSCoach Points: 18855 More actions · Answer 4

You can often get away with one generic exception handler.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.ExceptionHandler

AS

SET NOCOUNT ON

DECLARE @ErrorNumber int = ERROR_NUMBER()

,@ErrorSeverity int = ERROR_SEVERITY()

,@ErrorState int = ERROR_STATE()

,@ErrorProcedure nvarchar(126) = ERROR_PROCEDURE()

,@ErrorLine int = ERROR_LINE()

,@ErrorMessage nvarchar(2048) = ERROR_MESSAGE()

IF @@TRANCOUNT > 0

ROLLBACK

---- Email alert

--DECLARE @mailbody nvarchar(4000) =

--'Error ' + CAST(@ErrorNumber AS varchar(20)) + ' occurred in procedure '

--+ COALESCE(@ErrorProcedure, 'NA') + ' at line ' + CAST(@ErrorLine AS varchar(20))

--+ ' with a severity of ' + CAST(@ErrorSeverity AS varchar(20))

--+ ' and a state of ' + CAST(@ErrorState AS varchar(20))

--+ '.' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

--+ @ErrorMessage

--EXEC msdb.dbo.sp_send_dbmail

--@profile_name = 'YourProfile'

--,@recipients = 'You@You.com;'

--,@importance = 'High'

--,@subject = 'YourApp Exception'

--,@body = @mailbody

-- write to log

--CREATE TABLE dbo.Exceptions

--(

--ExceptionId int IDENTITY NOT NULL

--CONSTRAINT PK_Exceptions PRIMARY KEY CLUSTERED

--,ExceptionDate datetime NOT NULL

--,ErrorNumber int NOT NULL

--,ErrorSeverity int NOT NULL

--,ErrorState int NOT NULL

--,ErrorProcedure nvarchar(126) NOT NULL

--,ErrorLine int NOT NULL

--,ErrorMessage nvarchar(2048) NOT NULL

--)

INSERT INTO dbo.Exceptions

(

ExceptionDate, ErrorNumber, ErrorSeverity, ErrorState

,ErrorProcedure, ErrorLine, ErrorMessage

)

SELECT CURRENT_TIMESTAMP, @ErrorNumber, @ErrorSeverity, @ErrorState

,COALESCE(@ErrorProcedure, N'NA'), @ErrorLine, COALESCE(@ErrorMessage, N'No Message')

RAISERROR

(

N'Error %d occurred in procedure %s at line %d. %s'

,@ErrorSeverity

,@ErrorState

,@ErrorNumber

,@ErrorProcedure

,@ErrorLine

,@ErrorMessage

)