SQL server Database Mail sendet leere Nachrichten, wenn ich ihm einen HTML-Körper gebe

Ich versuche, die SQL server Management Studio Database Mail zu verwenden, um automatisierte HTML-formatierte E-Mails zu senden. Es funktioniert gut, wenn ich ihm einen Klartext-Körper gebe, aber sobald ich es in HTML ändere, sind die Nachrichten leer. Sie senden immer mit dem richtigen Thema und alles andere, aber der Körper ist nicht da.

Hier ist das relevante Code:

DECLARE @msg varchar(8000); --body of the message in html. DECLARE @recipient_emails varchar(50); --List of email addresses for everyone who needs to receive this message. DECLARE @subject_line varchar(100); --Subject line for the email. DECLARE @num_of_msgs int; --Since some updates need to trigger multiple different emails, this is used to determine how many emails will be sent. DECLARE @creator varchar(10); --Creator of the opportunity in CRM. DECLARE @account_id int; --Account number for customer. DECLARE @customer varchar(50); --Customer from whom the opportunity came. DECLARE @opty_type varchar(30); --Type of opportunity. DECLARE @opty_desc varchar(40); --Description of opportunity. DECLARE @date_created datetime; --Datetime when opportunity was created. DECLARE @notes varchar(500); --Notes about opportunity. SELECT @creator = CREATOR, @account_id = ACCT_ID, @opty_type = OPTY_TYPE_ID, @opty_desc = DESCRIPTION, @date_created = OPEN_DATE, @notes = COMMENTS FROM V_OPPORTUNITY WHERE ID = @opty_id; --Uses a CASE statement to determine recipient emails based on what activity has just been completed. SET @recipient_emails = CASE @description WHEN 'Create Opportunity in CRM' THEN 'nate.lovell@aggiemail.usu.edu' WHEN 'Request NDA' THEN 'Email 2' WHEN 'Receive Formula' THEN 'Email 3' WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 'Email 4' WHEN 'Issue Ballpark Quote' THEN 'Email 5' WHEN 'Credit Check' THEN 'Email 6' ELSE '' END; --Uses a CASE statement to determine HTML message based on what activity has just been completed. SET @msg = CASE @description WHEN 'Create Opportunity in CRM' THEN '<!doctype html> <html> <head> <meta charset="utf-8"> <title>DLI NEW CRM OPTY EMAIL</title> <style type="text/css"> body { margin-left: 10px; margin-top: 10px; margin-right: 10px; margin-bottom: 10px; text-align: center; background-color: #61FF51; } div#PAGECENTER { width: 1000px; margin-left: auto; margin-right: auto } </style> </head> <body> <table width="100%" border="2" cellspacing="5" cellpadding="5"> <tbody> <tr> <td colspan="4" align="center" valign="middle" style="font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace; font-weight: bold; font-size: 36px;"><p><!--<img src="/Images/DESLABLGtag3d.png" width="52" height="53" alt=""/>--><span style="font-size: 24px"> NEW OPTY CREATED IN CRM</span></p></td> </tr> <tr> <td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">CREATED BY</td> <td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+@creator+'</td> <td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">CUSTOMER</td> <td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">SQL HERE</td> </tr> <tr> <td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">OPTY TYPE</td> <td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+@opty_type+'</td> <td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">DESCRIPTION</td> <td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+@opty_desc+'</td> </tr> <tr> <td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">DATE CREATED</td> <td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+CAST(@date_created AS varchar(20))+'</span></td> </tr> <tr> <td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">NOTES</td> <td height="40" colspan="3" align="left" valign="middle" bgcolor="#E5E5E5" style="font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace">'+ISNULL(@notes,'')+'</span></td> </tr> </tbody> </table> <div id="PAGECENTER"></div> </body> </html>' WHEN 'Request NDA' THEN 'Msg 2' WHEN 'Receive Formula' THEN 'Msg 3' WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 'Msg 4' WHEN 'Issue Ballpark Quote' THEN 'Msg 5' WHEN 'Credit Check' THEN 'Msg 6' ELSE 'DEFAULT MSG' END; --Uses a CASE statement to determine subject line based on what activity has just been completed. SET @subject_line = CASE @description WHEN 'Create Opportunity in CRM' THEN 'Subject 1' WHEN 'Request NDA' THEN 'Subject 2' WHEN 'Receive Formula' THEN 'Subject 3' WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 'Subject 4' WHEN 'Issue Ballpark Quote' THEN 'Subject 5' WHEN 'Credit Check' THEN 'Subject 6' ELSE 'DEFAULT SUBJECT' END; --Uses a CASE statement to determine number of messages based on what activity has just been completed. SET @num_of_msgs = CASE @description WHEN 'Create Opportunity in CRM' THEN 2 WHEN 'Request NDA' THEN 1 WHEN 'Receive Formula' THEN 1 WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 1 WHEN 'Issue Ballpark Quote' THEN 1 WHEN 'Credit Check' THEN 1 ELSE 1 END; --Sends an email. EXEC msdb.dbo.sp_send_dbmail @recipients = @recipient_emails, @body = @msg, @body_format = 'HTML', @subject = @subject_line, @profile_name = 'SQLProfile'; 

HINWEISE: Der einzige Fall, den ich mir Sorgen mache, dass ich jetzt zur Arbeit bin, ist derjenige für 'Opportunity im CRM schaffen'. Auch hier werden mehrere variables, die hier verwendet werden, deklariert und / oder früher in den Code gesetzt, wie @opty_id und @description.

Das ist zu lange für einen Kommentar.

Die wahrscheinlichste Ursache für das Versagen ist, dass es keine Übereinstimmung zu den Fallaussagen gibt. Sie sollten eine else Klausel in allen CASE statementen haben, um sicherzustellen, dass sie verarbeitet werden, wie Sie beabsichtigen.

Eine sehr enge Sekunde ist, dass eine oder mehrere der Spalten, die für die Konstruktion von @msg werden, NULL . Dies führt dazu, dass der gesamte Wert NULL .

Zum Beispiel, wenn es keine Kommentare gibt, dann @Notes könnte NULL und die ganze @msg wird am Ende wird NULL .

Sie sollten hinzufügen:

 SELECT @creator, @account_id, @opty_type, @opty_desc, @date_created, @notes 

Um sicher zu sein, dass keiner dieser Werte NULL .

Sie können gegen NULL Werte schützen, wenn @msg mit COALESCE() .