One of the guys I am working with at the moment uses the phrase “every day is a school day”. He’s not wrong, rarely does a day go past when I don’t learn something new about some bit of computing technology (typically finding out a new way that some piece of software is broken).

Today’s lesson was on Microsoft SQL Server. I was struggling to debug a problem inside a stored procedure that is buried deep with a nested set of other stored procedures. I was getting frustrated and thought to myself “it would be so nice if I could dump stuff to the Windows Event Log from within this stored procedure”.

I got so fed up in the end that I went off to see whether event logging from Transact-SQL was actually possible. It is, using the xp_logevent stored procedure (which lives in the master database).

This is the code that I am using:

declare @message varchar(255)
set @message = '@primary_contact_type ' + cast(@primary_contact_type as varchar(30))
EXEC master..xp_logevent 60000, @message

Two things to know to make this work:

  • you have to declare the @message variable, you can’t just pass a string constant to the xp_logevent call
  • your database user needs the relevant permissions on the master database to make the call

The xp_logevent stored procedure makes a COM call to log the message, so you probably wouldn’t want to leave the call in any production code (unless you put in a switch to turn it on and off when you needed).