Post reply

SQL Agent Job Step message

  • January 12, 2011 at 3:50 am

    #233127

    Hi experts,

    I plan to set up a script that will monitor SQL Agent jobs and if the their status is failed provide additional info on the reasons why.

    My question is basically how can I retrieve the error message for a failed job with PowerShell?

    Thanks for your help.

    __________________________
    Allzu viel ist ungesund...

  • January 25, 2011 at 2:14 pm

    #1278185

    Determining the SQL Agent job information for a failed job is basically as easy as running a query against msdb.dbo.sysjobhistory where the run_status = 0 and then pulling out the error message associated with the failure.

    You can do that in powershell in a number of ways i.e. Invoke-sqlcmd, create a connection string to execute, etc... It really depends on what you are looking to do with the data once you get it as to what will be the best way to get that data.

  • January 26, 2011 at 12:37 am

    #1278311

    Thanks for your response. I'm aware of the Invoke-Sqlcmd Cmdlet option but was looking for a more straight forward solution. Looks like there's no way to pull the error msg directly. Thanks.

    __________________________
    Allzu viel ist ungesund...

  • January 26, 2011 at 7:06 am

    #1278451

    I see what you are looking for here. I think the SMO route actually might be better for you then, and here is a link that describes how to do that and gives a ton of sample code to go along with it.

    http://www.mssqltips.com/tip.asp?tip=1798

    At least I think that is more of what you are looking for.

Viewing 4 posts - 1 through 3 (of 3 total)

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