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?
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?