SQLServer Agent not sending dbmail on job completion
-
Hi Everyone!
I have a problem with a SQL Server 2017 with dbmail. I have the attached script and when I run it manually it works ok and sends the mail, and it also registers the activity on sysmail_event_log. But when I run it with a job, the step works fine but no mail is sent and no log can be found in sysmail_event_log.
My SQL Server is the following:
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Any ideas on how to fix this?
Thanks!!!
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Dunno for sure but my first guess is... is the user for the SQL Agent Service setup as an email user on your email system?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi!
Sql Server Agent is running with the default user as seen below.
I tried removing the .csv attachment from the query above and the mail is sent normally... So, it seems the thing gets broken when I attach a csv file from a view!
No clue on how to solve it though!
-
Hello,
Not sure if these links can address your issue, but I found a couple of them useful while troubleshooting some email issues I ran into.
https://www.sqlshack.com/troubleshooting-database-mail-failures/
https://stackoverflow.com/questions/14649685/check-if-sp-send-dbmail-was-successful/14652916
For this last link, this answer in particular:
sysmail_faileditems will only get you the list of failed emails. If you need to see a list of successfull emails you need to use sysmail_mailitems.
Use the below query to get details for all emails sent the same date:
SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())
And here is the complete query to get all the failed emails from the past 24 hours:
SELECT items.subject ,
items.recipients ,
items.copy_recipients ,
items.blind_copy_recipients ,
items.last_mod_date ,
l.description
FROM msdb.dbo.sysmail_faileditems AS items
LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > DATEADD(DAY, -1,GETDATE())
HTH
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Maybe the SQL Agent account cannot access the CSV file. If it's coming from a network location I believe you will need an AD account for SQL Agent. Something to check anyway.
-
You can try:
Execute as 'sa'
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply