Locate a table that contains Job Step History when Failed

[EE02757]

Hi everyone,

i'm trying to find on our Sql Server database where i could find the table that contains error messages when a job fail. I give you know an example :

OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataDrivenQueryTask_1
DTSRun OnError: DTSStep_DTSDataDrivenQueryTask_1, Error = -2147217843 (80040E4D) Error string: [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve service name Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147217843 (80040E4D); Provider Error: 12154 (2F7A) Error string: [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve service name Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0
DTSRun OnFinish: DTSStep_DTSDataDrivenQueryTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSDataDrivenQueryTask_4 DTSRun OnError: DTSStep_DTSDataDrivenQuer... Process Exit Code 2. The step failed.

This kind of message happened when a job fail, if you want to have precisions of the reasons you can Right-clic on the job on the enterprise manager,"View Job History", "Show Step Details", then when you clic on the step you can see the message...That is the kind of message i would like to access on a "system" table.

I would like to know on which table can i put out these datas? I've tried on MSDB :
- sysjobhistory
- sysdtspackagelog
- sysdtstasklog
- sysdbmaintplanhistory
...
But i didn't found what i wanted.
The aim is to realized a DTS that trace all DTS failed on last week, with the nature of the problem (with the message).

If anyone knows how to find it, it would be great...

Thanks
Bye

 

[mutley1]

Hi EE,

For DTS the best thing to do is turn on package logging. It will write the log to a text file on your server. and you can examine it when it fails to find out why.

1. Open the package you want to keep logs of
2. On the toolbar, click on "Package" then "Properties"
3. Click on the "Logging" tab
4. In the "Error Handling" section, put the path and a filename that you want to log the results to. I would do a seperate text file for each package you log obviously or it gets messy to read.

HTH.

M.

 

[EE02757]

Hi mutley1,

may be i wasn't clear... i didn't want to know what is the best way to read DTS failed logs but want to realize a dts that would run the week-end to know what exactly happened during the last week.


When a job fails you can do this operation to see step detail can't you ? :
- Right-clic on the job on the enterprise manager
- "View Job History"
- "Show Step Details"
- Clic on the step

you will have a precised message of the error won't you?
Like :
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataDrivenQueryTask_1
DTSRun OnError: DTSStep_DTSDataDrivenQueryTask_1, Error = -2147217843 (80040E4D) Error string: [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve service name Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: ...

So you agree me that these errors for every jobs are stored on a Db so that you can see every step details of every jobs.

But i have founded (in another forum the solution) :

SELECT message FROM sysjobhistory
WHERE run_status = 0 -- only failed executions
AND job_id = '7E2ADC28-5FBA-49A8-879F-CEB0FBC2B749'
AND step_id >0

 

[mutley1]

Hi EE,

sorry - I thought you meant you were looking at DTS failures!

Here's a script to monitor job failures.It will pull out everything from job history, but I'm posting it the way I use it. We have a table that it populates so we can check anything that failed or anything that succeeded etc. as and when we need. You can just add where clauses in at the end and define dates if you like

Create the table

CREATE TABLE [JobHistory] (
	[lngID] [int] IDENTITY (1, 1) NOT NULL ,
	[Server] [varchar] (50) NULL ,
	[Jobname] [varchar] (400) NULL ,
	[step_id] [int] NULL,
	[status] [varchar] (10) NULL ,
	[rundate] [varchar] (10) NULL ,
	[runtime] [char] (8) NULL ,
	[runduration] [char] (8) NULL ,
	[message] [varchar] (2000) NULL
) ON [PRIMARY]
GO

code to populate table

truncate table JobHistory
GO
INSERT INTO JobHistory (server, jobname, step_id, status, rundate, runtime,
runduration, message)

SELECT sj.originating_server, sj.name, sjh.step_id as step_id,

--Case to give job result

CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END AS Status,

--Convert Integer date to regular datetime 

CONVERT(VARCHAR(8), sjh.run_date) as rundate

--Change run time into something you can reecognize (hh:mm:ss)

, LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) AS runtime

--Change run duration into something you caan recognize (hh:mm:ss)

, LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) AS runduration,

--Why did it fail?
sjh.message

FROM msdb.dbo.sysjobs sj --job id and name

--Job history

INNER JOIN msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id

order by rundate DESC, [name] ASC, runtime DESC

HTH,

M.