How to Natively Return Output when using Invoke-Sqlcmd?

Question

How do I natively log output from the PowerShell Invoke-Sqlcmd?

I am using Invoke-Sqlcmd to loop through a large (50,000-90,000 SQL files w/ 1-10 inserts in each) set of small SQL insert statements. Most of the insert statements run correctly, but a few of the strings are too long and I get the "string or binary data would be truncated" error.

Right now I am using Start-Transcript to capture the output and identify the problematic insert statements, but Start-Transcript captures everything. I'd like to only catch the errors -or- just catch the Invoke_Sqlcmd statement output.

If this worked (as I believe it should), I would be all set:

Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true | Out-File -FilePath "C:\Temp\Errorlog.txt" -Append

Or if this worked:

Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true | Tee-Object -FilePath "C:\Temp\Errorlog.txt" -Append

But neither of those work.

I have also tried this monstrosity but I don't get any errors at all from it:

$sqls = (Get-ChildItem -Path "I:\2019_06_24\_SQL"-Filter *.sql | Sort-Object -Property CreationTime).FullName
        foreach ($sql in $sqls)
            {
            $Error.Clear()
            try 
                {
                Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true
                }
            catch 
                {
                'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' | Out-File "C:\Temp\Errorlog.txt" -Append
                $sql | out-file -filepath "C:\Temp\Errorlog.txt" -Append
                $Error | out-file -filepath "C:\Temp\Errorlog.txt" -Append
                } 
            }

How do I log directly from the Invoke-Sqlcmd statement?

Answer 1

This code is based on your "monstrosity" example above.

At the start of each loop it clears the $error variable. Then if the Invoke-Sqlcmd generates an error the IF statement picks it up. It will then add the FullName ($sql) to the log file and then add the error on the next line.

$sqls = (Get-ChildItem -Path "I:\2019_06_24\_SQL" -Filter *.sql | 
    Sort-Object -Property CreationTime).FullName
foreach ($sql in $sqls)
{
$error.clear()
Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true 
if ($error -ne $null)
    {
    $sql + '
' + $error | Out-File -FilePath "C:\Temp\ErrorLog.txt" -Append
    }
}

As it is, the ErrorLog.txt will look something similar to this:

I:\2019_06_24\_SQL\Test_File_5.sql
String or binary data would be truncated.
The statement has been terminated. 
Msg 8152, Level 16, State 30, Procedure , Line 3.
I:\2019_06_24\_SQL\Test_File_303.sql
String or binary data would be truncated.
The statement has been terminated. 
Msg 8152, Level 16, State 30, Procedure , Line 3.