SQL Guru's

[Gary.Lowe]

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

The problem is this. If I run this 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

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

[Gary.Lowe]

Guess not then

[Ecniv]

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

[Gary.Lowe]

Ahh Haaa

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

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