3

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)
    }
}
3

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
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.