I run an overnight job in PRODUCTION using SQL Server 2012 Agent. It has 11 steps & takes around 8 hours.
Though it was running smoothly for some time, it suddenly started failing from 10 days ago!
When I check the Job properties, the "Data Sources" tab has reverted to TEST configurations, as example
Data Source=cfasqls05-test.;Initial Catalog=CFA_EDW_DM;Integrated Security=True;
These might have been the original config values for the Data Source when they were setup many years ago by the then developer - I don't know.
But I keep changing them to point to our Prod server - e.g.
Then I check the MSDB sysjobsteps table -> the Command field is showing the correct Prod values :)
But when I run the job overnight, it fails trying to access the TEST server!
What's causing this reverting to the TEST server configs? Shouldn't SQL Agent be using the MSDB sysjobsteps values in the overnight runs? Am I missing something ?!?
Any help would be greatly appreciated.
asked May 08, 2017 at 01:08 AM in Default
Is this part of a ssis project? could that be the problem as you might change them but when it executes the actual project, it reverts back to original values???
answered Jul 13, 2017 at 09:06 AM