Sending Email when a new order is placed

Reply

Member

25 Points

Sending Email when a new order is placed

Aug 12, 2020 01:32 PM|RuthlessRoth|LINK

<div id="threadActionMenu" class="menu thread"> <div value="unsubscribe"> Remove alert</div> <div class="separator">|</div> <div value="edit"> Edit</div> <div class="separator">|</div> <div value="delete"> Delete</div> <div class="separator">|</div> <div value="changeType"> Change type</div> </div> <div class="clear"></div> <div class="messageSidebar"> <div class="iconWrapper"> <div class="status messageIcon question root">

</div> </div> <div class="votingouterbox"> <div class="voting">

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>

Reply

yij sun

Contributor

2210 Points

906 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

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.