Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

Display More Than 8000 Characters (SQL Spackle) Expand / Collapse
Author
Message
Posted Saturday, June 29, 2013 12:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 969, Visits: 3,006
steve.ledridge (6/28/2013)
Here is a little function I wrote to deal with this very problem.

RBAR




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1468773
Posted Saturday, June 29, 2013 3:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1468782
Posted Saturday, June 29, 2013 3:03 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:55 PM
Points: 369, Visits: 1,964
rac.coons (6/28/2013)
Here's a very quick and easy way to display all characters that I did not see here.

select convert(xml,'<txt>' + @txt + '</txt>')

In SSMS, it returns a link that opens a new tab with all of the text.


I was going to suggest nearly the same thing . The only thing I would change is that adding in the XML tags for the root node is not necessary.

I would think that the easiest way to see up to 2 MB of text that also happens to work just as well in a query as it does for printing a single variable is:

SELECT CONVERT(XML, @Variable)

OR

SELECT Field1, Field2, CONVERT(XML, Field3) AS [Field3]
FROM Schema.Table

This is a little more flexible, and straight-forward, than using FOR XML as that might not be desirable when debugging a query.

Take care,
Solomon...

Edit:

Correction on the 2 MB limit. I thought it was the max but I just checked again and it isn't. If you go to:

Tools -> Options... -> Query Results -> SQL Server -> Results to Grid

you will see at the bottom of the right side, in the "Maximum Characters Retrieved" section, a drop-down for "XML data:" that has the following options:

1 MB
2 MB (default)
5 MB
Unlimited





SQL# - http://www.SQLsharp.com/
Post #1468821
Posted Saturday, June 29, 2013 11:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
Solomon Rutzky (6/29/2013)
This is a little more flexible, and straight-forward, than using FOR XML as that might not be desirable when debugging a query.

As with most solutions to a given problem there are trade offs. If any of these characters appear in the text you're trying to view you'll have a harder time using the XML data type:

SELECT  CAST('<' AS XML)
GO
SELECT CAST('&' AS XML)
GO
SELECT CAST('<' AS XML)
GO

I've found a case where the processing-instruction technique also falls down (although I have yet to isolate the actual technical reason) but it seems a bit more durable than the solution using XML type.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1468840
Posted Sunday, June 30, 2013 3:10 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:55 PM
Points: 369, Visits: 1,964
opc.three (6/29/2013)
Solomon Rutzky (6/29/2013)
This is a little more flexible, and straight-forward, than using FOR XML as that might not be desirable when debugging a query.

As with most solutions to a given problem there are trade offs. If any of these characters appear in the text you're trying to view you'll have a harder time using the XML data type:

SELECT  CAST('<' AS XML)
GO
SELECT CAST('&' AS XML)
GO
SELECT CAST('<' AS XML)
GO



Thanks for pointing that out. I had completely forgotten about encoding certain characters. I can find only 2 that truly need to be converted. You listed 3 but the < was in there twice and while you might have meant >, that character is auto-converted. So the following adaptation of the overly-simplistic CONVERT should work in most cases:

SELECT CONVERT(XML, REPLACE(REPLACE(N'test &lt; & &gt; me', N'&', N'&amp;amp;'), N'&lt;', N'&amp;lt;'))

I say "most cases" as there is a subset of the UCS-2 character set that is not as easily convertible, at least not without a lot of additional REPLACE() functions that realistically won't be used that much. Fortunately the white-space control characters (13 = Carriage Return, 10 = Line Feed, and 9 = Tab) come through just fine.

To make the updated CONVERT more usable / less cumbersome, it can be encapsulated in a UDF as follows:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
IF (OBJECT_ID('dbo.ShowEntireString') IS NOT NULL)
BEGIN
DROP FUNCTION dbo.ShowEntireString
END
GO

CREATE FUNCTION dbo.ShowEntireString (@String NVARCHAR(MAX))
RETURNS XML
WITH SCHEMABINDING
AS
BEGIN
RETURN CONVERT(XML, REPLACE(REPLACE(@String, N'&', N'&amp;'), N'<', N'&lt;'))
END
GO

So first a simple test:

SELECT dbo.ShowEntireString(N'test < string for & invalid > chars' + NCHAR(13) + NCHAR(10) + N'new line?' + NCHAR(9) + 'and tabbed?')


And now the full test, showing both a) nearly all of the UCS-2 character set represented [only missing 2078 of the 65,535 characters], and b) a sizable string fully represented [in Grid mode].

