Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display More Than 8000 Characters (SQL Spackle)


Display More Than 8000 Characters (SQL Spackle)

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45173 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45173 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45173 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45173 Visits: 39925
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! Blush 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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
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.
*/
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45173 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
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.
venoym
venoym
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 2082
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('')


rac.coons
rac.coons
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 39
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.
steve.ledridge
steve.ledridge
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 331
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search