CODEWITHSUNDEEP
sqlpowershellsqlcmd
Joe
Joe

Reputation: 69

Suppress Invoke-SQLCMD Warnings

I have the following code:

invoke-sqlcmd -ServerInstance $server\$instance -InputFile "$Path\checkDBEncryption.sql" -ErrorAction Ignore -ErrorVariable var | Format-Table;

I need any error from invoke-sqlcmd to be hidden from the powershell output since I'm already logging the errors in a separate file. The problem is, -ErrorAction Ignore (or SilentlyContinue) isn't working, and setting $ErrorActionPreference isn't an option.

Upvotes: 1

Views: 3850

Answers (2)

thepip3r
thepip3r

Reputation: 2935

if you want to send stuff to the bit-bucket as it were, use one of the following three techniques:

cmdlet | out-null

or...

[void]cmdlet

or... (and pertienent to your case):

cmdlet -ErrorVariable $errorvar
cmdlet -WarningVariable $warningvar

"I/O" in all modern shells is done via streams. The most common are standard-out (STDOUT), standard-error (STDERR), and standard-in (STDIN). PowerShell has a bunch more (e.g. debug, verbose, etc.). Most of the time, normal output comes into one of the 3 main streams. However, data can also be emmited out of the verbose stream or debug stream. Most interactions are based off of the 3 primary types so when trying to assign output from a cmdlet, you're essentially trying to assign something from STDOUT but the data is coming from STDERR...

Just in case your question relates to actually handling the error, you need to trap it and the syntax for that is thus:

try {
   cmdlet -ERRORACTION STOP
} catch {
   ## Do something with $_ or $error[0] or nothing at all
}

the -erroraction stop is in all caps because it is essential. Error trapping catches terminating errors, only. PowerShell defaults to non-terminating (with the error action preference variable you talked about above) errors which is why we need to explicity define that this cmdlet should terminate on error. This allows our error trapping logic to handle the different possible error conditions, as necessary.

EDIT -- Including an example from the comments:

try {
   $sql = invoke-sqlcmd -ServerInstance $server\$instance -InputFile "$Path\checkDBEncryption.sql" -ErrorAction Stop 
} catch {
   ## do nothing, leave this blank or leave this comment
}

Upvotes: 1

Kowalchick
Kowalchick

Reputation: 448

I recommend assigning it to a variable :

$response = invoke-sqlcmd -ServerInstance $server\$instance -InputFile "$Path\checkDBEncryption.sql" -ErrorAction Ignore -ErrorVariable var | Format-Table;

If you do not assign the action to a variable anything returned by that cmdlet will basically be dumped into the console.

In some cases it can also cause issues with variable assignment (depending on how the code was written).

As an example if you have a function that returns a value but no variable is associated with it that will be returned to the instance in which you are calling the function.

In addition I recommend you make sure that your attempts to silence that issue have not interfered with your logging details. I believe your fix is simple as this variable assignment.

Hope this helped with the issue!

Upvotes: 0

Related Questions