Logging information into Windows Event Viewer

Have you ever wondered whether you could log information into Windows Event Log? If yes, read on :)

As you know, there are several methods to log information about your applications. If at all you want to create a specialized logging mechanism, consider using xp_logevent.

xp_logevent is a SQL Server extended stored procedure that is used to log customized events to the Windows NT system log. This information can then be examined through the NT Event Viewer.

Usage:

xp_logevent error_number, error_message, [severity]

here,

error_number -- can be any number greater than 50,000 and less than or equal to 1073741823

error_message -- can be any user defined message with 255 characters at the maximum. If the error message is > 255 characters it won't throw an error, instead only the first 255 characters would be displayed in the Event Viewer.

Severity -- can hold, one of the following 3 values: ERROR, WARNING & INFORMATION. Please note that severity is an optional value and the default value for it is INFORMATION

Code Snippet:

Below is a sample logging procedure that sends a dummy / test error message to Windows Event Viewer.

Create Procedure LogToEventViewer
As

Declare @Message varchar(1000)
Select @Message = 'Sample message: From SQL Server'

Exec master..xp_logevent 50101, @Message, WARNING

FYI, I have tried xp_logevent in SQL 6.5 and above.

Tip:

You might also want to check http://support.microsoft.com/default.aspx?scid=kb;EN-US;290085

Verification:

After executing the above stored procedure open the Event Viewer. In the Windows 2000 systems it would be here, Start >> Settings >> Control Panel >> Administrative Tools >> Event Viewer. Inside Event Viewer we could see the warning message which were created using our stored procedure inside "Application Log" tab.