DECLARE @String NVARCHAR(MAX)
SET @String = N'A' + NCHAR(13) + N'B' + NCHAR(10) + N'C' + NCHAR(9) + N'D'

;WITH cte AS
(
SELECT TOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [TheNumber]
FROM master.sys.all_columns sc1
CROSS JOIN master.sys.all_columns sc2
)
SELECT @String = @String + NCHAR(cte.TheNumber)
FROM cte
WHERE cte.TheNumber BETWEEN 32 AND 65533 -- 0 through 31 error (well, except 9, 10, and 13)
AND cte.TheNumber NOT BETWEEN 55296 AND 57343 -- invalid in XML

SELECT LEN(@String), DATALENGTH(@String)
-- 63461 and 126922

SET @String = REPLICATE(@String, 20)

SELECT LEN(@String), DATALENGTH(@String)
-- 1269220 and 2538440

SELECT dbo.ShowEntireString(@String)


Can you now find a case where this method does not work? Including the case where the other method does not work? If so, please let me know how to duplicate the test. Thanks.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1468892
Posted Sunday, June 30, 2013 4:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
I only meant to show the two characters that would be escaped when casting to the XML data type.

Thanks for the code samples Solomon. I ran a few tests and the one difference between the XML data type and the FOR XML techniques that I saw as important to note is that the XML data type will not show the actual text in all cases, i.e. it escapes the left-angle bracket and ampersand characters as < and & respectively, which could prove to disqualify its usage some scenarios:


As a side note, I did find the problem with the particular text that was tripping up the FOR XML method I showed above (code since amended)...I copied an incomplete code sample from sp_WhoIsActive and this is a lesson to me. I have added the code to my SQL Prompt snippets so I do not miss the TYPE again. In neglecting to provide TYPE in the FOR XML clause I left out the secret sauce that handles the escapable characters and delivers them as they exist in the text, as well as prevents them from causing XML parsing errors.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato


  Post Attachments 
51d058ba.jpg (170 views, 22.41 KB)
Post #1468897
Posted Sunday, June 30, 2013 9:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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.


Oh, now THAT's bloody clever and super easy to remember. It's soooooooo simple! Works in 2k5, as well. Just click on the resulting XML in the grid and a window opens up with the whole shootin' match right there. I just tested it on a 26KB NVARCHAR (13K characters) and it works great.

For anyone else that reads this, the AS [processing-instruction(xxx)] is super important for de-entitization. The "xxx" can be anyword but I use "SQL" there for consistancy.

Outstanding tip, Orlando. Thanks for posting it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1468916
Posted Sunday, June 30, 2013 9:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
rac.coons (6/28/2013)
Here's a very quick and easy way to display all characters that I did not see here.

select convert(xml,'<txt>' + @txt + '</txt>')

In SSMS, it returns a link that opens a new tab with all of the text.


You posted that before the others and I have to give credit where credit is due. Add the TYPE clause to de-entitize special characters and the special "AS" and it's a beautiful thing. Well done. Thanks for stopping by and thank you VERY much for the tip.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1468917
Posted Monday, July 1, 2013 9:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:56 AM
Points: 15, Visits: 145
The was (re)posted on SQLServerCentral and delivered to my inbox on the very day that I needed to examine why a 8k+ VARCHAR(MAX) dynamic SQL string was not executing the last few commands in the string. I had problems making Jeff's code work correctly using 8000 characters. When I reduced the 8000 to 1000, the code worked perfectly.

Turns out that my copy of SSMS was set to display a maximum of 1024 characters in each column on "Results to Text" output. I know I've never changed it, and no-one else has access to this box. Anyway, changing the value to 8192 (the maximum that SSMS 2008R2 allows) STILL didn't work, until I closed the tab containing the test script and reopened it. At that point, Jeff's original 8k value worked.

Jeff, perhaps the original posting should have a postscript added that discusses this situation. Anyway, thanks for sharing your insight with us huddled masses! <g>
Post #1469131
Posted Monday, July 1, 2013 10:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:06 AM
Points: 262, Visits: 128
The number of output characters displayed in the SSMS results window (grid or text) is configurable (see attached image).

Max is 64K for row data, and unlimited for XML. If you have a field with more than 64K, converting the output to XML seems to be a good option as well.

~Rusty





  Post Attachments 
SSMS-Options.jpg (4 views, 84.50 KB)
Post #1469157
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse