write to SQL Server job history log from ps script

• Alexander G.

  SSCrazy

  Points: 2705

  More actions

  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?

• Mike - CI

  SSCarpal Tunnel

  Points: 4146

  More actions

  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.