locked
Sending Email when a new order is placed RRS feed

  • Question

  • User1215529056 posted
    Remove alert
    |
    Edit
    |
    Delete
    |
    Change type
    Question
    You cannot vote on your own post
    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 = 'someone@someone.com'
            ,@body_format = 'HTML'
            ,@body =''
    -- @query
            ,@Subject = @Subject

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

    End

    Thanks

    Andy

    Wednesday, August 12, 2020 1:31 PM

All replies