Click here to monitor SSC
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
vikramjits
vikramjits
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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...:-D
Sam Bendayan
Sam Bendayan
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
aschoch
aschoch
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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, '' + column + '') 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, '*![CDATA[' + column + ']]*') AS ColumnXML FROM TABLE



Replace the asterisks with open and close tags, and you're good.
thakur_samir
thakur_samir
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
mayank jarsaniya
mayank jarsaniya
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 427
I think another solution...................

select @sql

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

It will gives all characters in single line............................
Steve McRoberts-357330
Steve McRoberts-357330
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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.
Mike Vassalotti
Mike Vassalotti
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
mayank jarsaniya
mayank jarsaniya
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 427
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.........
Sam Bendayan
Sam Bendayan
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
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
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