title: Suppress sqlpackage.exe warnings/errors in Powershell when triggered using TFS build
body: <p>I have an automated deployment script to deploy database changes using a dacpac and sqlpackage.exe, SSRS reports and a website. </p>
  
  <p>When I execute the script at the command line, sqlpackage outputs the following message:</p>
  
  <blockquote>
    <p>The object [objectname] already exists in database with a different
    definition and will not be altered.</p>
  </blockquote>
  
  <p>The output is in yellow on the console, indicating it is a warning, the deployment continues and completes as expected. </p>
  
  <p>However when I trigger the same script through TFS build, the same message is logged in the Build explorer, but with the error icon beside it. The deployment still continues, automated tests are run etc., but the build status changes to the icon with a tick and cross in it - indicating the build completed successfully, but other tasks failed. </p>
  
  <p>There is a parameter that can be provided to sqlpackage, UnmodifiableObjectWarnings, which is supposed to suppress this kind of warning, however there is a post on msdn indicating there is a bug with this parameter, and it doesn't work.</p>
  
  <p>I'm wondering how else in Powershell I can trap and suppress this kind of warning, as I don't want my build/deployment to fail just because of this warning. However I can't suppress all error output from the call to sqlpackage, as there may be other, completely valid errors. </p>
  
  <p><strong>Update</strong></p>
  
  <p>I've tried following the suggestions here:
  <a href="https://serverfault.com/questions/340711/redirect-stderr-to-variable-in-powershell">https://serverfault.com/questions/340711/redirect-stderr-to-variable-in-powershell</a>
  and have tried using similar to the following code:</p>
  
  <pre><code>  try {
          $test = &amp; "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:"$dacpac" /Profile:"$dbProfile" /p:UnmodifiableObjectWarnings=True /Action:Publish 2&gt;&amp;1
  
          $errMsg = $test | ?{$_.gettype().Name -eq "ErrorRecord"}
          $normMsg = $test | ?{$_.gettype().Name -ne "ErrorRecord"}
      }
      catch {
           Write-Host "Caught Exception $_"
      }
  </code></pre>
  
  <p>however as soon as the warning message is returned by the exe, an exception is thrown and handled by my calling function - and the deployment does not occur. </p>
  
  <p>However if I call it as follows:</p>
  
  <pre><code>try {
      &amp; "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:"$dacpac" /Profile:"$dbProfile" /p:UnmodifiableObjectWarnings=True /Action:Publish 2&gt;&amp;1
  }
  catch {
       Write-Host "Caught Exception $_"
  }
  </code></pre>
  
  <p>i.e. without the variable $test capturing output, the deployment completes successfully, but the catch still catches the same exception. Why does capturing the output in a variable cause the exception to be thrown immediately, however not capturing it still causes it to be caught, but after completing successfully?</p>