I am writing a stored procedure in SQL thats fires erros to the event log.

The problem is this. If I run this code

Code:
DECLARE @CurrentMessage varchar(8000)

SET @CurrentMessage = 'Testing wether this works'

Print @CurrentMessage

EXEC master..xp_logevent 12345678, @CurrentMessage, ERROR
You can try this yourself.

Now the problem is because i am running the SQL in a transaction, when I hit an error it rollsback (understandable)

BUT

It also rollsback the event log entry

To get round this I have entered the errors in a string and hold it until I rollback

I then write it to the event log

Now Then:

I use this to write the errors to a string

Code:
SET @CurrentMessage = 'Testing wether this works'
SET @CurrentMessage = @CurrentMessage + CHAR(13) + CHAR(10) + 'on another line '

Print @CurrentMessage

EXEC master..xp_logevent 12345678, @CurrentMessage, ERROR
If you run this in Query Analyzer it displays the message on 2 lines.

If you look in the event log though it only copies the first line

Any suggestions