Sending Email when a new order is placed

Question

User1215529056 posted

Remove alert

|

Edit

|

Delete

|

Change type

0

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 = '[e-mail]'
        ,@body_format = 'HTML'
        ,@body =''
-- @query
        ,@Subject = @Subject

   Update OrderHeader Set UserDefined5 = 'Pick Ticket' Where JobNumber = @JobNumber

End

Thanks

Andy

Answer 1

User1535942433 posted

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

Answer 2

User364663285 posted

Hi,

You can put the code (that is to send out Email) into Add record Trigger of Order table.