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 3 of 4

«««1 2 3 4 »»

Trouble printing out long VARCHAR(MAX) strings?

Expand / Collapse

Author

Message

Posted Thursday, July 30, 2009 7:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 15, 2009 2:53 AM
Points: 1, Visits: 6

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

BigGrin

Post #762391

Posted Thursday, July 30, 2009 8:52 AM

Grasshopper

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

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 MyTable

However, 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

Post #762468

Posted Thursday, July 30, 2009 9:21 AM

Forum Newbie

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

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:

SELECT CONVERT(xml, '<x>' + column + '</x>') AS ColumnXML FROM TABLE

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:

SELECT CONVERT(xml, '<x>*![CDATA[' + column + ']]*</x>') AS ColumnXML FROM TABLE

Replace the asterisks with open and close tags, and you're good.

Post #762499

Posted Thursday, July 30, 2009 9:15 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 1:50 PM
Points: 21, Visits: 24

I am not sure if the procedure or script is really needed for this. May be you should try following
Go 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

Post #762878

mayank jarsaniya

Posted Saturday, August 01, 2009 4:06 AM

Old Hand

Group: General Forum Members
Last Login: Monday, December 26, 2011 1:28 PM
Points: 346, Visits: 401

I think another solution...................

select @sql

instead of print @sql.................

It will gives all characters in single line............................

Post #763569

Steve McRoberts-357330

Posted Wednesday, August 12, 2009 11:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, Visits: 82

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.

Post #769515

Mike Vassalotti

Posted Tuesday, August 18, 2009 11:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, January 19, 2013 4:07 AM
Points: 5, Visits: 28

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 @LongString

SELECT 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 analysis

DROP TABLE [dbo].[TEMP]

That's all there is to it.

Enjoy!

Mike Vassalotti
mvassal@hotmail.com
Herndon, Virginia

Post #772893

mayank jarsaniya

Posted Wednesday, August 19, 2009 12:24 AM

Old Hand

Group: General Forum Members
Last Login: Monday, December 26, 2011 1:28 PM
Points: 346, Visits: 401

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

Post #773212

Posted Wednesday, August 19, 2009 6:47 AM

Grasshopper

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

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

Post #773442

Posted Wednesday, August 19, 2009 8:36 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 2,278, Visits: 2,998

mjarsaniya (8/19/2009)

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

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.

My blog: http://jahaines.blogspot.com

Post #773578

« Prev Topic | Next Topic »

Add to briefcase

39 posts, Page 3 of 4

«««1 2 3 4 »»

Permissions

Expand / Collapse