0

I set up database mail on a SQL Server. When I send a test e-mail form SSMS it works. When I try to send an email using msdb.dbo.sp_send_dbmail via the query window I get: Mail (Id: xx) queued. but email not sent.

log of email that sent via test e-mail form SSMS (sent successfully):

Profile ID Recipients Copy Recipients Blind Copy Recipients Subject From Address Reply To Body Body Format Importance Sensitivity File Attachments Attachment Encoding Query Execute Query Database Attach Query Result as File Query Result Header Query Result Width Query Result Separator Exclude Query Output Append Query Error Send Request Date Send Request User Sent Account ID Sent Status Sent Date Last Mod Date Last Mod User
17 [email protected] NULL NULL Subject NULL NULL Body TEXT NORMAL NORMAL NULL MIME NULL 0 1 256 0 0 0 0 2024-10-10 12:10:02.467 myuser 11 1 2024-10-10 12:10:08.000 2024-10-10 12:10:08.900 sa

log of email that sent via query (queued but not sent):

EXECUTE msdb.dbo.sp_send_dbmail
    @recipients = '[email protected]',
    @subject = 'Subject',
    @body = 'Body',
    @profile_name = 'MyProfile'
Profile ID Recipients Copy Recipients Blind Copy Recipients Subject From Address Reply To Body Body Format Importance Sensitivity File Attachments Attachment Encoding Query Execute Query Database Attach Query Result as File Query Result Header Query Result Width Query Result Separator Exclude Query Output Append Query Error Send Request Date Send Request User Sent Account ID Sent Status Sent Date Last Mod Date Last Mod User
17 [email protected] NULL NULL Subject NULL NULL Body TEXT NORMAL NORMAL NULL MIME NULL 0 1 256 0 0 0 0 2024-10-10 12:01:15.897 myuser NULL 0 NULL 2024-10-10 12:01:15.897 sa

I checked the sysmail_faileditems, and there are no error logs related to what I sent...

Why it is working via test e-mail form SSMS but not via query?

Improve this question
7
  • Have you checked the permissions of the user sending the mail to run "sp_send_dbmail"? EXEC msdb.dbo.sysmail_help_profileaccount_sp Commented Oct 15, 2024 at 9:22
  • @mbielsa I thought it might be a permissions-related issue, but I don't know what to check. The sysmail_help_profileaccount_sp query simply returns the profile name and the account name. Commented Oct 15, 2024 at 9:31
  • You can also check by adding @[email protected] to sp_send_dbmail. That way, if that doesn't work, you'll get a more specific error. And also SELECT dp.name AS principalName, dpr.name AS roleName FROM msdb.sys.database_principals dp JOIN msdb.sys.database_role_members drm ON dp.principal_id = drm.member_principal_id JOIN msdb.sys.database_principals dpr ON drm.role_principal_id = dpr.principal_id WHERE dpr.name = 'MyProfileRoleName' Commented Oct 15, 2024 at 9:41
  • @mbielsa Adding from_address didn’t change much... I still don’t see any error log. (the mail is queued but not sent) Regarding the query you wrote, I get the following results: MyUserLogin - DatabaseMailUserRole and NT Service\SQLSERVERAGENT - DatabaseMailUserRole Commented Oct 15, 2024 at 10:13
  • Did you try add your user to role member? USE msdb; EXEC sp_addrolemember 'DatabaseMailUserRole', 'myuser' Commented Oct 15, 2024 at 10:26

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.