Introduction
This post provides a sample of the steps required to use the SQL SERVER MANAGEMENT STUDIO user interface to create an automatic job that runs as a timed event. This process is useful when you need to automate certain tasks to run on a schedule.
Instructions
Step 1: Open SQL Server Management Studio and expand the SQL Server Agent. Then right click on the Jobs folder and select “New Job…”
Step 2: Give the Job a meaningful name. We do not recommend starting the name with P21, because it will be harder to find in the list among the P21 jobs that are already there. You can also optionally add a description.
Step 3: Click on the Steps menu item in the left pane of the new job window.
Then click the New… button near the bottom of the window
Step 4: Give the new step a meaningful name.
Select the type of step, which should be “Transact-SQL script”
Select the database that the job should run against, remember to test in a Play environment first
Finally, you need to type in the command that needs to be run in order to execute the procedure
Generally, the format will be: EXEC <name_of_procedure> <param_value>, <another_param_value>. More information on executing stored procedures can be found at: https://www.sqlservertutorial.net/sql-server-stored-procedures/basic-sql-server-stored-procedures/
Step 5: Click on the Schedules page on the left pane of the New Job window
Then click the New button to create a new schedule.
Step 6: Give the new job schedule a name, and then select the frequency.
In this case, we want the job to run at 1AM on the 1st day of every month.
Once the schedule is defined, click the OK button.
NOTE: the summary/description will update as you change the schedule and is a good way to make sure you are setting up the schedule you really want.
Step 7 (optional, but recommended): Click the Notifications page choice on the left pane of the New Job window
Select an operator to send a notification to in the event the job fails.
Finally, click OK in the New Job window and the job will be saved and go into effect immediately.
WARRANTY/DISCLAIMER
Atlas Precision Consulting does not recommend using any SQL statements to update your database without having those statements first reviewed by Epicor or other experienced SQL professionals. Test any code in your Play Database!
Using SQL statements to update your database may result in corrupting your database. Test any code in your Play Database!
THE TUTORIAL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE TUTORIAL OR THE USE OR OTHER DEALINGS IN THE TUTORIAL.
