SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trouble printing out long VARCHAR(MAX) strings?


Trouble printing out long VARCHAR(MAX) strings?

Author
Message
Sam Bendayan
Sam Bendayan
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 128
Comments posted to this topic are about the item Trouble printing out long VARCHAR(MAX) strings?
ian williams-212646
ian williams-212646
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 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
RiK Muñoz
RiK Muñoz
Mr or Mrs. 500
Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)

Group: General Forum Members
Points: 511 Visits: 126
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

blandry
blandry
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1715 Visits: 723
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...
adish
adish
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 639
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.
Sam Bendayan
Sam Bendayan
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 128
When writing SQL code generators it is very likely that you will exceed this limit.
aschoch
aschoch
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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"
Paul Bradshaw
Paul Bradshaw
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 140
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.
Lee Hilton
Lee Hilton
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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.
aschoch
aschoch
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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, '') 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search