11

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?

RoboShop
  • 3,576

2 Answers2

8

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 https://stackoverflow.com/questions/2759721/how-do-i-view-the-full-content-of-a-text-or-varcharmax-column-in-sql-server-200

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

Community
  • 1
Conrad Frix
  • 423
  • thank you, this is perfect for my need. I had a look at the log file, it was about 3MB, so I have a feeling SQL Server cuts off anything after one meg. – RoboShop Jun 11 '11 at 01:27
1
  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.

Matt Ball
  • 3,812
  • 1
    it could've been but i tried it in Word and WordPad also, and it was still continuous lines. The other solution worked though, both for getting the data out and getting the NewLines to appear. – RoboShop Jun 11 '11 at 01:25
  • Copying and pasting doesn't work for me either. I paste into a new SSMS query window, Emacs, or WordPad, and each CRLF is replaced with two spaces. If I use the XML hack, I can see the newlines but my text is polluted by entities. At least I can get the gist, though. I'm using SSMS 2016 (13.0.15900.1) and table view for my results listings. – binki Oct 28 '16 at 15:29