Hi

I have a really odd problem here!

I run the following process daily. There are 16 DTS packages that are all
set up exactly the same except for the name of the TempDataHoldingTable (the
table structures are identical) and the SELECT code which gets the data from
the live source. These DTS bring data from 16 very different live source
databases into one place into one format - a reporting warehouse.

Each DTS package carries out the following steps:
1. Select data from LiveSource into TempDataHoldingTable
2. Exec SP Update_NewRecs
This SP inserts data from TempDataHoldingTable into table ReportingData
where the ID does not alreay exist
3. Exec SP Update_Changes
This SP inserts data from TempDataHoldingTable into table ReportingData
where the ID DOES already exist but one of the fields data has changed.
4. Exec SP Update_Deleted
This SP inserts data into table ReportingData where the ID does not
exist in TempDataHoldingTable

This process works fine for 15 out of 16 of the DTS's when run one after the
other in an SQL job

On one of them it runs SP Update_NewRecs fine and appears to have run SP
Update_Changes (according to the error log it's run successfully) but the
updated records have not actually been inserted! If I then run the DTS on
it's own, all runs fine! I have tried everything! Close each connection
after each step, put the code into transaction statements in order to commit
at the end of each one, added select into statements within the SP to put the
data that should be inserted into an error logging table and all goes into
there fine, but STILL not into the ReportingData table where it should be!
Also, when trapping the TSQL @@ERROR at various stages throughout the
process, still getting 0 errors.

I can't think what else to check! It all runs fine if run manually, or if
scheduled on it's own, just won't run within the process! I even tried Exec
SP Update_Changes as it's own step right at the end of the SQL job, not in a
DTS, and it still doesn't do the insert. Run the code from Query Analyser
or just re-run the job but start from the last step (Exec SP Update_Changes)
and it inserts fine!

Help!!!!! It's driving me mad!!!!!