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 «««1234

Trouble printing out long VARCHAR(MAX) strings? Expand / Collapse
Author
Message
Posted Thursday, August 20, 2009 10:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 31, 2012 9:13 AM
Points: 19, Visits: 128
OK...I have fixed the bug and reposted the SQL as an update to this article. It's a very simple fix. Just remove the '+ 1' in Line 70 of the SQL that was posted previously. Here is the line of code with the problem area bolded and underlined:

SET @SubstringEnd = LEN(@Chunk) - (CHARINDEX(CHAR(10), REVERSE(@Chunk))) + 1 --...AND STOP THE PRINT AT THE END OF THE PREVIOUS LINE.

Thanks again for the feedback.

SB
Post #774385
Posted Thursday, August 20, 2009 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:55 AM
Points: 6, Visits: 35
Folks, there is a limitation to PRINT. It can print 4000 nvarchar() and 8000 varchar().

After sending out my previous message, I realized that the solution is even simpler than what I proposed, requiring no temp tables and no custom-developed sprocs at all: after building the long string as varchar(max) or nvarchar(max), rather than using PRINT, use SELECT, then copy-n-paste the result into your preferred text editor for analysis.

Mike Vassalotti
Herndon, Virginia
mvassal@hotmail.com
Post #774537
Posted Thursday, August 20, 2009 3:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 31, 2012 9:13 AM
Points: 19, Visits: 128
mjarsaniya suggested that in a previous post, but I tried it and it only printed out 8,192 characters.
Post #774613
Posted Friday, August 21, 2009 4:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
I have check with string of 11350 characters and its work fine...............


I cant consider what is problem with you.


Post #774885
Posted Friday, August 21, 2009 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 31, 2012 9:13 AM
Points: 19, Visits: 128
That is interesting. Does this work for anyone else out there?

SB
Post #774954
Posted Friday, August 21, 2009 3:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 31, 2012 9:13 AM
Points: 19, Visits: 128
aschoch: Finally got around to trying your CDATA suggestion, but it only prints out 8,192 characters. I think the key is to use the FOR XML clause. That seems to be the one that can get around the 8,000 character limitation. Since we're just doing a CONVERT in this query it's probably not getting around that limitation. Looks like Microsoft may have had to do something to get around that limitation when displaying XML using FOR XML.

I'm continuing to pursue this FOR XML angle to see if I can come up with a simpler solution.

SB
Post #775452
Posted Wednesday, May 5, 2010 10:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 21,397, Visits: 9,612
Wow, thanks a million.


I have to "duplicate" my queries dynamically for reporting. My customer has 30 incorporations and each of them has it's own set of tables in the same DB. He needs be to union all the data into a single recordset / reports for any number of cies chosen at run time by the user... now what's 30 times 250 lines for a query?


...Thank God the migration into the ERP isn't complete yet!
Post #916242
Posted Thursday, May 6, 2010 2:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 21,397, Visits: 9,612
I found a little annoyance in the script... it doesn't print out if you have less than 4000 characters. I added the if before the loop and now all is well .


ALTER PROCEDURE dbo.spPrintOutLongSQL
@SQLInput NVARCHAR(MAX),
@VariableName NVARCHAR(128),
@Debug BIT = 0
AS

/******************************************************************************************************
** Name: spPrintOutLongSQL.sql
** Script Type: Stored Procedure
** Desc: Description
**
** Auth: Sam Bendayan
** Database: HRMS_GLOBALDATA
** Scrum Team Name: Sherpas
** VersionOne Story #: ''
** Resync Parent Node: ''
*******************************************************************************************************
** USAGE:
DECLARE @SQLInput NVARCHAR(MAX)

--SET @SQLInput = ''
SET @SQLInput = (select replicate('a', 4000))
SET @SQLInput = @SQLInput + (select replicate('b', 4000))
SET @SQLInput = @SQLInput + (select replicate('C', 4000))
SET @SQLInput = @SQLInput + (select replicate('D', 4000))
SET @SQLInput = @SQLInput + (select replicate('E', 4000))
SET @SQLInput = @SQLInput + (select replicate('F', 4000))
SET @SQLInput = @SQLInput + (select replicate('G', 4000))
SET @SQLInput = @SQLInput + (select replicate('H', 4000))
--SET @Sqlinput = @SQLInput + ')'

EXEC dbo.spPrintOutLongSQL
@SQLInput = @SQLInput,
@VariableName = '@SQL',
@Debug = 0


SELECT ASCII(' ')
SELECT ASCII(' ')
THE PROBLEM WE'RE TRYING TO SOLVE HERE IS THAT A PRINT COMMAND CAN ONLY PRINT 8,000 CHARACTERS.
SO WE HAVE TO HAVE MULTIPLE PRINT COMMANDS TO PRINT OUT A STRING LONGER THAN THAT.
BUT, EVERY PRINT COMMAND STARTS ON A NEW LINE (ADDS A CRLF)...THIS IS THE PROBLEM.
HOW CAN WE REMOVE THIS CRLF THAT THE PRINT COMMAND GENERATES?
*******************************************************************************************************/
SET NOCOUNT ON

