SQL Guru's

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 :confused:

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 :confused:

Any suggestions

Guess not then

The event log only stores up to the new line char (in the exec bit)..?

Any way of looping for each line and exporting to the event log ?

My only idea - dunno about Sql Server (yet)

Regards

Vince

Ahh Haaa

You have to add the char's at the end of the last text

Code:

---

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

Print @CurrentMessage

EXEC master..xp_logevent 12345678, @CurrentMessage, ERROR

---

It then enters all the lines