Search code examples
powershellinvoke-sqlcmd

Catching Errors from Invoke-SqlCmd

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.

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.

My current Try/Catch blocks for these sections:

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 $_
}

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.

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

I know that there is known issue with Invoke-Sqlcmd and error handling (source, source), 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?

Any help is appreciated!

Solution

  • What you want to do as tell the cmdlet Invoke-Sqlcmd to stop if an error is received, then it will hit your catch block properly. You do this -ErrorAction Stop. So using the command in this manner will have the catch block entered

    $fullServerName = 'Doesnotexist'
$loginCommand = "SELECT @@SERVERNAME"
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" -ErrorAction Stop

    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 $_
}

    An example of the above executed via a ps1 file: enter image description here

    Then if I remove the -ErrorAction Stop from the script: enter image description here