DECLARE @VariableLength NUMERIC(10,2),
@PrintSQL NVARCHAR(MAX),
@ParmDefinition NVARCHAR(500),
@Chunk NVARCHAR(4000),
@SubstringStart INT,
@SubstringEnd INT,

SET @VariableLength = LEN(@SQLInput)
SET @ParmDefinition = N'@SQLInput NVARCHAR(MAX)'
SET @PrintSQL = 'PRINT ''--' + @VariableName + ': ''' + CHAR(10)
SET @SubstringStart = 0
SET @SubstringEnd = 4000

IF @VariableLength <= @SubstringEnd
BEGIN
SET @PrintSQL = @PrintSQL + 'PRINT SUBSTRING(@SQLInput, ' + CAST(@SubstringStart AS NVARCHAR(10)) + ', ' + CAST(@SubstringEnd AS NVARCHAR(10)) + ')'
END

WHILE (@SubstringStart + @SubstringEnd) < @VariableLength
BEGIN
--FIX "BROKEN LINE AT 4,000 CHARACTER POSITION" PROBLEM.
SELECT @SubstringStart = @SubstringStart + CASE @SubstringStart WHEN 0 THEN 1 ELSE @SubstringEnd END

SET @Chunk = SUBSTRING(@SQLInput, @SubstringStart, 4000)
IF RIGHT(@Chunk, 1) NOT IN ('', CHAR(10), CHAR(32), CHAR(9), CHAR(13))--IF THERE IS A LETTER IN THE 4,000th POSITION, ASSUME THAT IT'S A BROKEN LINE...
BEGIN
SET @SubstringEnd = LEN(@Chunk) - (CHARINDEX(CHAR(10), REVERSE(@Chunk))) --...AND STOP THE PRINT AT THE END OF THE PREVIOUS LINE.
END
ELSE
BEGIN
SET @SubstringEnd = LEN(@Chunk) --OTHERWISE, END POSITION IS OK.
END

IF @Debug = 1
BEGIN
PRINT 'PRINT SUBSTRING(@SQLInput, ' + CAST(@SubstringStart AS NVARCHAR(10)) + ', ' + CAST(@SubstringEnd AS NVARCHAR(10)) + ')'
END

SET @PrintSQL = @PrintSQL + 'PRINT SUBSTRING(@SQLInput, ' + CAST(@SubstringStart AS NVARCHAR(10)) + ', ' + CAST(@SubstringEnd AS NVARCHAR(10)) + ')'
END

IF @Debug = 1
BEGIN
PRINT @VariableLength
END

IF @Debug = 0
BEGIN
EXEC sp_executeSQL
@PrintSQL,
@ParmDefinition,
@SQLInput = @SQLInput
END
ELSE
BEGIN
SET @PrintSQL = @PrintSQL + 'PRINT ''--' + CAST(@VariableLength AS VARCHAR(20)) + ' characters.'''
END



Post #917414
Posted Wednesday, February 1, 2012 2:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 10:18 AM
Points: 6, Visits: 59
Not sure if I got a bad version of the script, but it doesn't work right - infinitely loops. The problem is line 76; it assumes every chunk to be written is 4000 characters long. So when it checks the last character on anything less than 4000 characters, the full length of the string is never realized.

I added this and it seems to be working fine now:

IF LEN(@Chunk) = 4000 And RIGHT(@Chunk,1)...
Post #1245342
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse