OUTPUT is not displaying more than ch3794, col 3794, Ln 35,

Question

i want to get all the records of the table but it is not displaying more than ch3794, col 3794, Ln 35

how to get the Query Output in HTML Format to send the report in Email?

    DECLARE @xmlthree NVARCHAR(MAX)
DECLARE @bodythree NVARCHAR(MAX)

SET @xmlthree = CAST(( SELECT 
[SerName] AS 'td','',
[IPAddress] AS 'td','',
[SName] AS 'td','', 
[Status] AS 'td','',
[TTaken] AS 'td','',
[MName] AS 'td','',
[TTime] AS 'td'
FROM  [dbo].[Error-details]
ORDER BY TTaken DESC  
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


SET @bodythree ='<html><body><H3>Web Report</H3>
<table border="1">
<tr>
<th> SerName </th> 
<th> IPAddress </th> 
<th> SName </th> 
<th> Status </th> 
<th> TTaken </th> 
<th> MName </th> 
<th> TTime </th> </tr>'    

SET @bodythree = @bodythree + @xmlthree +'</table></body></html>'
Print @bodythree
Go

i am getting the below output, some of the records are missing in the OUTPUT:- the red mark in the image at the end of the line in my problme

Please shorten your code & examples to only include the relevant part, instead of just dumping everything here, without even really asking anything — James Z, Mar 25 '17 at 07:03
Included what i need.! my question is some of the records are missing in the HTML Table output. please let me know if you need any — karhtik, Mar 25 '17 at 07:12
You should include what **we** need. A proper question with a short example of your issue. We do not need dump of your whole code and HTML. — James Z, Mar 25 '17 at 07:23
i did added the required fields with the screen shot. and shorten the code , is this enough for your need? — karhtik, Mar 25 '17 at 07:40
Did you ever calculate how many characters your output is? My guess is 4000. — James Z, Mar 25 '17 at 12:08
yes, i just calculated the characters it is more than 15,000 characters, and also it depends on the records in different tables. — karhtik, Mar 26 '17 at 00:16
Maybe your problem is not understanding English good enough... I didn't ask how much data you have. I asked how about the **output** length. — James Z, Mar 26 '17 at 04:48

score 0 · Answer 1 · edited May 23 '17 at 12:32

This is a probably just from the limitation of print, print is only returning the first 8 thousand characters for varchar and 4 thousand for nvarchar.

To print nvarchar strings shorter than 16,000 characters, you can use:

print cast(@bodythree as ntext);

For strings longer than that, you can use:

declare @String nvarchar(max)=@bodythree;
declare @CurrentEnd bigint, @offset int;
while len(@String) > 1
begin
  if charindex(char(10), @String) between 1 and 4000
  begin
    set @CurrentEnd =  charindex(char(10), @String) -1;
    set @offset = 2;
  end
  else
  begin
    set @CurrentEnd = 4000;
    set @offset = 1;
  end   
  print substring(@String, 1, @CurrentEnd);
  set @string = substring(@String, @CurrentEnd+@offset, len(@String));
end;

Combination of answers from here: How to print VARCHAR(MAX) using Print Statement?

For the second method to work with your situation, you need to add new lines to your body, like so:

declare @xmlthree nvarchar(max), @bodythree nvarchar(max);
set @xmlthree = replace(cast((
      select
          SerName as 'td',''
        , ipaddress as 'td',''
        , sname as 'td',''
        , [Status] as 'td',''
        , ttaken as 'td',''
        , mname as 'td',''
        , ttime as 'td'
      from  dbo.Error-details
      order by ttaken desc
      for xml path('tr'), elements ) as nvarchar(max))
    ,'</tr>','</tr>'+char(10))
set @bodythree ='<html><body><H3>Web Report</H3>
<table border="1">
<tr>
<th> SerName </th>
<th> ipaddress </th>
<th> sname </th>
<th> Status </th>
<th> ttaken </th>
<th> mname </th>
<th> ttime </th>
</tr>
'
set @bodythree = @bodythree + @xmlthree +'</table></body></html>';
declare @String nvarchar(max)=@bodythree;
declare @CurrentEnd bigint, @offset int;
while len(@String) > 1
begin
  if charindex(char(10), @String) between 1 and 4000
  begin
    set @CurrentEnd =  charindex(char(10), @String) -1;
    set @offset = 2;
  end
  else
  begin
    set @CurrentEnd = 4000;
    set @offset = 1;
  end
  print substring(@String, 1, @CurrentEnd);
  set @string = substring(@String, @CurrentEnd+@offset, len(@String));
end;

This works fine for me , but only thing is sometimes it breaks in HTML Tag so it displays some of the characters outside the table, i tried increasing the charindex but no luck. is there any other way to get ride of this. — karhtik, Mar 26 '17 at 06:09
@karhtik When you "send the report in Email" it should not be truncated. It is only truncated when previewing with `print`. — SqlZim, Mar 26 '17 at 12:00

score 0 · Answer 2 · answered Mar 25 '17 at 12:30

Instead of declaring and assigning values to variables try a direct select statement. Something like this

SELECT
    '<html><body><H3>Web Report</H3>
    <table border="1">
    <tr>
    <th> SerName </th> 
    <th> IPAddress </th> 
    <th> SName </th> 
    <th> Status </th> 
    <th> TTaken </th> 
    <th> MName </th> 
    <th> TTime </th> </tr>'+ISNULL(
    CAST(( SELECT 
    [SerName] AS 'td','',
    [IPAddress] AS 'td','',
    [SName] AS 'td','', 
    [Status] AS 'td','',
    [TTaken] AS 'td','',
    [MName] AS 'td','',
    [TTime] AS 'td'
    FROM  [dbo].[Error-details]
    ORDER BY TTaken DESC  
    FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)),'')
    +'</table></body></html>'

OUTPUT is not displaying more than ch3794, col 3794, Ln 35,

2 Answers2