01 May, 2009

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.

3 comments:

Anonymous said...

Thank you for responding to my question. I attempted the following in Query Analyzer:

DECLARE @O_iErrorState int, @I_dAgingDate datetime
select @I_dAgingDate = convert(varchar(10), GetDate(), 102)
EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 0, 0, '', @O_iErrorState OUT
SELECT @O_iErrorState

And got the following error message:

Server: Msg 8144, Level 16, State 2, Procedure rmAgeCustomer, Line 0
Procedure or function rmAgeCustomer has too many arguments specified.

Once again thank you for responding to my earlier comment. Please let me know what I may be missing.

mbsguru said...

I assume you are on a version prior to v10. The code below should work for v9. I can't say I've done this for versions prior to 9 but could help you out if you need.

DECLARE @O_iErrorState int, @I_dAgingDate datetime

select @I_dAgingDate = convert(varchar(10), GetDate(), 102)

exec rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 0, @O_iErrorState OUTPUT

SELECT @O_iErrorState

smitha said...

really amazing blog and I was thankful to you for sharing such a useful information.
- CRM Consulting