nadabadan (1/27/2011)
...you will get 11 rows instead of the expect 10.

ColdCoffee (1/26/2011)

---

Thanks for this cute Spackle,Jeff

samir-424465 (1/26/2011)

---

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 (1/27/2011)

---

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 (da-zero) (1/27/2011)

---

Thanks, great article Jeff.
You find uses for the Tally table everywhere, don't you

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 .

Matt Whitfield (1/27/2011)

---

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...

dave.clark (1/27/2011)

---

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.