时间戳

首页 > 解决方案 > 从 sql 发送邮件

sql-server - 从 sql 发送邮件

问题描述

Sql 执行结果但邮件未发送，错误是必须指定收件人。

我声明了收件人列表并将多封邮件添加到收件人列表中，其中执行了查询并显示了多个收件人邮件。

BEGIN 
 Use [register ] 
 DECLARE Receipientlist varchar(8000) 
 SET ReceipientList = STUFF(
     ( 
       SELECT ';' + Manager_Login_Id 
       FROM dbo.Login 
       FOR XML PATH('')
     ),1,1,'') 

 SELECT Idea_Id, Login_Id, Manager_Login_Id, Idea_Date_Of_Submission, Idea_Status ='PENDING' 
 FROM dbo.Idea 
 WHERE Manager_Login_Id IS NOT NULL 
   and Idea_Date_Of_Submission <= DATEADD(day, -5, GETDATE()); 

 EXEC msdb.dbo.sp_send_dbmail profile_name = ' ', recipients = Receipientlist, body =' ',subject=' ', 

 END

标签： sql-serveremailjobs

解决方案

您拥有的那个 SQL（请 LukStorms 格式化）不完整，它不会运行。您的语句末尾有一个“任性”逗号：

EXEC msdb.dbo.sp_send_dbmail profile_name = ' ', recipients = Receipientlist, body =' ',subject=' ', <-- What is this comma here for?

但是，除了随意的逗号之外，您的 SQL 不起作用的原因是您的参数和变量的语法也是错误的。两者的名字都以@. 因此，正确的语法是：

USE [register ] 
DECLARE @Recipientlist varchar(8000); --Variables begin with a @. Also changed the name to the correct spelling
SET @Recipientlist = STUFF((SELECT ';' + Manager_Login_Id 
                            FROM dbo.[Login] --Login is a reserved word, i suggest using a different name for your table.
                            FOR XML PATH('')),1,1,'');


SELECT Idea_Id,
       Login_Id,
       Manager_Login_Id,
       Idea_Date_Of_Submission,
       Idea_Status AS PENDING
FROM dbo.Idea 
WHERE Manager_Login_Id IS NOT NULL 
  AND Idea_Date_Of_Submission <= DATEADD(day, -5, GETDATE());  

 EXEC msdb.dbo.sp_send_dbmail @profile_name = ' ',
                              @recipients = @Recipientlist,
                              @body =' ',--Why are you sending an empty body?
                              @subject=' ';--, Removed the comma, although I suggest having a subject too.

推荐阅读