December 26, 2021 at 7:03 pm
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
December 27, 2021 at 2:40 pm
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
December 28, 2021 at 9:48 pm
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.
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
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