none
sp_send_dbmail issues

    질문

  • I seem to be having an issue with sending DB mail. 

    I have successfully set up my profile and account, and the test email was received.

    However for some reason I can not get any mail to send using sp_send_dbmail. It probably comes down to the fact I may have overcomplicated some of the email content creation, so that staff can edit the email templates once I had everything set up. Because of this, I'm going to explain the full process so I don't miss anything that may be causing the issue. 

    The system is a recruitment/new starter tracking system. The first email I am trying to set up is a contract request email. 

    Because I wanted the staff to be able to edit the email templates in a similar way to a mail merge uses merge fields, I started off by creating a lookup table which listed the fields and their corresponding "tags" to be used in the template. 

    Next I created an email template table, and a form in access (where the front end of the system is built) to easily be able to view/edit the templates.

    Next, I created a view which contained all of the information needed to generate the email's content from different tables, which only shows the records where a user has set a field called SendContractRequestEmail to "Yes". On the table where this field is located, I have also placed the following trigger:

    USE [IMHRS]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <vinnyclegg>
    -- Create date: <01/05/2018>
    -- Description:	<Description,,>
    -- =============================================
    ALTER Trigger [dbo].[Add_Contract_Request_to_Email_Send]
    ON  [dbo].[Onboarding] 
    AFTER Update
    AS 
    BEGIN
    
    SET NOCOUNT ON;
    
    
    DECLARE @MasterID Nvarchar(50),
    	@EmailTemplateName Nvarchar(50),
    	@EmailTemplateContent Nvarchar(500),
    	@EmailTo  Nvarchar(50),
    	@EmailFrom  Nvarchar(50),
    	@EmailCC Nvarchar(50),
    	@First_Name Nvarchar(50),
    	@Last_Name Nvarchar(50),
    	@Region Nvarchar(50),
    	@Practice_Name Nvarchar(50),
    	@Role Nvarchar(50),
    	@Hours Nvarchar(50),
    	@Salary Nvarchar(50),
    	@AdditionalMessage Nvarchar(250),
    	@EmailSubject Nvarchar(100),
    	@Run Int
    
    
    set @MasterID =			(Select Master_ID from inserted)
    set @First_Name =		(Select FirstName From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @Last_Name =		(Select LastName From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @Region =			(Select Region From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @Practice_Name=		(Select [Name] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @Role =			(Select [Role] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @Hours =			(Select [Hours] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @Salary =			(Select [FTSalary] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @EmailTemplateName =	(Select [EmailTemplateName] From IMHRS.DBO.EmailTemplates where EmailTemplates.EmailTemplateID = 1)
    set @EmailTemplateContent =	(Select [EmailTemplateContent] From IMHRS.DBO.EmailTemplates where EmailTemplates.EmailTemplateID = 1)
    set @EmailTo =			(Select [HRAdmin_Email] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @EmailFrom =		(Select [RP_Email] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @EmailCC =			(Select [ContractRequestEmailCC] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @AdditionalMessage =	(Select [ContractRequestEmailMessage] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    set @Run =			(SELECT COUNT(Master_ID) AS Count FROM V_Contract_Request_Email_Content_Gather)
    Set @EmailSubject =		(Select [EmailTemplateSubject] From IMHRS.DBO.V_Contract_Request_Email_Content_Gather where V_Contract_Request_Email_Content_Gather.Master_ID = (Select Master_ID from inserted))
    
    
    
    BEGIN
    SET NOCOUNT ON;
    
    IF	@Run = 0 GOTO Finish
    If	@Run > 0 GOTO StartEmail
    
    StartEmail:
    INSERT INTO [dbo].[EmailSend]
     
    ([MasterID], [EmailTo], [EmailFrom], [EmailCC], [EmailSubject], [EmailTemplateContent]) Select @MasterID, @EmailTo, @EmailFrom, @EmailCC, @EmailSubject, @EmailTemplateContent; --EMAIL Subject
    UPDATE [dbo].[EmailSend] SET [EmailSubject] = REPLACE([EmailSubject], '@First_Name', @First_Name) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailSubject] = REPLACE([EmailSubject], '@Last_Name', @Last_Name) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailSubject] = REPLACE([EmailSubject], '@Practice_Name', @Practice_Name) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailSubject] = REPLACE([EmailSubject], '@Role', @Role) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailSubject] = REPLACE([EmailSubject], '@Hours', @Hours) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' --EMAIL BODY UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@First_Name', @First_Name) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@Last_Name', @Last_Name) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@Region', @Region) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@Practice_Name', @Practice_Name) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@Role', @Role) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@Hours', @Hours) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@Salary', @Salary) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' UPDATE [dbo].[EmailSend] SET [EmailTemplateContent] = REPLACE([EmailTemplateContent], '@AdditionalMessage', @AdditionalMessage) WHERE [dbo].[EmailSend].[MasterID] = @MasterID and [dbo].[EmailSend].[Sent] = 'No' -- RESET EMAIL TRIGGER FIELD UPDATE [dbo].[Onboarding] SET [SendContractRequestEmail] = 'No' WHERE [dbo].[Onboarding].[Master_ID] = @MasterID and[dbo].[Onboarding].[SendContractRequestEmail] = 'Yes' GOTO Finish Finish: END END

    At this stage, I created a view in Access to check the email was created successfully with the variable content inserted.

    I then have the following Stored Procedure (which I currently execute manually for testing purposes) to send the actual email.

    USE [IMHRS]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    -- =============================================
    -- Author:	VinnyClegg
    -- Create date: 01/05/2018
    -- Description:	
    -- =============================================
    ALTER PROCEDURE [dbo].[ALLMailSEND]
    AS
    BEGIN
    SET NOCOUNT ON;
    
    
    --Send Email
    
    Start:
    
    Declare         @EmailID int,
    		@Continue int,
    		@To Nvarchar(50),
    		@from Nvarchar(50),
    		@CC Nvarchar(100),
    		@Sub Nvarchar(100),
    		@Content Nvarchar(100),
    		@Sent Nvarchar(50)
    
    
    set @Continue =	(SELECT COUNT(EmailID) AS Count FROM [IMHRS].[dbo].[EmailSend] where [Sent] = 'No')
    IF	@Continue = 0 GOTO Finish
    
    Else	Set @EmailID =	(Select TOP(1)EmailID From [IMHRS].[dbo].EmailSend where [Sent] = 'No')
    	Set @from =	(Select EmailFrom From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    	Set @to =	(Select EmailTo From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    	Set @CC =	(Select EmailCC From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    	Set @Sub =	(Select EmailSubject From [IMHRS].[dbo].EmailSend where (EmailSend.EmailID = @EmailID))
    	Set @Content =	(Select EmailTemplateContent From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    		
    EXEC	msdb.dbo.sp_send_dbmail  
    		@profile_name		= 'IMHRS',
    		@from_address		= @from,
    		@recipients		= @to,
    		@copy_recipients	= @CC,
    		@subject		= @Sub,
    		@body 			= @Content,
    		@body_format		= 'HTML';
    		
    
    
    Update [IMHRS].[dbo].[EmailSend]
    Set [Sent] = 'Yes' where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID) and ([IMHRS].[dbo].EmailSend.[Sent] = 'No')
    
    
    GOTO Start
    
    
    Finish:
    END
    


    At this point, I would expect the email to send, but it doesn't. 

    I'm not claiming to be an expert at SQL (pretty must self taught, and learnt how to do things on a need to learn basis), so I apologise if someone points out a rooky mistake. 

    I hope you can help. 

    2018년 5월 16일 수요일 오전 9:32

모든 응답

  • Hi VinnyClegg,

    Please try this procedure and see if it sends email normally.

    USE [IMHRS]
    GO
    
    ALTER PROCEDURE [dbo].[ALLMailSEND]
    AS
    BEGIN
    --SET NOCOUNT ON;
    --Send Email
    
    --Start:
    
    Declare @EmailID int,
    		@Continue int,
    		@To Nvarchar(50),
    		@from Nvarchar(50),
    		@CC Nvarchar(100),
    		@Sub Nvarchar(100),
    		@Content Nvarchar(100),
    		@Sent Nvarchar(50)
    
    
    --set @Continue =	(SELECT COUNT(EmailID) AS Count FROM [IMHRS].[dbo].[EmailSend] where [Sent] = 'No')
     SELECT @Continue=COUNT(EmailID)  FROM [IMHRS].[dbo].[EmailSend] where [Sent] = 'No'
    --IF	@Continue = 0 GOTO Finish
      IF @Continue>0 
      BEGIN
    
        declare db_cursor cursor for 
        select EmailID,EmailFrom,EmailTo,EmailCC,EmailSubject,EmailTemplateContent from [IMHRS].[dbo].EmailSend WHERE [Sent] = 'No'
    	open db_cursor
    
        FETCH NEXT FROM db_cursor INTO @EmailID,@from,@to,@CC,@Sub,@Content
    	WHILE @@FETCH_STATUS = 0
        begin
    
    --Else	Set @EmailID =	(Select TOP(1)EmailID From [IMHRS].[dbo].EmailSend where [Sent] = 'No')
    --	Set @from =	(Select EmailFrom From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    --	Set @to =	(Select EmailTo From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    --	Set @CC =	(Select EmailCC From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    --	Set @Sub =	(Select EmailSubject From [IMHRS].[dbo].EmailSend where (EmailSend.EmailID = @EmailID))
    --	Set @Content =	(Select EmailTemplateContent From [IMHRS].[dbo].EmailSend where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID))
    		
           EXEC	msdb.dbo.sp_send_dbmail  
    		@profile_name		= 'IMHRS',
    		@from_address		= @from,
    		@recipients		= @to,
    		@copy_recipients	= @CC,
    		@subject		= @Sub,
    		@body 			= @Content,
    		@body_format		= 'HTML';
    
         FETCH NEXT FROM db_cursor INTO @EmailID,@from,@to,@CC,@Sub,@Content
    	 end
    	 CLOSE db_cursor   
        DEALLOCATE db_cursor
    		
    --   Update [IMHRS].[dbo].[EmailSend]
    --Set [Sent] = 'Yes' where ([IMHRS].[dbo].EmailSend.EmailID = @EmailID) and ([IMHRS].[dbo].EmailSend.[Sent] = 'No')
    
       Update [IMHRS].[dbo].[EmailSend]
       Set [Sent] = 'Yes' where [IMHRS].[dbo].EmailSend.[Sent] = 'No'
    
    
    --GOTO Start
       END
    --Finish:
    END
    
    EXEC [dbo].[ALLMailSEND]

    Reference: Database Mail

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 17일 목요일 오전 7:50
  • When you call the Email sending procedure are you getting any error?

    Also where's your SMTP server setup? Is it on premise like MS Exchange or are you using a cloud provider like gmail?

    Is the mail getting queued at least?

    Also check database mail log for any errors warnings as below


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 17일 목요일 오전 8:41
  • I'm on office 365. As I mentioned, the test email is sending fine, just not the actual emails.

    There is no error on the execution, I get:

    Mail (Id: 40287) queued.

    (1 row affected)

    • 편집됨 VinnyClegg 2018년 5월 18일 금요일 오전 8:13
    2018년 5월 18일 금요일 오전 8:08
  • Have you tried setting up a local relay server in IIS for O365 ?

    see

    https://support.office.com/en-us/article/how-to-configure-iis-for-relay-with-office-365-eb57abd2-3859-4e79-b721-2ed1f0f579c9


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 9:05