HTML ISSUE

Question

Post reply

HTML ISSUE

wweraw25

SSC Eights!

Points: 953
More actions
July 18, 2017 at 8:03 pm

#402325

SET @XML =  N'Hello;<br/><br/> On the server, sample queries:<br/>'+
N'<table style="border-collapse:collapse;font-size:10pt;white-space:nowrap;" border="1" cellpadding="10">' +
N'<tr bgcolor="#D6D9E0"><td>SPID</td><td>BlockedBy</td><td>Database</td><td>User</td><td>Status</td>' +
N'<td>Wait</td><td>CurrentStmt</td><td>CurrentBatch</td><td>Reads</td><td>Logical_Reads</td><td>CPU</td><td>TimeElapsedinMin</td>' +
N'<td>Program</td><td>HostName</td><td>Start_Time</td></tr>'+
    (SELECT top 1
         '<td valign="top">' + isnull(cast([spid] as varchar),space(1)) + '</td>' +
              '<td valign="top">' + isnull(cast([blockedBy] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([database] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast( as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([status] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([wait] as varchar),space(1)) + '</td>' +
              '<td valign="top">' + isnull(cast([current stmt] as varchar),space(1)) + '</td>' +
        -- '<td valign="top">' + isnull(cast(
        --      [SUBSTRING (
        --             qt.text,
        --       er.statement_start_offset/2,
        --             (CASE
        --                   WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)
        --                   ELSE er.statement_end_offset
        --             END - er.statement_start_offset)/2)] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([current batch] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([reads] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([logical_reads] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([cpu] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([time elapsed (min)] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([program] as varchar),space(1)) + '</td>' +
         '<td valign="top">' + isnull(cast([hostname] as varchar),space(1)) + '</td>' +
       --,nt_domain
         '<td valign="top">' + isnull(cast(start_time as varchar),space(1)) + '</td>'
              +'</tr>' + '</table>'
FROM #temp
)
how can I get multiple values in the above result set. Script fails for multiple values.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Lowell posted some code that output a dataset as an HTML table not too long ago. Look around for it.

Thom A SSC Guru Points: 99130 More actions · Answer 2

Microsoft docs also covers how to do this as well, on sp_send_dbmail (Transact-SQL). Have a look for Example C; "Sending an HTML e-mail message".

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk