I made this little job to check every 15 minutes to see if there is a new job and send out an email with the job number in the header, nothing in the body. A person could easily see in the inbox new orders without having to open each to see the job number.
What I think is happening lately is they are sending a lot of orders in short period of time making this code unreliable. The orders are generated and pushed from an outside server.
I am being told they are not getting emails.
I suspect this Update OrderHeader Set UserDefined5 = 'Pick Ticket' Where JobNumber = @JobNumber
may be setting more than one UserDefined5 to 'Pick Ticket' and removing it from the next query.
Any suggestions how to make this more reliable instead of having it run every 5 minutes or less?
Accroding to your descirption,as far as I think,there are two guessings cause the issues that somesone don't get emails.
1. The service DatabaseMail90.exe sometimes stoped and thus relating to that external mail where not sent (since internal mail was sent right away).You need to check wheather the server is stopped,if it stopped,you must start it again and then send emails.
2.You could use replace MSDB with SMPT server.It has been optimized through the years to send emails quickly.
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
25 Points
184 Posts
Sending Email when a new order is placed
Aug 12, 2020 01:32 PM|RuthlessRoth|LINK
I made this little job to check every 15 minutes to see if there is a new job and send out an email with the job number in the header, nothing in the body. A person could easily see in the inbox new orders without having to open each to see the job number. What I think is happening lately is they are sending a lot of orders in short period of time making this code unreliable. The orders are generated and pushed from an outside server.
I am being told they are not getting emails.
I suspect this Update OrderHeader Set UserDefined5 = 'Pick Ticket' Where JobNumber = @JobNumber
may be setting more than one UserDefined5 to 'Pick Ticket' and removing it from the next query.
Any suggestions how to make this more reliable instead of having it run every 5 minutes or less?
DECLARE @recordCount INT;
DECLARE @query NVARCHAR(MAX) = N'';
DECLARE @Subject NVARCHAR(300) = N'';
--DECLARE @recipients NVARCHAR(50) = N'';
DECLARE @body_format NVARCHAR(50) = N'';
DECLARE @email_to NVARCHAR(50) = N'';
DECLARE @JobNumber NVARCHAR(50) = N'';
While (Select Count(*) From orderheader Where CustAccount = '000403' AND UserDefined5 = '') > 0
Begin
SET @JobNumber=( Select Top 1 jobnumber From orderheader Where CustAccount = '000403' AND UserDefined5 = '')
SET @query = N'<H4>Pick Ticket </H4> ' + N'<table border="1">' + N'<tr><th>Job Number</th>' + N'<th>Job Description</th> </tr>' + CAST((
SELECT Top 1 JobNumber
,'' Jobdescription
,''
FROM OrderHeader
WHERE CustAccount = '000403'
AND UserDefined5 = ''
FOR XML PATH('tr')
,TYPE
) AS NVARCHAR(MAX)) + N'</table>';
SET @Subject = 'Live Job - Pick Ticket ' + @JobNumber
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL2012Mail'
,@recipients = 'someone@someone.com'
,@body_format = 'HTML'
,@body =''
-- @query
,@Subject = @Subject
Update OrderHeader Set UserDefined5 = 'Pick Ticket' Where JobNumber = @JobNumber
End
Thanks
Andy
</div> </div> </div>Contributor
3950 Points
1550 Posts
Re: Sending Email when a new order is placed
Aug 13, 2020 06:39 AM|yij sun|LINK
Hi RuthlessRoth,
Accroding to your descirption,as far as I think,there are two guessings cause the issues that somesone don't get emails.
1. The service DatabaseMail90.exe sometimes stoped and thus relating to that external mail where not sent (since internal mail was sent right away).You need to check wheather the server is stopped,if it stopped,you must start it again and then send emails.
2.You could use replace MSDB with SMPT server.It has been optimized through the years to send emails quickly.
More details,you could refer to below articles:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6bb7b600-f025-451b-898b-2caa29c10d4d/only-some-email-gets-sent-successfully-from-spsenddbmail-database-mail-on-sql-server-2005?forum=sqltools
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-general-troubleshooting?view=sql-server-ver15
Best regards,
Yijing Sun
Contributor
6479 Points
5815 Posts
Re: Sending Email when a new order is placed
Aug 14, 2020 07:00 AM|wmec|LINK
Hi,
You can put the code (that is to send out Email) into Add record Trigger of Order table.
Peter