August 20, 2009 at 10:27 am
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
August 20, 2009 at 1:30 pm
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
August 20, 2009 at 3:02 pm
mjarsaniya suggested that in a previous post, but I tried it and it only printed out 8,192 characters.
August 21, 2009 at 4:31 am
I have check with string of 11350 characters and its work fine...............
I cant consider what is problem with you.
August 21, 2009 at 5:57 am
That is interesting. Does this work for anyone else out there?
SB
August 21, 2009 at 3:13 pm
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
May 5, 2010 at 10:08 am
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? :hehe:
...Thank God the migration into the ERP isn't complete yet!
May 6, 2010 at 2:21 pm
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 :-D.
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
February 1, 2012 at 2:33 pm
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)...
June 30, 2016 at 1:25 am
thanks for this solution. i'm creating a repository DB for maintenance and handy scripts. some are for SQL server 2005 and wont compile as Sps in 2012, etc, so I am now saving scripts into a varchar max field in a table. the problem was, without creating an interface, there was no way to access the full scripts from the field easily.
this SP has solved the problem! saved me writing an asp.ntet page and making the simple scripts db a pain to move around!
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply