Blog Detail

27 Apr 2009
Tejas Shah
Today I came across the situation where I need to following scenario. 

I need to make entry in Event Viewer when there is an error in Stored Procedure. 
I wondered to do this, but finally I come up with the solution. SQL is much powerful. 
SQL provides us to make an entry in Event Viewer by two ways: 
1. using XP_LogEvent
2. By Raiserror WITH LOG
Lets first see the way using XP_LogEvent: 
Here I have created one SP which will raise an error “Divide by zero error encountered.” as I tried to do “10 / 0”.
CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY 
   SELECT  10/0
END TRY
BEGIN CATCH 
   PRINT ERROR_Message() 
   DECLARE @msg VARCHAR(100) 
   SELECT @msg = ERROR_Message()
    EXEC xp_logevent 60000, @msg, informational
END CATCH
Lets Execute this SP:
EXEC TestEventViewer
This will write entry in Event Viewer. Now open Event Viewer. You can find Event Viewer at Control Panel –> Administrative Tools –> Event Viewer.
You will get en entry of Error there.

 

So we can do this by Extended Stored Procedure: “xp_logevent”.
Let see the parameters of this Procedure.
First Parameter: “60000” is the Error Number
Second Parameter: “@msg” is the message to be displayed in Event Viewer.
Third Parameter: “informational” is the Error Level. It could be “informational”, “Error”, “Warning”.
Now, Lets see by another way By Raiserror WITH LOG:
It is the same way as we used Raiserror to Raise an Error.

CREATE PROCEDURE TestEventViewer 
AS 
BEGIN TRY 
    SELECT  10/0 
END TRY 
BEGIN CATCH 
    PRINT ERROR_Message() 
    DECLARE @msg VARCHAR(100) 
    SELECT @msg = ERROR_Message()

    RAISERROR(@msg, 11, 1) WITH LOG

END CATCH

Lets Execute this SP:
EXEC TestEventViewer.
So By these ways we can make an entry to Event Viewer.
Let me know if it helps you in any way

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.