Sending email using sp_send_dbmail() does not work sometimes

Question

I am sending notification email using SQL Server but sometimes emails aren't sent to users.

Here is my SQL table that I store emails which it will be sent to users

CREATE TABLE [dbo].[EmailNotification](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[EmailAdress] [NVARCHAR](50) NULL,
[EmailBody] [NVARCHAR](500) NULL,
[EmailSubject] [NVARCHAR](250) NULL,
[Attachment] [NVARCHAR](500) NULL,
[EmailSent] [BIT] NULL CONSTRAINT [DF_EmailNotification_EmailSent]  DEFAULT 
((0)),
[EmailCreateDate] [DATETIME] NULL CONSTRAINT 
[DF_EmailNotification_EmailCreateDate]  DEFAULT (GETDATE()),
[EmailSentDate] [DATETIME] NULL,
CONSTRAINT [PK_EmailNotification] PRIMARY KEY CLUSTERED 
([Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

And I have created a job which executes this procedure every 1 minute

CREATE PROCEDURE [dbo].[spSendEmail] 
AS
BEGIN
BEGIN TRAN
DECLARE @id BIGINT
DECLARE @max_id BIGINT
DECLARE @query NVARCHAR(1000)
DECLARE @EmailBody NVARCHAR(1000)
DECLARE @EmailAdress NVARCHAR(500)
DECLARE @EmailSubject NVARCHAR(500)
DECLARE @attachments NVARCHAR(1000)

if exists (SELECT * FROM dbo.EmailNotification where EmailSent=0)
begin 
SELECT @id=MIN(id) FROM dbo.EmailNotification where EmailSent=0


SELECT @EmailAdress=EmailAdress,@EmailBody=EmailBody,@EmailSubject=EmailSubject,@attachments=Attachment
FROM EmailNotification WHERE id = @id


exec [msdb].[dbo].[sp_send_dbmail] @profile_name='Notification',
@recipients=@EmailAdress,
@blind_copy_recipients='example.email.com',
@subject=@EmailSubject,
@body=@EmailBody,
@file_attachments=@attachments

end
IF(@@ERROR>0)
BEGIN
ROLLBACK
END
ELSE
BEGIN
UPDATE EmailNotification set EmailSent=1, EmailSentDate=getdate() WHERE Id=@id
COMMIT  
END

"Does not work". Do you mean they don't receive the email? First alter your code take note of the return value as mentioned below. Then check the SQL logs to see if there is any error mentioned — Nick.Mc, Sep 30 '17 at 11:41

Stéphane CLEMENT · Accepted Answer · 2017-09-30T12:50:51.507

2

What do you mean : spSendEmail is not triggering sp_send_dbmail? sp_send_dbmail is triggered but doesn't do anything....?

Please get the return code of sp_send_dbmail :
0 => OK
<> 0 => Error occured

DECLARE @result int;
DECLARE @ErrorNb int;
EXECUTE @result = exec [msdb].[dbo].[sp_send_dbmail] @profile_name='EDMS email notification',
@recipients=@EmailAdress,
@blind_copy_recipients='example.email.com',
@subject=@EmailSubject,
@body=@EmailBody,
@file_attachments=@attachments
SET @ErrorNb = @@ERROR    

IF @result <> 0
BEGIN
-- Something goes wrong
SELECT @result,@ErrorNb  
END

You can also use TRY :

BEGIN TRY
    EXECUTE exec [msdb].[dbo].[sp_send_dbmail] @profile_name='EDMS email notification',
    @recipients=@EmailAdress,
    @blind_copy_recipients='example.email.com',
    @subject=@EmailSubject,
    @body=@EmailBody,
    @file_attachments=@attachments
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE();
END CATCH

edited Sep 30 '17 at 12:50

answered Sep 30 '17 at 11:34

Stéphane CLEMENT

374
1
14

Thanks for your response clementakis.I will try what you offer – Nurlan Sep 30 '17 at 17:44
Have you found out you problem? Hope so ^^ – Stéphane CLEMENT Sep 30 '17 at 19:36
The problem was maximum attachment size exceeded sometimes:)) – Nurlan Sep 30 '17 at 19:50
Ok, this value can be changed... Good night ^^ – Stéphane CLEMENT Sep 30 '17 at 20:06
Hi, clementakis.Again I have a problem sending e-mail. When I see view history of the job there was some errors stating error message Id 22051 Attachment error.But I execute query manually it worked and then job started working correctly – Nurlan Oct 02 '17 at 13:04
SQL Server services must have rights to access the folder where you have put the attachment files... Note that the services must have right for the full path to the file, and to the file itself. – Stéphane CLEMENT Oct 02 '17 at 16:56
Use SELECT * FROM sys.dm_server_services to know what user is used for services. then you have to see what group this user is a part of... And then look at the windows right of each subdirectoris in the path of the file. – Stéphane CLEMENT Oct 02 '17 at 19:42
Hi, clementakis. I try your select statement it showed me service account is .\sqldb and I am going to add this user to the folders and subfolders which I want to attach file from them but windows does not let me select this user – Nurlan Oct 03 '17 at 07:51
Windows is out of my knowledge scope : sorry. Maybe you could ask another question on this sepcifc problem... – Stéphane CLEMENT Oct 03 '17 at 09:42

Sending email using sp_send_dbmail() does not work sometimes

1 Answers1