5

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?

  • What version of PowerShell are you running? Can you add that to your question? – DBADon Jun 26 at 14:19
  • I used this insert statement in a few SQL files: INSERT INTO Test_Table (Test_Data, More_Test_Data) SELECT 'Some text here', 'More text here'. If your insert statements are somehow different &/or would change how we answer, please add an example to your question. – DBADon Jun 26 at 14:23
  • I had looked at this before without success. I deleted that answer and added a solid working answer. – DBADon Jun 26 at 19:17
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.