How many times you have received calls from your accounting team that GL side is not updated recently, and you as the system admin come to realization that Accounting task is stopped.
There might be multiple ways to monitor accounting task, including workflow calls from inside 4GL, but below is one simple example of accomplishing this using a SQL agent job that sends an email when accounting task is off.
Here are the steps:
- Setup Database mail in SQL server. There is a great Step by step blog post here with pictures that goes through that.
- After you have successfully setup Database Mail services and you can send a test email. Next we are going to create a SQL agent Job, like below.
- Right click on SQL Server Agent Jobs and select New job.
- Enter a new name and description.
- Click on Steps page and create a new step.
- Enter a name and pick TSQL for the type.
- For the command you can use something like below, Please note that Profile name would be the name of the Email profile you have created on step one.
use x3erpv11
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyTestMail',
@recipients = '[email protected]',
@subject = 'Accouting task is down, Take a look please!!',
@query = N'SELECT * FROM x3erpv11.SEED.BATCH where STA_0<>1'
- Click On Schedules and click New and define how often this query should run.
- Right click on SQL Server Agent Jobs and select New job.
Conclusion: You can use a SQL agent job to send you an email when accounting task status is not active.
