SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sam Bendayan / Trouble printing out long VARCHAR(MAX) strings? / Latest Posts

RE: Trouble printing out long VARCHAR(MAX) strings?

sgtmango333 — Wed, 01 Feb 2012 14:33:58 GMT

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

RE: Trouble printing out long VARCHAR(MAX) strings?

Ninja's_RGR'us — Thu, 06 May 2010 14:21:48 GMT

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.[code="sql"]ALTER PROCEDURE dbo.spPrintOutLongSQL @SQLInput NVARCHAR(MAX), @VariableName NVARCHAR(128), @Debug BIT = 0AS/******************************************************************************************************** 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[/code]

RE: Trouble printing out long VARCHAR(MAX) strings?

Ninja's_RGR'us — Wed, 05 May 2010 10:08:33 GMT

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!

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Fri, 21 Aug 2009 15:13:21 GMT

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

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Fri, 21 Aug 2009 05:57:03 GMT

That is interesting. Does this work for anyone else out there?SB

RE: Trouble printing out long VARCHAR(MAX) strings?

mayank jarsaniya — Fri, 21 Aug 2009 04:31:44 GMT

I have check with string of 11350 characters and its work fine...............I cant consider what is problem with you.

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Thu, 20 Aug 2009 15:02:23 GMT

mjarsaniya suggested that in a previous post, but I tried it and it only printed out 8,192 characters.

RE: Trouble printing out long VARCHAR(MAX) strings?

Mike Vassalotti — Thu, 20 Aug 2009 13:30:18 GMT

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 VassalottiHerndon, Virginiamvassal@hotmail.com

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Thu, 20 Aug 2009 10:27:10 GMT

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))) [b][u] + 1 [/u][/b]--...AND STOP THE PRINT AT THE END OF THE PREVIOUS LINE. Thanks again for the feedback.SB

RE: Trouble printing out long VARCHAR(MAX) strings?

Adam Haines — Wed, 19 Aug 2009 08:36:13 GMT

[quote][b]mjarsaniya (8/19/2009)[/b][hr]why to do so many homework.........(creating tamp table take value in it then copy)you simply select @variablename (As previously I have given )and then take it to text editor.........[/quote]For me, using a select and copying the command out is not very efficient. When copying/pasting the command into an editor, you lose all formatting. If you have a code formatter, selecting the command is probably the best option because you can copy the command into a new window and click the format button. For those who do not have a formatter, this option is going to be worse because you have to reformat a huge tsql statement. I believe in the scenario, where a code formatter is not available, the XML method may be the easiest to implement even though it has character limitations.

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Wed, 19 Aug 2009 06:47:18 GMT

Sorry for not replying sooner...thakur_samir: The option you mentioned DOES affect the length of the string that is displayed, but it has a max value of 8192. Try to put something bigger in there and you'll see that it sets it back down to 8192.mjarsaniya: SELECT @SQL doesn't show the whole string. It only shows up to 8,192 characters, so it has the same problem as PRINT.Steve McRoberts: I wasn't seeing the bug because I had changed my 'usage' example to add a CHAR(10) to the end of each line. If you do that then the string prints out correctly. However, when I remove the CHAR(10) I see the bug. Will fix and repost. Thanks for the feedback.Mike Vassalotti: I have tried the table solution before and it only returned 8,192 characters. Nonetheless, I tried your specific code but it only returned 8,192 characters as well. Does this work for you on strings longer than 8,192 characters?SB

RE: Trouble printing out long VARCHAR(MAX) strings?

mayank jarsaniya — Wed, 19 Aug 2009 00:24:56 GMT

why to do so many homework.........(creating tamp table take value in it then copy)you simply select @variablename (As previously I have given )and then take it to text editor.........

RE: Trouble printing out long VARCHAR(MAX) strings?

Mike Vassalotti — Tue, 18 Aug 2009 11:04:25 GMT

I experienced the same problem of printing nvarchar(max)/varchar(max), with truncation at 4000/8000 characters. I strongly agree: it's a bug (while Microsoft insists it's a feature). I found a very simple/effective workaround. I don't know how/why my workaround works, but it works, bullet-proof, all the times. Try it.After bulding a @LongString longer than 8000 chars, as varchar(max), create a small temp table with two fields, insert the long string @LongString you created through your code into the varchar(max) field, and then retrieve it from the table, as shown below: CREATE TABLE [dbo].[TEMP]( [RecNo] [smallint] IDENTITY(1,1) NOT NULL, [LongString] [varchar](max) NULL) ON [PRIMARY]INSERT INTO [dbo].[TEMP] ([LongString])SELECT @LongStringSELECT LongString FROM [dbo].[TEMP] WHERE RecNo = 1--the above SELECT returns the entire unchopped string,--which you can copy and paste into a text editor for analysisDROP TABLE [dbo].[TEMP]That's all there is to it.Enjoy!Mike Vassalottimvassal@hotmail.comHerndon, Virginia

RE: Trouble printing out long VARCHAR(MAX) strings?

Steve McRoberts-357330 — Wed, 12 Aug 2009 11:06:46 GMT

This is a very useful idea, but it seems that there is a bug in the code. If you run the "usage" example at the top of the code, you will see only 3,999 "b" characters: in place of the 4,000th "b" a "C" gets written. This continues with each succeeding string of characters being only 3,999 instead of 4,000. I changed one of my procs to call this code (in place of its current use of a loop with READTEXT) and I ended up with a mess.

RE: Trouble printing out long VARCHAR(MAX) strings?

mayank jarsaniya — Sat, 01 Aug 2009 04:06:10 GMT

I think another solution...................select @sqlinstead of print @sql.................It will gives all characters in single line............................

RE: Trouble printing out long VARCHAR(MAX) strings?

thakur_samir — Thu, 30 Jul 2009 21:15:45 GMT

I am not sure if the procedure or script is really needed for this. May be you should try followingGo to Options and Query results one of the option has a setting that allows you to control the maximum length of the string that is displayed.I use results to grid and increase the text sice to 64 K or something

RE: Trouble printing out long VARCHAR(MAX) strings?

aschoch — Thu, 30 Jul 2009 09:21:54 GMT

To Sam Bendayan,I couldn't get my SQL to appear correctly--the HTML editor won't display the CDATA section right. Here's the first part:[code]SELECT CONVERT(xml, '' + column + '') AS ColumnXML FROM TABLE[/code]That part works if your string doesn't have any XML reserved characters like < or &. To be safe, you need to account for those, so you can include a CDATA section inside the x tag, and put your column inside the CDATA. Unfortunately, there's no way to show you the actual syntax--nothing lets me actually display a CDATA section here. This is as close as I can get: [code]SELECT CONVERT(xml, '*![CDATA[' + column + ']]*') AS ColumnXML FROM TABLE[/code]Replace the asterisks with open and close tags, and you're good.

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Thu, 30 Jul 2009 08:52:03 GMT

Regarding the reply by aschoch: I'm not sure how your suggestion works...you mentioned that you ran this query:SELECT CONVERT(xml, '') AS DataXML FROM MyTableHowever, I don't see a column reference in that query. Can you explain how this works in a bit more detail? I'm very interested in seeing if XML technology can replace what I'm doing with a simpler script.Thanks,SB

RE: Trouble printing out long VARCHAR(MAX) strings?

vikramjits — Thu, 30 Jul 2009 07:25:38 GMT

I faced the same problem sometime back... the way around for me was to insert the dynamic query in one of the tables i've created with varchar(max) field and then selecting the query from that table...:-D

RE: Trouble printing out long VARCHAR(MAX) strings?

timothyawiseman — Wed, 29 Jul 2009 17:44:49 GMT

A very nice article.I ran into a similar problem a little over a year ago and posted my solution at the time to the site. It is at: http://www.sqlservercentral.com/scripts/Print/63240/It is always interesting to see how different people solve similar problems. I also found some of the XML-based solutions mentioned in the comments very interesting.

RE: Trouble printing out long VARCHAR(MAX) strings?

Adam Haines — Wed, 29 Jul 2009 14:59:30 GMT

[quote]Referring to the last post about the XML FOR PATH functionality, this is very interesting, but it has one issue: when you have the "<" or ">" characters in the string, it displays them as "<" and ">" respectively. Do you know how to get past this?[/quote]There is probably a way to do this, but I dont know it. I have never had a need to devise a solution. Typically, I just do a search/replace, if I need to change the HTML tags. It should also be known that other characters may not come across correctly, such as &. I would be interested to hear if anyone has a work around for this problem.

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Wed, 29 Jul 2009 13:01:51 GMT

Referring to the last post about the XML FOR PATH functionality, this is very interesting, but it has one issue: when you have the "<" or ">" characters in the string, it displays them as "<" and ">" respectively. Do you know how to get past this?Thanks,SB

RE: Trouble printing out long VARCHAR(MAX) strings?

Adam Haines — Wed, 29 Jul 2009 11:30:51 GMT

I too have faced this problem. The easiest method I have found is to set the string variable to an xml variable. You can select the variable and use for xml path clause to dynamical convert the string into "valid" xml text. You can then select the xml coumn and click the link to launch the code in a new window.[code]SET NOCOUNT ON;GODECLARE @t TABLE(ID INT,Str1 VARCHAR(1500),Str2 VARCHAR(1500),Str3 VARCHAR(1500),Str4 VARCHAR(1500))INSERT INTO @t VALUES ( 1, REPLICATE('A',1500), REPLICATE('B',1500), REPLICATE('C',1500), REPLICATE('D',1500));DECLARE @sql VARCHAR(MAX), @x XML, @Id INT SET @Id = 1SET @sql = (SELECT 'SELECT ' + CHAR(13) + '''' + Str1 +'''' + ',' + CHAR(13) + '''' + Str2 +'''' + ',' + CHAR(13) + '''' + Str3 +'''' + ',' + CHAR(13) + '''' + Str4 +'''' + CHAR(13) + 'FROM t' + CHAR(13) + '--WHERE something = something' FROM @t WHERE id = 1)SET @x = (SELECT @sql AS [text()] FOR XML PATH(''))SELECT dataLength(@sql) AS SQL_LengthSELECT @x[/code]

RE: Trouble printing out long VARCHAR(MAX) strings?

joku01 — Wed, 29 Jul 2009 11:27:32 GMT

You can actually specify a length larger than the total string and substring will work fine, so the following will work too:declare @str varchar(max), @strlen int, @startIndex int, @blockSize intselect @str = 'some long sql string', @strlen = len(@str), @startIndex = 1, @blockSize = 4000while(@startIndex <= @strlen)begin print substring(@str,@startIndex,@blockSize) set @startIndex = @startIndex + @blockSizeend

RE: Trouble printing out long VARCHAR(MAX) strings?

geerobg — Wed, 29 Jul 2009 10:51:13 GMT

We had the same issue.We have a dynamically assigned security model that allows different levels of heirarchy in an organization to view data by creating where clauses which include or don't include data contained in our dashboard pivot grids / graphs. Basically with any field that we supply in our dashboard queries, we allow the end users to limit data that is then viewable by subordinates in an organization (a CFO can limit a regional manager's access to the exact same financial query by say division, the regional manager can then limit a branch manager's access by branch etc...). As the where clause is generated further down the list getting more and more specific, often times our where clause has exceeded 8000 characters. Our solution was pretty simple, we'd use a temp table with one text field, build our where clause into that field and then at the end of the where clause generator, simply perform a select on the text field of the temporary table... this is then added to the query inside c# code that generates the dataset.

RE: Trouble printing out long VARCHAR(MAX) strings?

E-DB — Wed, 29 Jul 2009 10:08:08 GMT

This is in the 32-bit version of SQL Server as well. In my experience, it's usually good practice to store the generated dynamic SQL in a log table for easy debugging later on. Not exactly related to this post, but something to consider.

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Wed, 29 Jul 2009 09:44:04 GMT

In my project I was not using tables to hold the string...just variables. I tried building a variable with the very long string and then just doing a SELECT @VariableName but it only printed out 8,192 characters.BTW, this is all on the 32-bit version of SQL Server.SB

RE: Trouble printing out long VARCHAR(MAX) strings?

E-DB — Wed, 29 Jul 2009 09:23:06 GMT

A solution I've found for this issue of SQL Server truncating the VARCHAR(MAX) value is to right click the table I want in the Object Explorer and "Open Table". This view doesn't truncate the values within each field.If it's a small table, you're done, and you can copy the entire VARCHAR value out of the field from this view. If it's a big table, you can stop the query, click the "Show SQL Pane" icon that shows up in your toolbar, and write or copy/paste your query in the window that appears. The editor is akin to Access's Query Editor, which means it sucks, but this is the fastest, most fool proof way I've found to quickly get the entire string in a table.

RE: Trouble printing out long VARCHAR(MAX) strings?

JJ B — Wed, 29 Jul 2009 08:58:10 GMT

I voted. Thanks for pointing this out.I haven't run into this myself, but it seems like a basic need.Thanks for sharing.

RE: Trouble printing out long VARCHAR(MAX) strings?

aschoch — Wed, 29 Jul 2009 08:57:05 GMT

Here's a funny hack. Knowing that XML data doesn't have the limitation, I ran this query on a non-XML column [Note: can't get this to appear right--there's a CDATA node inside the x tag, and concatenated inside the CDATA is your column]: [code]SELECT CONVERT(xml, '') AS DataXML FROM MyTable[/code]. It spit everything out despite the length exceeding the max for a string. Just have to strip off the containing XML tag, and you've got your string.

RE: Trouble printing out long VARCHAR(MAX) strings?

Lee Hilton — Wed, 29 Jul 2009 08:48:22 GMT

I'm not sure this constitutes much of an issue, and probably not a bug in any case. This is a limitation with the print statement itself and has nothing to do with the (n)varchar(max) data type. I have run in to this very problem outside of the (max) type when concatenating strings together.My work around was to actually SELECT the column of data I needed and work with the object that way. I was curious if this would work, so I just tested on SQL 2005 x64 Standard (no service packs). My table is a single field called MyString of type varchar(max). I inserted a string of Lorem Ipsum that was a bit over 11,000 characters. As the article stated, printed the string truncated at character 8000. SELECTing the field did no truncation.

RE: Trouble printing out long VARCHAR(MAX) strings?

Paul Bradshaw — Wed, 29 Jul 2009 08:23:15 GMT

I had worse issues when I tried to run the SQL through OSQL.EXE ... it seemed to like to (almost randomly) truncate strings to be even smaller.I ended up writing code that would parse the string line by line, and PRINT out each line in its own print statement. That was the only thing that seemed to work, ugly as it was.These limitations really are ridiculous.

RE: Trouble printing out long VARCHAR(MAX) strings?

aschoch — Wed, 29 Jul 2009 07:28:24 GMT

Had the same issue with a column into which I was stuffing large XML strings. A simpler workaround, if your string happens to be XML, is to type the column as XML--there appears to be no size restriction on the output of the XML data type. select convert(xml,DATACOLUMN) as "DataXML"

RE: Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Wed, 29 Jul 2009 07:16:03 GMT

When writing SQL code generators it is very likely that you will exceed this limit.

RE: Trouble printing out long VARCHAR(MAX) strings?

adish — Wed, 29 Jul 2009 07:01:14 GMT

Nice post.Just out of curiosity/ignorance/inexperience, may I ask you where are we likely to write 50000-100000 lines of Dynamic SQL. I only have ~2 yrs of exp with SQL, that justifies the question.

RE: Trouble printing out long VARCHAR(MAX) strings?

blandry — Wed, 29 Jul 2009 05:54:15 GMT

Thanks very much for this very timely article. We just ran into this problem a few weeks ago and it was just great timing that your elegant solution was presented.Very helpful, and very much appreciated!

RE: Trouble printing out long VARCHAR(MAX) strings?

RiK Muñoz — Wed, 29 Jul 2009 03:14:01 GMT

a very handy SP & well written article! Nice one!ps. also tried to vote but rating widget wont move from the 1 *!!!

RE: Trouble printing out long VARCHAR(MAX) strings?

ian williams-212646 — Wed, 29 Jul 2009 03:05:07 GMT

I had exactly this problem with a script I wrote that would print out any stored procedures and functions that were created or modified between 2 dates. We use it for deployment scripts but when we have very large SPs they get truncated, this script of yours will be fantastic.P.S. I tried to vote for this but the voting control will not move from 1 vote

Trouble printing out long VARCHAR(MAX) strings?

Sam Bendayan — Wed, 29 Jul 2009 00:29:18 GMT

Comments posted to this topic are about the item [B]Trouble printing out long VARCHAR(MAX) strings?[/B]