I am using the following code to concatenate all my result into one record (separated by carriage returns)
SELECT [ConcactColumn] = STUFF((
SELECT CHAR(10) + t.column
FROM #table t
FOR XML PATH('')), 1, 1, ''
)
The only issue is I am not able to have a result with more than 43,720 characters when I need to fit at least 5,000,000 .
how can I achieve this ? thank you in advance.
The size of a string in SSMS is limited. But the Size of an XML is not
Then try this:
SELECT STUFF(
(
SELECT CHAR(13) + CHAR(10) + t.name
FROM sys.objects t
FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'), 1, 2, '')
FOR XML PATH('');
I cut away 2 characters with STUFF() due to CHAR(13)+CHAR(10). With CHAR(10) only you must change this to ...),1,1,'').
If the first line might stay blank you can go without STUFF
SELECT
(
SELECT CHAR(10) + t.name
FROM sys.objects t
FOR XML PATH(''),TYPE
).value('text()[1]','nvarchar(max)')
FOR XML PATH('');
Click the XML and you see a result which is limited by your machines capacity only...
If you are trying to copy from the results grid, take a peek here http://connect.microsoft.com/SQLServer/feedbackdetail/view/951995/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode
Perhaps this can help
Declare @S varchar(max) = ''
Select @S = @S + char(10) + t.column
From #table t
Where t.column is not null
Select Len(@S)
SELECT [ConcactColumn] = @S
PRINT will cut this, SELECT will cut it too... I just placed an answer how to solve this within the XML result view, which is not limited in size...This used to give me fits. I love this stored procedure and, although I wish I wrote it, I didn't (credit in the comments).
CREATE PROCEDURE dbo.LongPrint @string nvarchar(MAX)
AS
/*
Source:
https://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html
Example:
exec LongPrint @string = 'This String Exists to test the system.'
This procedure is designed to overcome the limitation in the SQL print command that causes
it to truncate strings longer than 8000 characters (4000 for nvarchar).
It will print the text passed to it in substrings smaller than 4000 characters. If there
are carriage returns (CRs) or new lines (NLs in the text), it will break up the substrings
at the carriage returns and the printed version will exactly reflect the string passed.
If there are insufficient line breaks in the text, it will print it out in blocks of 4000
characters with an extra carriage return at that point.
If it is passed a null value, it will do virtually nothing.
NOTE: This is substantially slower than a simple print, so should only be used when actually needed. */
DECLARE
@CurrentEnd bigint, /* track the length of the next substring */
@offset tinyint /*tracks the amount of offset needed */
set @string = replace(replace(@string, char(13)+char(10), char(10)), char(13), char(10));
WHILE LEN(@String) > 1 BEGIN
IF CHARINDEX(char(10), @string) BETWEEN 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(char(10), @String) -1;
SET @offset = 2;
END
ELSE
BEGIN
SET @CurrentEnd = 4000;
SET @offset = 1;
END;
PRINT SUBSTRING(@String, 1, @CurrentEnd);
SET @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822);
END /*End While loop*/
You would use the proc like this:
DECLARE @results varchar(max);
SELECT @results = STUFF((
SELECT CHAR(10) + t.column
FROM #table t
FOR XML PATH('')), 1, 1, '');
EXEC dbo.LongPrint @results;
So we're clear: This solution is not designed for blazing fastness.
