title: Error detection from Powershell Invoke-Sqlcmd not always working?
body: <p>The database is updated by executing a list of queries that are located in a folder.</p>
  
  <p>I need to be able to detect <em>any</em> errors that would also result in "Query completed with errors" in SQL Server Management Studio.</p>
  
  <p>The following works to detect the "Invalid Object" error:</p>
  
  <pre><code>PS SQLSERVER:\&gt; $ErrorActionPreference
  Stop
  PS SQLSERVER:\&gt; $Error.Clear()
  PS SQLSERVER:\&gt; $Error
  PS SQLSERVER:\&gt; Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database Test -Query "select * from doesnotexist" -ErrorAction SilentlyContinue
  PS SQLSERVER:\&gt; $Error.Exception
  Invalid object name 'doesnotexist'.
  PS SQLSERVER:\&gt;
  </code></pre>
  
  <p>Doing the same for select 1/0 does <em>not</em> work:</p>
  
  <pre><code>PS SQLSERVER:\&gt; $ErrorActionPreference
  Stop
  PS SQLSERVER:\&gt; $Error.Clear()
  PS SQLSERVER:\&gt; $Error
  PS SQLSERVER:\&gt; Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database Test -Query "select 1/0" -ErrorAction SilentlyContinue
  PS SQLSERVER:\&gt; $Error.Exception
  PS SQLSERVER:\&gt;
  </code></pre>
  
  <p>I would expect this to result in a "Divide by zero error encountered" error just like in SSMS.</p>
  
  <p>Not detecting this particular error makes me wonder if other errors will also remain undetected.</p>
  
  <p>Any idea why this is a happening and how I can make sure the <em>all</em> errors will be detected?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>It turns out that I do not have Invoke-Sqlcmd available on the server I am installing, so on second thought I have to use sqlcmd.exe.</p>
  
  <p>I think this is working for me:</p>
  
  <pre><code>$tempfile = [io.path]::GetTempFileName()
  $cmd = [string]::Format("sqlcmd -S {0} -U {1} -P {2} -d {3} -i {4} -b &gt; $tempfile",
      $g_connectionstring."Data Source",
      $g_connectionstring."User ID",
      $g_connectionstring."Password",
      $g_connectionstring."Initial Catalog",
      $path)
  Invoke-Expression -Command $cmd
  if ($LASTEXITCODE)
  {
      $err = Get-Content $tempfile | Out-String
      Corax-Message "SQL" "Error" $err
      exit
  }
  Remove-Item $tempfile
  </code></pre>