HTML stored in a varchar max column being truncated, how do I return it?

Question

Post reply

HTML stored in a varchar max column being truncated, how do I return it?

ifthenelsenullif

Mr or Mrs. 500

Points: 552
More actions
January 23, 2015 at 9:21 pm

#314059

SQL Server 2008r2. This is a vendor database passing to a crystal report so I can't change the underlying data.
The vendor is storing HTML from an editor in an application and it is rather long. In this case it's 199869 characters. However there's no reason it can't be much longer.
SQL has been truncating the column to 43679 characters.
I tried
convert(xml,'<xml><![CDATA[' + cast(table.column as varchar(max)) + ']]></xml>') AS fulltextreturn ,
That did return all the values but I am no longer able to use crystal to convert the HTML into formatted code.
I decided to try
substring(table.column,1,30000) as stringtext1
substring(table.column,30001,60000) as stringtext2
Then continued that for a number of repetitions. I then concatenated the strings together in crystal to form a giant string so I can display it and format it as HTML again.
I'm sure there's a better way but I'm not sure of what it is. Any help you can provide would be greatly appreciated.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 1

Sorry... post withdrawn. Let me look at your post again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 2

There is a difference between what you can see on the screen from SSMS and what is actually in a column. You've already seen that. How do you know that Crystal isn't getting the whole string?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ifthenelsenullif Mr or Mrs. 500 Points: 552 More actions · Answer 3

Because in Crystal I can see that it's missing data. It's basically a series of web pages and it ends about 1/3 of the way through. When I use the XML I can see that the string is complete but I can't format it as HTML using crystal.

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 4

Ugh! Understood.

Are you, by any chance, defining a tag as <a>, <col>, or <del>somewhere in the mix? Or maybe there's an "unclosed" tag somewhere? Other than that, I'm coming up short on this. And your network doesn't have a message length limit, does it? I've never seen anyone do that on purpose but you never know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

try this, just to see:

SELECT table.column AS [processing-instruction(x)] FOR XML PATH('')

the output of that should be like this - where {your data} is your html string.

<?x {your data}?>

In your report, you should be able to take a substring of that to remove the first 4 and last 2 characters, leaving your original html.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Eirikur Eiriksson SSC Guru Points: 182884 More actions · Answer 6

Quick thought, seen this behaviour occasionally on SQL Server 2008, caused by an implicit cast or at least adding the N'' has fixed the problem.

😎

Try changing

convert(xml,'<xml><![CDATA[' + cast(TABLE.COLUMN as varchar(max)) + ']]></xml>',3) AS fulltextreturn

to

convert(xml,N'<xml><![CDATA[' + cast(TABLE.COLUMN as varchar(max)) + N']]></xml>',3) AS fulltextreturn

The most common cause for string truncation is the use of 8000/N4000 character limited functions, make certain that's not the case.

Here is a quick code to test if there is any difference between the two (implicit conversion and no conversion)

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_LONG_TEXT') IS NOT NULL DROP TABLE dbo.TBL_LONG_TEXT;

CREATE TABLE dbo.TBL_LONG_TEXT

(

LT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,LT_TXT NVARCHAR(MAX) NOT NULL

);

DECLARE @SEED NVARCHAR(MAX) = REPLICATE(N'ABCDEFGHIJ1234567890',400);

DECLARE @LSTR NVARCHAR(MAX) = N'<ROOT>'

+ @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED

+ @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + @SEED + N'</ROOT>'

;

INSERT INTO dbo.TBL_LONG_TEXT(LT_TXT) VALUES (@LSTR)

SELECT

convert(xml,N'<xml><![CDATA[' + cast(L.LT_TXT as varchar(max)) + N']]></xml>',3) AS fulltextreturnN

,convert(xml,'<xml><![CDATA[' + cast(L.LT_TXT as varchar(max)) + ']]></xml>',3) AS fulltextreturnA

FROM dbo.TBL_LONG_TEXT L;

Alternatively you can post an example (anonymized) and we can try to replicate the behaviour.

ifthenelsenullif Mr or Mrs. 500 Points: 552 More actions · Answer 7

I kept running into string size limits with crystal so what I ended up using was the substring where I broke it into 30000 characters at a time and will just deal with the occasional unclosed html tag. I am going to try the posts here after I get this working and see if the xml conversion ends up being a more efficient way of handling it for the future. I certainly appreciate the help and will update in a day or so when I have time to fully test the options. Thanks again for your help.