Post reply

write to SQL Server job history log from ps script

  • July 24, 2009 at 2:19 am

    #212487

    Hi,

    I have implemented a powershell agent job step. The step has to fail on a certain condition, that is coded like

    if ($LastExitCode -gt 0) { throw 'My Personal Error Message' }

    It works fine but the error message is not being written to the job history log. How can I achieve that?

  • January 7, 2010 at 2:44 pm

    #1100526

    I think it very much depends on what you are doing. But I have one running that does throw the error into the log. The code was taken from Chad Millers blog to get sql connectivity.

    http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!210.entry

    The code is pretty simple.

    # ---------------------------------------------------------------------------

    ### <Script>

    ### <Author>

    ### Chad Miller

    ### </Author>

    ### <Description>

    ### Verifies Sql connectivity and writes successful conenction to stdout and

    ### failed connections to stderr. Script is useful when combined with other

    ### scripts which would otherwise produce a terminating error on connectivity

    ### </Description>

    ### <Usage>

    ### Get-Content ./servers.txt | ./Test-SqlConn.ps1

    ### </Usage>

    ### </Script>

    # --------------------------------------------------------------------------

    foreach ($i in $input) {

    $connectionString = "Data Source=$i;Integrated Security=true;Initial Catalog=master;Connect Timeout=30;"

    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString

    # trap {Write-Error "Cannot connect to $i.";continue} $sqlConn.Open()

    trap {Write-Error "Cannot connect to $i."} $sqlConn.Open()

    if ($sqlConn.State -eq 'Open') {$sqlConn.Close();$i}

    }

    This file is saves as a powershell script named test-sqlconn.ps1 and you call it using the following code and passing in a servers.txt file with a list of servers you want to check for connectivity

    Get-Content ./servers.txt | ./Test-SqlConn.ps1

    I have this running in a SQL Job powershell step and it does throw the error correctly to the history as you are looking for (with some other stuff...).

    I am not sure how your code is being called but I believe there are only certain circumstances where this will be written to the history tables, but the way I have above definitely does.

    Hopefully this is helpful.

Viewing 2 posts - 1 through 1 (of 1 total)

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