title: Catching Errors from Invoke-SqlCmd
body: <p>I am working on a Powershell script that takes as input as CSV file containing information about servers and the databases on each server in order to create a specific login and user for every server and database in the file. The script attempts to create the login and user with SQL commands using Invoke-SqlCmd, and then outputs the results of the attempt to a new CSV file. If the login and user were successfully added, the data is output with a success message, and if either of the commands fail, it is supposed to output the data with a failure message. Right now, if there is a Invoke-SqlCmd error, the script does not acknowledge that there was a failure and outputs that the run was successful. </p>
  
  <p>I would like some help figuring out how to write my Try/Catch blocks so that they actually catch that the SQL commands failed and output the correct message.</p>
  
  <p>My current Try/Catch blocks for these sections:</p>
  
  <pre><code>Try # Create Login
  {
      # Importing sqlserver module to run the commands
      if (-not (Get-Module -Name "sqlserver"))
      {
          Import-Module sqlserver
      }
      Write-Host "Checking server $fullServerName to see if login [Example_Login] exists. Will create login if it does not already exist."
      Invoke-Sqlcmd -ServerInstance $fullServerName -Query "$loginCommand"
  
      Write-Host "Login command successfully executed.`n"
  }
  Catch
  {
      Write-Host "An error has occurred while attempting to add login [Example_Login] to server $fullServerName."
      Write-Host $_
  }
  
  Try # Create database user
  {
      Write-Host "Checking server $fullServerName to see if user [Example_User] exists on database $currentDatabase. Will create user if it does not already exist."
      Invoke-Sqlcmd -ServerInstance $fullServerName -Query "$databaseUserCommand"
      Write-Host "Database User command successfully executed.`n"
  }
  Catch
  {
      Write-Host "An error has occurred while attempting to add user [Example_User] to login [Example_Login] on $currentDatabase."    
      Write-Host $_
  }
  </code></pre>
  
  <p>When running the above code, which I would hope would catch that there was an issue when Invoke-SqlCmd was run, I get the following error but the script outputs that the run was a success.</p>
  
  <pre><code>Invoke-Sqlcmd : Database 'MyDatabase' does not exist. Make sure that the name is entered correctly. 
   Msg 911, Level 16, State 1, Procedure , Line 2.
  At E:\Create_Login.ps1:175 char:6
  + ...             Invoke-Sqlcmd -ServerInstance $fullServerName -Query "$da ...
  +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
      + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
  
  </code></pre>
  
  <p>I know that there is known issue with Invoke-Sqlcmd and error handling (<a href="https://social.technet.microsoft.com/Forums/Lync/en-US/53d43256-557d-4615-9fd2-8d1e449097f9/invokesqlcmd-try-and-catch?forum=winserverpowershellsource" rel="nofollow noreferrer">source</a>, <a href="https://www.sqlservercentral.com/blogs/invoke-sqlcmd-and-error-results" rel="nofollow noreferrer">source</a>), but is there any way I can make my script output the correct message for situations like this? Right now, I am stuck manually going in to my output file and changing the message from Success to Failure. This is in a work environment, so I would preferably like my script to be able to be executed by others without them having to make edits as well. Am I just making some simple mistake?</p>
  
  <p>Any help is appreciated!</p>