Post reply

Locate a table that contains Job Step History when Failed

  • July 11, 2007 at 8:46 am

    #174319

    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

  • July 11, 2007 at 9:15 am

    #718669

    You'll find this information in msdb.dbo.sysjobhistory.

    Greg

    Greg

  • July 12, 2007 at 12:21 am

    #718869

    Hi Greg,

    I've mentionned this table in the explanation...

    I've ever seen it but it doesn't contain the history of the step of the job failed...

    Another idea?

    (Thanks)

  • July 12, 2007 at 1:03 am

    #718875

    You will have to enable the logging for the dts package to get the detailed description. The job history would not give you the exact details of the error in case of DTS.

    So the next time when it runs and fails there would be detaile message.

  • July 12, 2007 at 1:20 am

    #718880

    Hi,

    yes i know that, in fact i don't want to solve dts problems but i want to realize a dts that would run the week-end to know what exactly happened during the last week.

    I think that what you say is partially wrong...i say "i think"... i explain you now why.

    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.

    That's the reason why i'm sure it exists a table that contain that (different of msdb.dbo.sysjobhistory).

    msdb.dbo.sysjobhistory helps me but doesn't give the information i would have.

    If anyone knows where i can find it...

    Thanks

    BYE

  • July 12, 2007 at 3:13 am

    #718899

    The message your looking for is in sysjobhistory.

    Try

    select message from sysjobhistory

    where run_status = 0 -- only failed executions

    AND job_id = 'Your_Job_Id'

    and you will find it. The problem with sysjobhistory is that the message can only be a max of 1024 characters, which for complex DTS packages usually is not enough. Therefore I prefer my DTS packages writing a log to a file.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • July 12, 2007 at 3:16 am

    #718900

    Sorry I forgot to exclude stpe_id 0 which gives you just the overall outcome, no details.

    So better use:

    SELECT message FROM sysjobhistory

    WHERE run_status = 0 -- only failed executions

    AND job_id = '7E2ADC28-5FBA-49A8-879F-CEB0FBC2B749'

    AND step_id >0

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • July 12, 2007 at 3:35 am

    #718903

    Hi Markus,

    i've just tried your query...unfortunatelly there were rows, but empties!

    That's very strange, i think your query is right however...

    I don't know how to do, i've try with a failed job which step history is reachable by doing this :

    - Right-clic on the job on the enterprise manager

    - "View Job History"

    - "Show Step Details"

    - Clic on the step

    So i am sure that the failure message details exists...nonetheless when i try with you query rows are empties.

    I understand the problem  that the message can only be a max of 1024 characters but i should see at less a little bit...Don't you think?

     

  • July 12, 2007 at 4:29 am

    #718909

    Ok guysssss.

    I have founded the problem. I've tried your query output on a file, and the file gave me all the job steps failed message !!!!!!

    The reason is when i tried this query i tried from the entreprise manager (right clic on the table, open table, query) but it gave me empties rows.

    Sincerly the reason why i have no idea.

    Then i've tried it on the query analyzer which actually truncate the message (1024 characters).

    Thanks a lot and i hope our reasearches would help other guys in the future...

    Bye

  • July 12, 2007 at 6:00 am

    #718927

    Hope this can work:

    SELECT name 
    FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B 
    WHERE A.job_id = B.job_id AND B.last_run_outcome = 1

  • July 12, 2007 at 6:16 am

    #718932

    hi yitongzhang 

    I have found, i have found don't worry, the query

    SELECT message FROM sysjobhistory

    WHERE run_status = 0 -- only failed executions

    AND job_id = '7E2ADC28-5FBA-49A8-879F-CEB0FBC2B749'

    AND step_id >0

    given by MarkusB is right. The problem is to be carefull when you use entreprise manager to realize

    the query, because it don't give you the message, whereas on the DTS package when you put it into

    a file you have these datas....

    Thanks

  • July 12, 2007 at 10:09 am

    #719026

    I always use QA rather than EM to run queries.  There's way more flexibility in QA.

    Greg

    Greg

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply