SQL Server Agent Job help 1

[spence74]

I am working on a project to automate as much data entry as possible. All servers are running SQL Server 2000 and my data source is on another server. I cannot create a linked server because the SA passwords are different between the two boxes and that cannot be changed.

The job that I am trying to create has 5 steps.
1 - DTS Package to pull records from another server.
2 - Stored Procedure to validate records, flag records with errors and insert error information into an error table.
3 - DTS Package to create required metadata.
4 - Stored Procedure to move validated records from the transfer database into the production database on the same server.
5 - DTS Package to clear records from the remote server.

The problem that I am having is that step #2 works fine when all the steps are kicked off individually, but when I kick off the automated job, it is like step #2 is skipped and all records are making it into production whether they have errors or not. Does anyone know of any bugs in SQL Server 2000 that may cause this? (Perhaps like the bug that blocks email notifications when a job is scheduled?)

Another thing that I was thinking of was maybe the validation routine is being kicked off too soon, even though I have each step set up to go to the next one on success. Is there any way to launch a SQL Server Agent Job from within another SQL Server Agent Job?

 

[SQLBill]

The problem is that the step isn't skipped, it's that the procedure doesn't complete before the next step starts.

Step 2 is just to run the stored procedure.
EXEC someprocedure

That happens. Once the EXEC statement is issued, that step has finished and goes onto Step 3. But, while the EXEC has been run, the procedure itself is STILL running.

One solution, add a new step after 2 that waits. Check the BOL for WAITFOR. Run Step 2 by itself to see how long it takes to run AND complete. Then make the new STEP 3 wait that amount of time or a bit longer.

-SQLBill

Posting advice: FAQ481-4875

 

[spence74]

I thought that the suggestion would work, but it still does the same thing. If I was to set up a second job to move the records, is there any way that I can kick off the second job from within the first?