I'm a big fan of test-driven development and has for my latest project managed to write tests for (almost) all my code. My unit tests are written in JUnit, jMock, and NUnit.
I have now come to implementing a scheduled job on SQL Server 2000 (Using SQL Server Job Agent). The jobs run every x number of months (where x is specified by the user). I'm struggling to get my head around how to write an automated unit test for ensuring that the task is indeed being ran at the right time.
After more careful consideration I think I have derived a simple way of testing my scheduled tasks. At first I was thinking of somehow testing it using mock objects, but figure out a much simpler approach.
I will be splitting the tests into two disctinct test cases. One for testing that the task will execute at the desired time and another for testing that the job does what it is suppose to do.
Test Case: "Time"
The fixture for the test case includes setting up the SQL Server Agent Job using a stored procedure.
Test 1) Open connection to MS-SQL and examine the SQL Server Agent Job registry to see if it can find the specified jobs (using unique identifiers) .
Test 2) Open connection to MS-SQL and examine the SQL Server Agent Job registry to see if the specified jobs are set to execute at the desired time.
Test Case: "Job Tasks"
The fixture for the test case includes setting up the stored procedures that are to be executed when the SQL Server Agent triggers the scheduled tasks.
Test 1 - n) Test the desired effect of each task.
I think that should do it. If anyone can see any problems in doing the test in this way please feel free to comment.