WRITING SQL ERRORS TO THE EVENT LOG v7.0
In SQL Server, there are several ways to log information about your 
applications. However, if you need to create your own specialized 
logging mechanism, consider using xp_logevent. Xp_logevent is a SQL 
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.

The syntax for xp_logevent

xp_logevent {error_number, 'message'} [, 'severity'] 
error_number is > 50,000 and <= 1073741823 
message is the user defined message that is < 8000 characters 
severity is optional level of severity of ERROR, WARNING and 
INFORMATIONAL (default)

Below is a sample logging procedure that captures failed attempts to 
delete data from within the application.

CREATE PROCEDURE pLogFailedDelete @TableName varchar(100) 
AS 
DECLARE @Message varchar(1000), 
    @User varchar(20)  
SET @User = CURRENT_USER 
 
SELECT @Message = 'Failed Attempt to delete data from table ' + 
@TableName + ' by ' + @User 
 
EXEC xp_logevent 50101, @Message, WARNING