Help me set up this task (Newbie)

  • Hey guys. I just started an SQL course and this is one of the first tasks I receieved.

    "If the money (different currencies) on an account is less than the limit, send an email to the bank"

    Basically I need to create a procedure that will be called upon every day at 7am, an email will be sent once a day to the bank.

    and I got this to call upon the bank

    GIVE_BACK_NAME_VPO_NADR

    MOS_RKT_PCK.GIVE_MAIL_TO_THE_BANK

    to call upon the limit on the account

    select * from MOS_PKR.opucurrencylimit

    select * from MOS_PKR.dashlimitopu -- where opu='MO10'

    and then I also got the function to call upon different bank offices with different currencies. (too long to write it down here).

    Can somebody please help me with setting it up? Would be really appreciated

  • You can write a stored procedure to check the money limit, then set up SQL Mail and use sp_send_dbmail  to generate the email based on the limit.

    Then use a scheduled job to run the stored procedure daily at 7:00

    • This reply was modified 3 years, 8 months ago by homebrew01.
    • This reply was modified 3 years, 8 months ago by homebrew01.
    • This reply was modified 3 years, 8 months ago by homebrew01.
  • homebrew01 wrote:

    You can write a stored procedure to check the money limit, then set up SQL Mail and use sp_send_dbmail  to generate the email based on the limit.

    Then use a scheduled job to run the stored procedure daily at 7:00

    I would not call xp_send_dbmail from inside the procedure.  I would have the procedure insert records into a holding table, and then execute a separate job/process that sends the emails.

    FraUno wrote:

    and I got this to call upon the bank

    GIVE_BACK_NAME_VPO_NADR MOS_RKT_PCK.GIVE_MAIL_TO_THE_BANK

    What are these?  Tables? Procedures"? Columns?

    We can't really provide much specific help, you have provided us nothing to look at.  See my signature line for an article on how to ask questions so we can help you better.

    If you can write the query that produces the list of banks that need an email, then insert those records into a "holding" table.   Then, query that table in a separate procedure / job, and send the emails.  If the email being sent is successful, flag the records in the table as "sent".  Once all of them are processed, attempt to re-send the flagged emails.  If it fails a second time, capture the errors and write them to the log.

    Truncate the records in this table, and read the SQL log to send you the failed emails so you can diagnose the issue.

    This is the link to xp_send_dbmail

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply