powershell: invoke-sqlcmd catching errors when using -InputFile instead of -Query

Question

I have an powershell script which is executing sql scripts from a given folder. This is working, but in case of error I don't see the reason of the error. In some cases the error is displayed, when the powershell itself throws the error. But when it's a more simple failure such as a primary key conflict, no error is shown.

I've tried it the following way, but this does not work when using the "-InputFile" parameter. Replacing this by the "-Query" Parameter and the error will be shown.

try 
{
    sqlps Invoke-Sqlcmd -ServerInstance "$dbInstance" -Database "$dbName" -InputFile "$updateScriptsFolder\$updateSql" -username $username -password $password -ErrorAction 'Stop' -Verbose -OutputSqlErrors 1
}
catch
{
    Write-Host "Error while executing $updateScriptsFolder\$updateSql" -foregroundcolor red
    Write-Host $error -foregroundcolor red
}

The only solution for now is to add the error handling to every sql script. But it's very likely that developers forget to add the errorhandling to their script. I want a more general error handling. Can anybody help?

Thanks, Michael

Is there a reason you don't read the file into a string variable and use that variable as the value for the -query parameter? — nimizen
– nimizen, Commented Oct 13, 2011 at 7:09
Cool... I'll add my comment as an answer to stop this appearing unanswered (although I'm not sure if that's the correct S.O. etiquette). — nimizen
– nimizen, Commented Oct 18, 2011 at 16:24

nimizen · Accepted Answer · 2011-10-18 16:25:08Z

1

As per the comments above, read the file into a string variable and use that variable as the value for the -query parameter.

answered Oct 18, 2011 at 16:25

nimizen

3,4292 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2023-08-31 22:27:37Z

0

I got this same error when using -InputFile instead of -Query, but that wasn't the actual problem. The problem was that I had been improperly attempting to set a value (false) for -TrustServerCertificate. Once I removed that value, -InputFile started working just fine.

answered Aug 31, 2023 at 22:27

David

3,4656 gold badges36 silver badges51 bronze badges

Collectives™ on Stack Overflow

powershell: invoke-sqlcmd catching errors when using -InputFile instead of -Query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related