XP_LogEvent and RaiseError


xp_logevent
xp_logevent extended stored procedure is used to log the message into the SQL Server error log and event viewer application log.

Syntax
xp_logevent { error_number , ‘message’ } [ , ‘severity’ ]
Parameter Description
Error number Error number should be greater than 50000
Message Text you want to record in the error log and event viewer log
Severity Optional parameter (warning, informational or error) used to determine the type of error that will be logged in the NT application event log.

Than the question will arise in your mind what is the use of RAISERROR,  here is the difference between XP_LOGEVENT and RAISEERROR

XP_LOGEVENT RAISEERROR
Logs a user-defined message in the SQL Server log file and in the Windows Event Viewer. xp_logevent can be used to send an alert without sending a message to the client. Generates an error message. RAISERROR can either reference a user-defined message stored in the sys.messages view or build a message dynamically
Syntax
xp_logevent { error_number , ‘message’ } [ , ‘severity’ ]
Syntax
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,…n ] ] )
[ WITH option [ ,…n ] ]
Does not return the error to the client application Does return the error to the client application
Does not change the value of @@error Does change the value of @@error
Can only be used to log the messages in the SQL Server error log and event viewer log Can use for error handling and RaiseError with log can perform the similar functionality to XP_LOGEVENT
Permission
Requires membership in the db_owner fixed database role in the master database, or membership in the sysadmin fixed server role.
Permission
Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions

Example

EXEC master..xp_logevent 50003, ‘Stored Procedure executed successfully’
Exec xp_readerrorlog –check the error message in error log or event viewer log

Advertisements

One thought on “XP_LogEvent and RaiseError

  1. Matt Tey

    Doing this: EXEC master..xp_logevent 50003, ‘Stored Procedure executed successfully’
    On event viewer we get this: Error: 50003 Severity: 10 State: 1 Stored Procedure executed successfully

    Can we not showing ‘Error: 50003’ as part of the message, as this is not intended to be logged as an error?

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s