Fri 1 Dec 2006
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
@messagevariable, you can’t just pass a string constant to the
- your database user needs the relevant permissions on the master database to make the call
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).