I am having a problem sending email from one of my servers.
I want to use DatabaseMail and/or msdb.dbo.sp_send_dbmail
I have other production servers that use all the same databaseMail settings and the same SMTP server, and they are all working fine, only this one particular server is not sending emails via Sql Server and I need to find out why.
The first thing I test is the connection to the SMTP server, and for testing that I use Powershell to send an email, and it works fine!!!!
Here is the script as how to send emails using Powershell.
$smtpServer = "18.104.22.168" $smtpPort = 25 $emailFrom = "firstname.lastname@example.org" $emailTo = "email@example.com" $smtp = new-object Net.Mail.SmtpClient($smtpServer) $smtp.Port = $smtpPort $subject = "ccatsql" $body = "test email from ccatsql " $smtp.Send($emailFrom, $emailTo, $subject, $body)
and that works fine.
the problem that I am having in that server is DatabaseMail didn't start when an email is queued
when I query the sysmail_event_log I don't see any event related to the emails I have just sent (attempted to).
SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc
when I query the sysmail_unsentitems I can see all my emails are there, in an
SELECT * FROM msdb.dbo.sysmail_unsentitems ORDER BY send_request_date DESC
This is the piece of code that I am mostly using to test the email sending. It shows how to send the result of a query by email, but in this case I made the query very simple because I need to get the email working first.
-=========================================================================== -- Failed to initialize sqlcmd library with error number -2147467259 -- that was caused because I had left a piece of rubish code from a previous query -- and internally it was saying: Invalid column name 'sintMarketID'. -- but I could only see this using the profile. --========================================================================== set deadlock_priority high set transaction isolation level repeatable read declare @sub varchar(150) declare @radhe int select @sub = '-- Number of rows affected by Hare Krishna job run at ' + CAST(convert(datetime, getdate(), 100)AS VARCHAR) --print @sub EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA', -- Change profile name accordingly @firstname.lastname@example.org', -- change mail address accordingly @subject = @sub, @body_format = 'TEXT', @importance='High', @sensitivity='Private', @file_attachments=NULL, @execute_query_database='MSDB', @query_no_truncate=0, @query_result_header = 1, @mailitem_id= @Radhe OUTPUT, @query= ' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON DECLARE @SQL VARCHAR(MAX) SELECT @SQL = '' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select getdate() '' EXEC(@SQL) '
there are similar questions without a conclusion:
DatabaseMail process is shutting down
What is missing?
select * from msdb.dbo.sysmail_faileditemsI see all the emails there with