preserving \n from SQL Server Management Studio

Question

I have a column called EventLog varchar(max) and can be pretty big because essentially it stores the event log of an entire batch process.

The problem is that when I go into the query window and do a select like this

SELECT EventLog from BatchProcess

When I cut and paste the text of the event log into Notepad, it

1. removes all of the NewLines \n and is just one continous line.
2. only copies a certain amount of text into the clipboard and truncates the rest.

How can I get the content of EventLog from the SQL Server Management Studio?

Answer 1

To deal with the truncation try converting it to XML for processing

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + EventLog  FROM BatchProcess

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')

Code modifed from Martin's Smith's answer to How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?

This may also solve the line breaks, but if its a big deal try a different editor

Answer 2

1. removes all of the NewLines and is just one continous line.

That sounds like the typical crappiness of Notepad. IIRC, in order to actually display line breaks, Notepad needs \r\n line endings, not just \n. Try pasting into WordPad or a real editor.