Display More Than 8000 Characters (SQL Spackle)
-
JJ B (2/3/2011)
JJ B. If you're not currently using a loop or the method in the article, what are you using?
I'll tell ya, but you have to be kind (not that you wouldn't be). I put this together several years ago. I didn't have access to rank functions at the time, and I made this up myself. Also, this is run once a night on very few records (less than 50). So, stressing about being terribly efficient didn't make sense.
It is a multi-step stored proc. The proc does use what you call a tally table, but not in the same, most likely more efficient way, as you did. Below are comments copied from my code. If they don't make sense and you are still interested, I could attach the proc - which has more comments and actual SQL. 🙂
No need for the code, JJ B. It's nice to see someone comment correctly! I always tell the folks I work with, "You can tell if you've commented properly by removing all of the code. If you could rebuild the code from the comments, then you've commented correctly." Well done.
It's ironic... your Step C) is all that needed to be done and it would be the same as this "SQL Spackle" article. Thanks for sharing what you did.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's ironic... your Step C) is all that needed to be done and it would be the same as this "SQL Spackle" article.
Oh ya. Thanks for pointing that out. And thanks for spackle article.
-
I wonder, Could you add a section to do something similar with an XML output? Standard in 2008 SSMS and 2012 SSMS is 2 MB of output text in XML.
So, for example use this:
DECLARE @LongString AS VARCHAR(MAX)
SET @LongString = STUFF((SELECT CASE WHEN t.Number % 16000 = 0 AND t.Number > 1 THEN Char(13) + Char(10) ELSE ',' END + CAST(t.Number AS VARCHAR(MAX))
FROM dbo.LargeTally t --Has 100,000 rows
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
SELECT [Value] = @LongString
FOR XML PATH('')
-
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.
-
Here is a little function I wrote to deal with this very problem.
CREATE FUNCTION [dbo].[dbaudf_SplitSize] (@String varchar(max),@Size INT = 8000)
RETURNS @Array TABLE(
PartNumberINTIDENTITY(1,1)
,Partvarchar(8000)
)
AS
BEGIN
DECLARE@index INT
IF @Size < 1 SET @Size = 1
IF @Size > 8000 SET @Size = 8000
--loop through source string and add elements to destination table array
WHILE LEN(@String) > 0
BEGIN
IF LEN(@String) > @Size
SET @index = @Size
ELSE
SET @index = LEN(@String)
INSERT@Array
SELECTSUBSTRING(@String, 1, @index)
SET @String = STUFF(@String,1,@Index,'')
END
RETURN
END
GO
-
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 -
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
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 -
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 < & > me', N'&', N'&'), N'<', N'<'))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 ONSET 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'&'), N'<', N'<'))
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
(
SELECTTOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [TheNumber]
FROMmaster.sys.all_columns sc1
CROSS JOINmaster.sys.all_columns sc2
)
SELECT@String = @String + NCHAR(cte.TheNumber)
FROMcte
WHEREcte.TheNumber BETWEEN 32 AND 65533 -- 0 through 31 error (well, except 9, 10, and 13)
ANDcte.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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
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 -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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>
-
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
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply