Invoke-SqlCmd2 error handling fails to halt on error when EAP is set to SilentlyContinue

[fsackur]

Hi,

First of all, thank you for the code - even now, it still provides much value. And I think it's worth a PR.

I run Invoke-SqlCmd2 with a combination of ErrorAction SilentlyContinue and ErrorVariable; this gives me a way to parse errors and add value for my use case (I have a wrapper function that injects creds and default values). I do not want to emit the errors to the stream, as they include permission errors where I want my wrapper to retry with the next credential.

I have found that the script attempts to continue execution when continuing makes no sense.

I have a device where

PowerShell/Invoke-Sqlcmd2.ps1

Line 478 in 7d136b1

[void]$da.fill($ds)

errors with VIEW DATABASE STATE permission denied in database 'master'.

1. This obscures the error:

   PowerShell/Invoke-Sqlcmd2.ps1

   Line 486 in 7d136b1

   if ($PSBoundParameters.Verbose) {Write-Verbose "SQL Error: $Err"} #Shiyang, add the verbose output of exception

   - it should not be inspecting PSBoundParameters to determine whether or not to Write-Verbose; it should Write-Verbose and allow the engine to handle the stream. There is a naked Write-Verbose just above; L486 should also behave like that.
2. This also defeats Powershell:

   PowerShell/Invoke-Sqlcmd2.ps1

   Lines 488 to 494 in 7d136b1

   	switch ($ErrorActionPreference.tostring())
   	{
   	{'SilentlyContinue','Ignore' -contains $_} {}
   	'Stop' { Throw $Err }
   	'Continue' { Throw $Err}
   	Default { Throw $Err}
   	}

   - that is a bad approach for the same reasons. Write-Error respects -EA and EAP; the script should rely on that engine mechanism.
3. If L478 throws, then - as far as I can see - there is no scenario in which the script should continue. What is happening is the script moves on to

   PowerShell/Invoke-Sqlcmd2.ps1

   Line 526 in 7d136b1

   $ds.Tables[0].Columns.Add($Column)

   where it thorws - hard, this time - with You cannot call a method on a null-valued expression.

Expected behaviour when run with -EA SilentlyContinue and -EV set:

• EV is populated with error representing VIEW DATABASE STATE permission denied in database 'master'.
• Script returns control to caller after completing the finally block at

  PowerShell/Invoke-Sqlcmd2.ps1

  Lines 512 to 519 in 7d136b1

  	Finally
  	{
  	#Close the connection
  	if(-not $PSBoundParameters.ContainsKey('SQLConnection'))
  	{
  	$conn.Close()
  	}
  	}