SQL Clone
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 Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218239 Visits: 41995
Comments posted to this topic are about the item Display More Than 8000 Characters (SQL Spackle)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8905 Visits: 5555
Thanks for this cute Spackle,Jeff :-)
samir-424465
samir-424465
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 83
We can use BCP utility to get the data of the table (queryout option). The contents of the file will have character field (with varchar(MAX) / nvarchar(MAX)) with all the data in it.
chuckh 3191
chuckh 3191
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 9
You don't have to have a Tally table made in your database. Using common table expressions, you can generate them on the fly. Here is such using the example code from the article where we reaplace Tally with cteTally

--===== Declare a couple of long string variables of two different datatypes
DECLARE @LongString VARCHAR(MAX),
@NLongString NVARCHAR(MAX)
;
--===== Fill each string with 10,000 GUIDs followed by a space
-- for a total of 369999 (+1 trailing space) characters.
;WITH
cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP 20000
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT @LongString = (SELECT CAST(NEWID() AS CHAR(36)) + ' '
FROM cteTally t
WHERE t.N BETWEEN 1 AND 10000
FOR XML PATH('')),
@NLongString = @LongString
;
--===== Just confirming the length of the strings here
SELECT LEN(@LongString), LEN(@NLongString)
;
--===== Let's solve the problem with a little control over the width
-- of the returned data. This could easily be converted into
-- an inline Table Valued Function.
DECLARE @Width INT;
SELECT @Width = 8000;

--===== Show that the solution works on VARCHAR(MAX)
;WITH
cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP (LEN(@LongString))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT StartPosition = (t.N-1)*@Width+1,
SliceData = SUBSTRING(@LongString,(t.N-1)*@Width+1,@Width)
FROM cteTally t
WHERE t.N BETWEEN 1 AND LEN(@LongString)/@Width+1
;
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63528 Visits: 13298
Thanks, great article Jeff.
You find uses for the Tally table everywhere, don't you :-D


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
nigel.
nigel.
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3285 Visits: 2908
Thanks Jeff, nice article.

Spotted a minor typo in the first code example, has SliceDate instead of SliceData for the column alias.
I know it has no real effect. But, knowing what a perfectionist you are .:-)

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Matt Whitfield
Matt Whitfield
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2285 Visits: 719
Good article Jeff, nice one.

It does surprise me, though, that it's even necessary. I mean, it's 2011...

I guess that sort of thing is why I very rarely use SSMS... :-)

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
dave.clark
dave.clark
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 564
Thanks for the excellent info!
I tackled this issue myself previously, but my solution is not quite as "simple" ;-)

I'll have to test this and see if I can replace my lengthy solution.
thisisfutile
thisisfutile
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 1005
nigel. (1/27/2011)
Thanks Jeff, nice article.

Spotted a minor typo in the first code example, has SliceDate instead of SliceData for the column alias.
I know it has no real effect. But, knowing what a perfectionist you are .:-)


I noticed that too. Is it wrong for me to find some sort of comfort in knowing that even Jeff Moden makes the date/data typo? :-P

If I had a dollar for every time I've done that...

Thanks for the article Jeff. I always enjoy seeing how you solve "the loop" issue.
nigel.
nigel.
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3285 Visits: 2908
thisisfutile (1/27/2011)
nigel. (1/27/2011)
Thanks Jeff, nice article.

Spotted a minor typo in the first code example, has SliceDate instead of SliceData for the column alias.
I know it has no real effect. But, knowing what a perfectionist you are .:-)


... Is it wrong for me to find some sort of comfort in knowing that even Jeff Moden makes the date/data typo? :-P


No, definitely not :-)

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

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