Trouble printing out long VARCHAR(MAX) strings?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sam Bendayan » Trouble printing out long VARCHAR(MAX)...

Add to briefcase

39 posts, Page 1 of 4

Trouble printing out long VARCHAR(MAX) strings?

Expand / Collapse

Author

Message

Posted Wednesday, July 29, 2009 12:29 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 31, 2012 9:13 AM
Points: 19, Visits: 128

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

Post #761290

ian williams-212646

Posted Wednesday, July 29, 2009 3:05 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 06, 2010 3:52 PM
Points: 23, Visits: 26

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

Post #761345

Posted Wednesday, July 29, 2009 3:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:04 AM
Points: 46, Visits: 63

a very handy SP & well written article! Nice one!

ps. also tried to vote but rating widget wont move from the 1 *!!!

Con mucho carino,

RiK Munoz

Post #761347

Posted Wednesday, July 29, 2009 5:54 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 07, 2012 9:23 AM
Points: 304, Visits: 716

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!

There's no such thing as dumb questions, only poorly thought-out answers...

Post #761416

Posted Wednesday, July 29, 2009 7:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 228, Visits: 403

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.

Post #761493

Posted Wednesday, July 29, 2009 7:16 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 31, 2012 9:13 AM
Points: 19, Visits: 128

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

Post #761521

Posted Wednesday, July 29, 2009 7:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 8:16 AM
Points: 3, Visits: 24

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"

Post #761532

Posted Wednesday, July 29, 2009 8:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 2:02 PM
Points: 30, Visits: 113

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.

Post #761607

Posted Wednesday, July 29, 2009 8:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 29, 2009 11:38 AM
Points: 2, Visits: 17

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.

Post #761653

Posted Wednesday, July 29, 2009 8:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 8:16 AM
Points: 3, Visits: 24

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]:

SELECT CONVERT(xml, '<x><![CDATA[ ' + MyColumn + ']]></x>') AS DataXML FROM MyTable

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

Post #761663

« Prev Topic | Next Topic »

Add to briefcase

39 posts, Page 1 of 4

Permissions

Expand / Collapse