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.
xp_logevent error_number, error_message, [severity]
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
Below is a sample logging procedure that sends a dummy / test error message to Windows Event Viewer.
Create Procedure LogToEventViewer
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.
You might also want to check http://support.microsoft.com/default.aspx?scid=kb;EN-US;290085
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.