September 26, 2020 at 3:08 am
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!!!
September 27, 2020 at 3:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 27, 2020 at 3:16 am
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
Change is inevitable... Change for the better is not.
September 28, 2020 at 2:31 am
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!
October 1, 2020 at 1:41 pm
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
October 1, 2020 at 4:43 pm
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.
October 12, 2020 at 8:02 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy