sql-server - 发送的电子邮件格式不正确

问题描述

我遇到的问题是当电子邮件发送给我时，发送到我的电子邮件的结果在 html 表中的格式不正确。

首先，这是我的疑问。第一个查询使用包含类型 M 和 C 的数据构建一个临时表。第二个查询构建另一个临时表并比较之前创建的临时表并检查数据是否包含类型 M 或 C。

use Letters

-- Build a temp table with the M and C Denial letters. We do not want to include these in our final list.

select distinct
LT.Description
into #ModCarvLetters
from dbo.AELetters_LetterTemplateText TT (nolock)
inner join dbo.AELetters_LetterTemplates LT (nolock) on 
TT.TemplateUniqueID = LT.TemplateUniqueID
inner join dbo.AELetters_LetterAssignment LA (nolock) on
LT.TemplateUniqueID = LA.TemplateUniqueID
inner join dbo.AELetters_LetterAssignmentDetail LAD (nolock) on
LA.LetterAssignmentID = LAD.LetterAssignmentID
where CurrentVersion = 1
and LT.TemplateType = 'Auto'
and LAD.LetterAssignment_lkupID = 3 --- Autst
and (LAD.MatchTableColumnValue =  'C' or LAD.MatchTableColumnValue =  'M')
and cast(LA.EffFrom as DATE) <= cast(GETDATE() as DATE)
and cast(LA.EffTo as DATE) >= cast(GETDATE() as DATE)
order by LT.Description

-- Get the language from the letter just before the denial reason.

select distinct
LT.Description,
substring(TT.TemplateText, charindex('Denial', TT.TemplateText)-500, 500) as [Denial_Reason]
into #DenialLetters
from dbo.AELetters_LetterTemplateText TT (nolock)
inner join dbo.AELetters_LetterTemplates LT (nolock) on 
TT.TemplateUniqueID = LT.TemplateUniqueID
inner join dbo.AELetters_LetterAssignment LA (nolock) on
LT.TemplateUniqueID = LA.TemplateUniqueID
inner join dbo.AELetters_LetterAssignmentDetail LAD (nolock) on
LA.LetterAssignmentID = LAD.LetterAssignmentID
where CurrentVersion = 1
and LT.TemplateType = 'Auto'
and TT.LanguageCodeID  = 65 -- English
--and LAD.LetterAssignment_lkupID = 2
and LAD.MatchTableColumnValue = 'Denied'
and cast(LA.EffFrom as DATE) <= cast(GETDATE() as DATE)
and cast(LA.EffTo as DATE) >= cast(GETDATE() as DATE)
and LT.Description not in (select M.Description from  #ModCarvLetters M)
order by LT.Description desc--, TT.TemplateText

drop  table #ModCarvLetters

以下是我通过电子邮件发送结果的地方。

DECLARE @CT AS INT
DECLARE @LETTER_DESCRIPTION AS VARCHAR (255)
DECLARE @DENIAL_REASON AS VARCHAR (MAX)
DECLARE @EMESSAGE AS VARCHAR (MAX)

SELECT @CT = COUNT(*) FROM  #DenialLetters

                        SET @EMESSAGE = '<head><style type="text/css">h2 {font-family: Arial, verdana;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>
                        <h2><font color="#0000ff" size="4">DENIAL LETTERS ON TEST</font></h2></head><table border="1"><tr><th>Letter Description</th><th>Denial Reason</th></tr>';
                        --<h2><font color="#0000ff" size="4">DENIAL LETTERS ON PRODUCTION</font></h2></head><table border="1">' + '<tr><th>Letter Description</th><th>Denial Reason</th></tr>';

                        WHILE @CT > 0

                        BEGIN

                              SELECT @LETTER_DESCRIPTION = Description, @DENIAL_REASON = Denial_Reason  FROM  #DenialLetters

                              SET @EMESSAGE = @EMESSAGE + 
                              '<tr><td>' + @LETTER_DESCRIPTION + '</td><td>' +  @DENIAL_REASON + '</td></tr>' ;

                              DELETE FROM  #DenialLetters WHERE (Description = @LETTER_DESCRIPTION and Denial_Reason = @DENIAL_REASON)

                              SELECT @CT = COUNT(*) FROM  #DenialLetters
                              --Print @CT
                              --Print @LETTER_DESCRIPTION
                              --Print  @DENIAL_REASON
                        END

                        SET @EMESSAGE = @EMESSAGE + '</table>'

                        exec msdb.dbo.sp_send_dbmail      
                             @recipients = 'mbruhn@regalmed.com',
                             @subject = 'Denial letters on REA Test',
                           --@subject = 'Denial letters on REA Production',  
                             @body = @EMESSAGE,
                             @body_format = 'HTML'              

Drop table  #DenialLetters

这是我收到电子邮件时显示的结果。我遇到的问题是表格外的文字应该在表格中。"!=""){%>" 不应出现。

标签： sql-servertsqlemail

sql-server - 发送的电子邮件格式不正确

问题描述

解决方案

推荐阅读