Help get the HTML to columns

Question

0.00/5 (No votes)

See more:

IF EXISTS
(
    SELECT convert(varchar, [LastUpdate],20),
           convert(varchar, [LastS48Run], 20),
           CASE
               WHEN DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
                    AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1 THEN
                   'Auto PDF Service is Hanging'
               ELSE
                   'Normal'
           END AS [AutoPDF_Status],
           CASE
               WHEN DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
                    AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1 THEN
                   'Restart AutoPDF Service on AL-DR-CORRESP Server'
               ELSE
                   'Service is running'
           END AS [Action_Plan]
    FROM [dbo].[LG_ServiceStatus]
    WHERE DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
          AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1
)
BEGIN
    DECLARE @tableHTML NVARCHAR(MAX);
    SET @tableHTML
        = N'<h1>AutoPdf Status</h1>' 
        + N'<table border="1">' 
        + N'<th>Last Update</th><th>Last S48 Run</th><th>AutoPDF Status</th><th>Action Plan</th><' 
        + CAST(
            (
                SELECT 
                     N'<td>' + convert(varchar, [LastUpdate], 20) + N'</td>',
                    + N'<td>' + convert(varchar, [LastS48Run], 20) + N'</td>',
                    + N'<td>' + 
                        CASE
                            WHEN DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
                                 AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1 THEN
                                'Auto PDF Service is Hanging'
                            ELSE
                                'Normal'
                        END + N'</td>'
                    + N'<td>' + 
                        CASE
                            WHEN DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
                                 AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1 THEN
                                'Restart AutoPDF Service on AL-DR-CORRESP Server'
                            ELSE
                                'Service is running'
                        END +  N'</td>' 
    
                WHERE DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
                      AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1
                FOR XML PATH (''), TYPE
            ) AS NVARCHAR(MAX)
          ) 
        + N'</table>';
    
    EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'AIMS',
         @copy_recipients = 'marks.co.za',
         @subject = 'AutoPDF is down',
         @body = @tableHTML,
         @body_format = 'HTML';
END;

What I have tried:

I need to get this into Columns for the email can you help

Posted 23hrs ago

Mark Smith 2024

Add a Solution

2 solutions

Solution 1

NEVER put UI formatting code in your SQL. It makes it so your SQL can only ever be used with one application type, in this case, a web page.

If you wanted your SQL to be used for another app type, like a web service or a mobile app, you're screwed.

UI formatting goes in the apps UI layer, not in your SQL.

Posted 22hrs ago

Dave Kreskowiak

Comments

Mark Smith 2024 22hrs ago

This is just a notification email

Dave Kreskowiak 21hrs ago

Just as bad. SQL is for database operations, not sending emails. You're combining concerns in a layer that should not care about emails. It should care about the database, that's it.

You're also finding out that doing it in the database makes it so much more difficult to debug what's wrong.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2024-10-15T04:26:00

Well, start by generating valid HTML. You've got a stray <, and you've added <th> and <td> elements as direct children of the <table>, when they need to be nested in <tr> elements, preferably within the sematically-correct <thead> and <tbody> elements.

SQL

SET @tableHTML
    = N'<h1>AutoPdf Status</h1>' 
    + N'<table border="1">' 
    + N'<thead><tr><th>Last Update</th><th>Last S48 Run</th><th>AutoPDF Status</th><th>Action Plan</th></tr></thead>' 
    + N'<tbody>'
    + (
        SELECT 
            N'<tr>'
            + N'<td>' + convert(varchar, [LastUpdate], 20) + N'</td>',
            + N'<td>' + convert(varchar, [LastS48Run], 20) + N'</td>',
            + N'<td>' + CASE
                WHEN DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
                     AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1 THEN 'Auto PDF Service is Hanging'
                ELSE 'Normal'
            END + N'</td>'
            + N'<td>' + CASE
                WHEN DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
                    AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1 THEN 'Restart AutoPDF Service on AL-DR-CORRESP Server'
                ELSE 'Service is running'
            END +  N'</td>' 
            + N'</tr>'
        WHERE DATEDIFF(MINUTE, [LastUpdate], GETDATE()) > 1
        AND DATEDIFF(MINUTE, [LastS48Run], GETDATE()) > 1
        FOR XML PATH (''), TYPE
    ).value('.', 'nvarchar(max)')
    + N'</tbody>'
    + N'</table>';

If it still doesn't work, then you need to explain precisely what the problem is.