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 Wednesday, July 29, 2009 12:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, July 29, 2009 3:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 6, 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:33 AM
Points: 47, Visits: 71
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 7, 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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 231, Visits: 488
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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 3:07 PM
Points: 30, Visits: 123
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 1234»»»

Permissions Expand / Collapse