I have got a backup maintenance plan, and I want to output to a file if it has been a success or fail. I was using sp_help_jobhistory and relying on the run_status to determine if it succeeded(1) or failed(0). I have found this to be unreliable compared with the report which is in the SQL Agent Log File Viewer.
I assume this has to be stored some where, how do I access the table where this is stored? So i can return the results of my maintenance plan from SQL Agent.
that data is not stored in a table. it's stored in agent's log file, which is a flat file called SQLAGENT.OUT (or SQLAGENT.X where X is an integer, for archived log files. a new log file is created each time agent starts, and the old file is archived. only a certain number of archived logs are kept around, don't remember how many)
Warning! read on at your own risk, the rest of this post talks about undocumented system procedures, and they could change in future releases or service packs!
You can read the file from sql using the extended proc xp_readerrorlog.
the first param says which file to read in terms of history. 0 specifies the current log file, 1 the last one, 2 the one before that, and so on.
the second param says which type of file to read. 1 means sql server, 2 means sqlagent. so pass 2 here since you want to read agent's log
exec xp_readerrorlog 0,2 -- read the current agent log file
exec xp_readerrorlog 0,1 -- read the current sql server log file
exec xp_readerrorlog 1,2 -- read the last archived agent log file
exec xp_readerrorlog 1,1 -- read the last archived sql server log file