Sql-server – How to catch error from sql server on powershell

powershellsql server

I have written powershell script that executes all sql from folder, but I want to stop executing on error? Is it possible to catch the error with powershell that was happened on SQL server? This is powershell code

function runSqlScript{
    param([string]$sqlPath) 
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString.connectionString
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = Get-Content $sqlPath
    $command.ExecuteNonQuery()
    $connection.Close()
}

function runScriptsFromFolder{
    param( [string]$pathToFolder)
    Get-ChildItem $pathToFolder -Filter *.sql| ForEach-Object {
        runSqlScript($_.FullName)
    }
}

Best Answer

Powershell supports try/catch/finally. This allows you detect and handle errors within the scope of try block.

If you simply want to stop the PS script if any errors occur, including non-SQL errors, wrap the main code in a try block:

try {
    runScriptsFromFolder -pathToFolder "C:\SqlScripts"
}
catch {
    throw
}
Related Question