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 Wednesday, February 02, 2011 10:54 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Dugi (1/28/2011)
Hey Jeff, I'm late here, but really this is very nice article, thanks for sharing!

Wish you more success my friend,
Dugi


No problem. Thanks for the read and the kind words, Dugi!


--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." -- 04 August 2013
(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 #1057822
Posted Wednesday, February 02, 2011 10:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
JJ B (1/31/2011)
Believe it or not, this kind of thing can have a production purpose. I have to send "narrative" from our database to a database in another agency. The other agency set their narrative column to have a max of X characters even though the actual data would be much bigger than X. (Don't ask why.) When they display the narrative to the user on their web application, they string the text from multiple rows together. When the narrative is saved to the database, they break it back up into X chunks/rows.

So, when I send the agency our data, I have to break it into chunks of size X. It doesn't mater where the break is, and it doesn't even matter if there is an extra row at the end. So, this kind of technique could work great for my production purpose. (Note: I don't currently use a loop. I also don't use this spackle method. I will do some research first, but I will likely switch methods in the future.)


Very cool feedback, JJ B. If you're not currently using a loop or the method in the article, what are you using?


--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." -- 04 August 2013
(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 #1057823
Posted Thursday, February 03, 2011 6:59 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Roy Ernest (1/31/2011)
Hi Jeff, Great article as usual.


Hi Roy! Thanks for the read and the comment. Always a pleasure.


--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." -- 04 August 2013
(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 #1058061
Posted Thursday, February 03, 2011 7:07 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
timothyawiseman (2/1/2011)
I have run into this problem off and on, and once even wrote a solution to it ( http://www.sqlservercentral.com/scripts/Print/63240/ ). After struggling with it myself a few times, I am in awe of the elegance of your solution. It is very clean and effecient and easy to understand.

One challenge that I think remains in your technique is that the output is in the form of a select statement rather than a print. While you can certain output to text as you mentioned, that can be problematic if your are troubleshooting something and want other output to come out as a grid. Still, an absolutely beautiful piece of code.


Wow! I'm humbled, Timothy. Thanks for the very kind words and the awesome feedback.

I absolutely agree... the grid/text mix is very helpful in troubleshooting and forcing the results to the Messages tab to see a bit of correctly formatted dynamic SQL along with the data it produced works best when the grid/text mix is available.


--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." -- 04 August 2013
(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 #1058066
Posted Thursday, February 03, 2011 9:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
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.

/*
So, just how is the text broken out?
There were two general possible approaches. I don't know which is faster, but
speed is not a major issue here. I just liked the approach taken here best. It
seems the cleanest.
The approach not taken: Use cursors and a couple embedded loops to go through
each applicable record and then to break out each applicable text chunk in each
record.
The lovable approach used below: Three steps:
a) for each return record, create copies in @RetTable. The number of copies created for all records equals the maximum possible number of chunks needed for
the data being exported now. A simple DataLength() function will tell us
the length of the largest narrative in the data to be exported. If you divide
that number by the ChunkLength and round up, you get the maximum number of
chunks needed (and is the number of duplicate records we want to create in
this step). For example, if the record with the largest narrative will need no more than 3
records/chunks, then all the narrative records will essentially be unioned
to themselves/duplicated 3 times in this step. This data will be stored in our return
table variable, @RetTable.
Note that we give each duplicate record a sequence number using the Number table. In the
example used here, the duplicate allegation narrative records will have
sequence numbers 1, 2 and 3.
b) run a query to delete records which do not have enough text to fill up any
part of that chunk. Continuing the example from above, if allegation 234
has a narrative that takes 3 chuncks/records, then step a) above makes 3
three copies of each allegation, including allegation say 543, which say only
needs two chunks. So, in this step, we delete the record for the record
belonging to the third sequence number for allegation narrative 543. If
any allegations only need one chunk, then we delete the records for the last
sequence numbers. The where clause in our query already eliminates narratives
records without any text.
c) run a query that uses SUBSTRING to make each remaining record only have
the relevant chunk of text needed for that sequence number. For example,
the records belonging to sequence/chunk #1 will delete everything after the
first 8,000 characters. The records belonging to sequence 2 will delete
all data except for characters 8,001 through 16,000.
*/
Post #1058183
Posted Friday, February 04, 2011 6:54 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
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." -- 04 August 2013
(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 #1058705
Posted Friday, February 04, 2011 8:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
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.
Post #1058813
Posted Friday, June 28, 2013 9:40 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: Today @ 4:33 AM
Points: 936, Visits: 1,528
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('')

Post #1468596
Posted Friday, June 28, 2013 4:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 4:22 AM
Points: 1, Visits: 25
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.
Post #1468724
Posted Friday, June 28, 2013 7:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:43 AM
Points: 13, Visits: 186
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 (
PartNumber INT IDENTITY(1,1)
,Part varchar(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
SELECT SUBSTRING(@String, 1, @Index)

SET @String = STUFF(@String,1,@Index,'')
END

RETURN
END
GO

Post #1468756
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse