Last post Aug 14, 2020 07:00 AM by wmec
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
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
WHERE CustAccount = '000403'
AND UserDefined5 = ''
FOR XML PATH('tr')
) AS NVARCHAR(MAX)) + N'</table>';
SET @Subject = 'Live Job - Pick Ticket ' + @JobNumber
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL2012Mail'
,@recipients = 'email@example.com'
,@body_format = 'HTML'
,@Subject = @Subject
Update OrderHeader Set UserDefined5 = 'Pick Ticket' Where JobNumber = @JobNumber
Aug 13, 2020 06:39 AM|yij sun|LINK
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:
Aug 14, 2020 07:00 AM|wmec|LINK
You can put the code (that is to send out Email) into Add record Trigger of Order table.