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 Thursday, July 30, 2009 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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...
Post #762391
Posted Thursday, July 30, 2009 8:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Saturday, August 1, 2009 4:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 346, Visits: 412
I think another solution...................

select @sql

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

It will gives all characters in single line............................
Post #763569
Posted Wednesday, August 12, 2009 11:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 18, 2009 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:55 AM
Points: 6, Visits: 35
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
Posted Wednesday, August 19, 2009 12:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 346, Visits: 412
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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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 «««1234»»

Permissions Expand / Collapse