Error when multiple use of Invoke-SqlCmd2 against the same SQLConnection with -Verbose

[jeffchulg]

Hi,

I've experienced an issue using Invoke-SQLCmd2 against the same instance of a SQLConnection object.

The command is :
Invoke-SQLCmd2 -SQLConnection $sqlconn -Query "PRINT 'Hello';" -Verbose

The first time, I get the expected output, which is VERBOSE: Hello
But, each time I run the command, I get extra lines that come.

The problem is there (there is a little modification from @RamblingCookieMonster's repository, but that do not change the things here)

Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller

       if ($PSBoundParameters.Verbose)
       {
           $conn.FireInfoMessageEventOnUserErrors=$false # Shiyang, $true will change the SQL exception to information
           $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
           $conn.add_InfoMessage($handler)
       }

Is there a way to detect a SQL Connection has already a "verbose handler" ?

[jeffchulg]

I have found a way to fix it.
You will find attached a customized version of Invoke-SQLCmd2 which

• has a new parameter called "OutputFile" (to allow to PRINT messages to be appended to a file)
• has two new variables at the beginning of Process code block: $OutToFileHandler = $null and $VerboseHandler = $null (which replaces $handler)
• the first handler is set if $OutputFile is not null and added as InfoMessage handler for the connection
• the second handler is set if verbose mode is active and added as InfoMessage handler for the connection
• a finally block is added after the call to fill()method so that we call remove_InfoMessage method of the connection, with $VerboseHandler and $OutToFileHandler (if necessary)

Invoke-SQLCmd2.txt