How to output everything to a log file in Invoke-SqlCmd

  • rnunez

    Right there with Babe

    Points: 762

    I have powershell script to run database scripts below, but I can't get it to output to my logfile the "..x row(s) affected"(the messages that you see in SSMS) output whether I run an update, delete, etc against it. Do you know what parameters I am missing?

    I've seen this work in the regular sqlcmd in a batch file, but don't know why it doesnt work in the invoke-sqlcmd.

    $logfile = Join-Path "logs" $("deployment.txt")

    Invoke-SqlCmd -InputFile $script -Database $database -ServerInstance $server -OutputSqlErrors:$true -Verbose 2>&1 | Tee-Object -variable thost | Add-Content $logfile

    write-host $thost

    Thanks!

  • Laerte Poltronieri Junior-367636

    Hall of Fame

    Points: 3311

    Hi rnunez,

    I have tried this for a long time. According to the help of invoke-sqlcmd

    Invoke-Sqlcmd does not return message output, such as the output of PRINT statements, unless you use the PowerShell -Verbose parameter.

    even using -verbose, it will only show the print command. So one way is you change the script to use print and @@rowcount, something like :

    insert into teste (codigo) values ('test')

    PRINT 'RowsAffectd - ' + CAST(@@ROWCOUNT AS VARCHAR(10))

    insert into teste (codigo) values ('test')

    PRINT 'RowsAffectd - ' + CAST(@@ROWCOUNT AS VARCHAR(10))

    insert into teste (codigo) values ('test')

    PRINT 'RowsAffectd - ' + CAST(@@ROWCOUNT AS VARCHAR(10))

    delete from teste

    PRINT 'RowsAffectd - ' + CAST(@@ROWCOUNT AS VARCHAR(10))

    and use start-transcript and stop-transcript

    Start-Transcript c:\temp\output.txt

    invoke-sqlcmd........-verbose

    Stop-Transcript

    This is the way I did. Maybe could be better way , but I still do not know 🙁

    I really hope someone has a better solution 🙂

    $hell your Experience !!![/url]

  • rnunez

    Right there with Babe

    Points: 762

    Sorry for the delay in the post. For some reason, I didn't get an email when you replied.

    Thanks, I will your suggestion!

  • Ronen Ariely

    SSChasing Mays

    Points: 613

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply