Scheduling Jobs in SQL 2005

After posting Automatic Aging for GP a request to explain how to schedule such a script to run unassisted was posed. Leveraging SQL Server Agent Jobs to schedule tasks that can execute a myriad of different types of events is a powerful way to improve productivity and automate processes among other things. On top of that, it's incredibly simple!

In this example, I'm going to use SQL 2005. The process would vary depending on which version of SQL Server you are running. In SQL Management Studio expand the SQL Server Agent and right click on Jobs to create a new Job.

In the New Job Window, General Tab enter a name for your job, specify the owner and category, and enter a description if you'd like. I don't recommend using sa or individual user accounts as job owners. It's best to setup service accounts specifically for this purpose.

Next, Select the Steps Page and click the New button to create a new step. You can setup jobs with many steps of varied types. In this example, we'll create only one that will execute the rmAgeCustomer stored procedure. If you have multiple GP company databases you can setup a separate job to run RM Aging in each. Give the step a name, select Transact-SQL script (T-SQL) as the type, and specify the GP company database aginst which the SQL statement will run; in this case TWO.

Next, select the Schedule Page to specify when the job will run. In this example, the job will run every morning at 3 AM. This way, receivables are aged each morning before the business day begins.

Finally, setup alerts to write to the event log on success and notify your GP Administrator or DBA when the job fails.

This is a very simplistic example of using the SQL Server Job Agent. There are many best practices on managing jobs you should consider. The purpose of this post isn't to cover all of those but rather help introduce those new to GP & SQL Server to the concepts.