sp_send_dbmail Incorrect syntax near '<'

Question

I'm having a problem sending a HTML formatted email from SQL server.

With the following section of code I get a "Line 1, incorrect syntax near '<'" error.

SET @tableHTML =
    '<H1>Progress Report</H1>' +
    '<table border="1">' +
    '<tr>' +
                '<th>Project Name</th>' +
                '<th>Platform</th>' +
                '<th>Due By</th>' +
                '<th>Current Status</th>' +
                '<th>Current State</th>' +
    '</tr>' +
    CAST (  
                ( 
                        SELECT
                                td = [Project Name],    ' ',
                                td = Platform,  ' ',
                                td = [Due By],  ' ',
                                td = [Current Status],  ' ',
                                td = [Current State],   ' '
    FROM [dbo].[table_name]
    ORDER BY [Current Status] DESC
    FOR XML PATH('tr'), TYPE
              ) AS NVARCHAR(MAX) ) +
    '</table>' ;

I cant seem to pin it to anything in particular? Any Idea's?

Thanks

Update 1:

Ok I've run the code in a debug session and have checked the contents of @tableHTML, the contents looks fine and it gets populated with the expected data from my Table.

Meaning the errors coming in from somewhere else, so I've copied the whole query this time.

DECLARE @tableHTML NVARCHAR(MAX);

SET @tableHTML =
    '<h1>Progress Report</h1>' +
    '<table border="1">' +
    '<tr>' +
                '<th>Project Name</th>' +
                '<th>Platform</th>' +
                '<th>Due By</th>' +
                '<th>Current Status</th>' +
                '<th>Current State</th>' +
    '</tr>' +
    CAST 
        (   
                ( 
                        SELECT
                                td = [Project Name],    '',
                                td = Platform,  '',
                                td = [Due By],  '',
                                td = [Current Status],  '',
                                td = [Current State],   ''
    FROM [dbo].[table_name]
    ORDER BY [Current Status] DESC
    FOR XML PATH('tr'), TYPE
              ) AS NVARCHAR(MAX) ) +
    '</table>';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'db_mail_account',
    @recipients = 'example@example.com',
    @subject = 'Daily Project Tracking Report',
    @query = @tableHTML,
    @body_format = 'HTML';

Thanks again.

Answer 1

It looks like you want @tableHTML to be the body of the email, but you're passing it in as @query, which has to contain valid SQL, hence the error.

Try using @body instead:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'db_mail_account',
    @recipients = 'example@example.com',
    @subject = 'Daily Project Tracking Report',
    @body = @tableHTML,
    @body_format = 'HTML';