Invoke-SqlCmd2

Invoke-SqlCmd2 copied to clipboard

why the whole thing in the fist place ? PS output of any stream can be redirected to any given file pretty easily....

Jan 08 '19 16:01 niphlod

@niphlod Personally, I'm used to use PRINT to log info/debug messages. In this case, I think neither -Verbose nor MessagesToOutput parameters are appropriate.

Let's consider this simple case:

PRINT 'test log message';
SELECT 1

• -Verbose option will add verbose messages from Invoke-SqlCmd execution, plus 'test log message', and the user will get back the value of 1.

• -MessagesToOutput will return an array of values: ('test log message',1)

With -OutputFile, the 'test log message' string will go to the output file and the behaviour with Verbose can still be the same.

Jan 08 '19 16:01 jeffchulg

What did you ever do @jeffchulg , I know it's been a while. Would appending *> outputfile.txt work for you? This would redirect all powershell streams to the file.

Jan 14 '22 04:01 mattcargile

Hi,

personally, I modified Invoke-SqlCmd2 to do this:

if ($MessagesToOutput) {
...
}
else {

    if($OutputFile) {
                        $conn.FireInfoMessageEventOnUserErrors=$false # Shiyang, $true will change the SQL exception to information
                        $OutToFileHandler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Out-File -FilePath $OutputFile -InputObject "$($_)" -Append }
                        $conn.add_InfoMessage($OutToFileHandler)
                    }

Jan 14 '22 05:01 jeffchulg

Heard that. Glad it's working. Is the behavior any different than appending *> file.txt while also including the MessagesToOutput parameter? Or does using your new OutputFile parameter add something?

Jan 14 '22 05:01 mattcargile

Well, it's a very old subject and I don't remember all the testing I made before, but, according to my comment :

Let's consider this simple case:

PRINT 'test log message';
SELECT 1

-  `-Verbose` option will add verbose messages from Invoke-SqlCmd execution, plus 'test log message', and the user will get back the value of 1.
- `-MessagesToOutput` will return an array of values: ('test log message',1)

This means you cannot instantly differenciate between a logging message, a verbose message from the PowerShell function and an actual resultset.

The -OutputFile option will tell to Invoke-SqlCmd to write to a text file any text PRINTED during T-SQL execution (leaving exception contents apart) and return results sets as Invoke-SqlCmd would do without -Verbose option.

I'm not sure that a complete redirection as you propose would do that.

Jan 14 '22 06:01 jeffchulg

I would think then all you would need is the -Verbose parameter and then append 4> outputfile.txt for the verbose PRINT messages.

I just tested this and it did work on the current build of the function.

Invoke-sqlcmd2 localhost -Database db -Query "print 'hello world'
select 1" -Verbose 4> t.txt

Jan 14 '22 06:01 mattcargile

Could you try to raise an error and check that this error would be also thrown inside Powershell ?

Jan 14 '22 06:01 jeffchulg

Yeah it does. I tested the below which does output to the file. All streams in powershell can be redirected like niphlod said above.

Invoke-sqlcmd2 localhost -Database db -Query "print 'hello world'
raiserror(N'hello error' ,10 ,1 )
select 1" -Verbose 4> t.txt

Here is the terminating error

Invoke-sqlcmd2 localhost -Database db -Query "print 'hello world'
raiserror(N'hello fix this problem' ,16 ,1 )
select 1" -Verbose 4> t.txt

Output of the error is below from pwsh.exe.

Exception:
Line |
 584 |                          [void]$da.fill($ds)
     |                          ~~~~~~~~~~~~~~~~~~~
     | Exception calling "Fill" with "1" argument(s): "hello fix this problem"

Jan 14 '22 06:01 mattcargile

Well, it's almost what I want, but here is the content I get with your test script:

get-content .\t.txt

Running Invoke-Sqlcmd2 with ParameterSet 'Ins-Que'. Performing query 'print 'hello world' raiserror(N'hello error' ,10 ,1 ) select 1' Querying ServerInstance 'localhost' hello world hello error

With the OutputFile parameter, only "Hello world" would be written

Jan 14 '22 06:01 jeffchulg

The source code on the Gallery is different than what is in this code base even though those version numbers are the same. Thus far I have been testing with the source code on this repo. Please try to pull the code directly from this code base.

The code on github doesn't include the extra verbose output and would function more to your liking, I think.

Jan 14 '22 22:01 mattcargile