sp_send_dbmail “<”附近的语法不正确

Question

我在从SQL服务器发送HTML格式的电子邮件时遇到问题。

在以下代码部分中，我收到一个"Line 1, incorrect syntax near '<'"错误。

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>' ;

我似乎不能把它固定在什么特别的东西上？有什么想法吗？

谢谢

更新 1：

好的，我已经在调试会话中运行了代码并检查了@tableHTML的内容，内容看起来不错，并且填充了我的表中的预期数据。

这意味着错误来自其他地方，所以我这次复制了整个查询。

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 = '[e-mail]',
    @subject = 'Daily Project Tracking Report',
    @query = @tableHTML,
    @body_format = 'HTML';

再次感谢。

Answer 1

看起来您希望@tableHTML成为电子邮件的正文，但您将其作为@query传入，它必须包含有效的 SQL，因此出现错误。

请尝试改用@body：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'db_mail_account',
    @recipients = '[e-mail]',
    @subject = 'Daily Project Tracking Report',
    @body = @tableHTML,
    @body_format = 'HTML';