Burned again by the famous combination of Oracle and SQL Server linked servers, and a poorly configured SQL Agent job.

Scenario:

1. Job Runs every 5 minutes
2. Job Runs a standard T-SQL Stored Procedure
3. Stored Procedure makes use of Oracle Linked Server (full 4 part naming and Open query used)
4. Oracle goes down for scheduled maintenance
5. SQL DBA's forget to stop job
6. Job pages on-call (me)
7. Disable job until Oracle maintenance complete
8. Oracle maintenance complete.
9. Connectivity of linked server verified
10. Re-enable job
11. JOB Fails !
12. T-SQL Stored procedure runs from SSMS fine, but won't run as a job

Here is the poorly configured sql agent.  1st off the job output file is set to over-write, so know as I write this I can't quote the exact error, 2nd the system was set at the default job history retention, so I can't go into the job history to grab the error either.

Basically the error was something about reading a row from sysoledbservers or something.

The solution was to Stop and Start SQLAgent; now why SQLAgent, running tsql using a linked server would not work, when the sqm tsql would run just fine from SSMS is beyond me, which is why I wish i had the exact error, as I was going to call pss support, which i didn't do at 1am in the morning.