opc.three (6/29/2013)

---

If the goal is only to see the data in the variable for purposes of debugging then here is an alternate method which I find works quite well. I prefer it over the tally table method only because it requires less thought to recall from memory (I'm lazy too). An added benefit is that it let's me see the uninterrupted text with all formatting left intact:

-- test code from article goes here...

SELECT @LongString AS [processing-instruction(LongString)]

FOR XML PATH(''), TYPE;

SELECT @NLongString AS [processing-instruction(NLongString)]

FOR XML PATH(''), TYPE;

Credit: I picked up this technique from reading the source code for Adam Machanic's sp_WhoIsActive. He employs this technique to deliver the sql-text for a given session which can exceed 8000 bytes.

Edit: add , TYPE to FOR XML clause.