SQL server agent reverting to TEST configurations

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.
Data Source=cfasqls08;Initial Catalog=CFA_EDW_DM;Integrated Security=True;

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.

more ▼

asked May 08, 2017 at 01:08 AM in Default

avatar image

10 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jul 13, 2017 at 09:06 AM

avatar image

101 2 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 08, 2017 at 01:08 AM

Seen: 54 times

Last Updated: Jul 13, 2017 at 09:06 AM

Copyright 2018 Redgate Software. Privacy Policy