Post reply

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

  • May 25, 2010 at 6:47 am

    #221908

    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!

  • May 30, 2010 at 7:36 pm

    #1174369

    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]

  • June 7, 2010 at 12:16 pm

    #1177186

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

    Thanks, I will your suggestion!

  • September 25, 2018 at 2:36 pm

    #2007010

    The following post should give a full explanation on the topic:
    http://ariely.info/Blog/tabid/83/EntryId/214/How-to-control-the-output-of-a-QUERIES-including-the-PRINT-statement-and-ERROR-massages-using-PowerShell.aspx

    Senior consultant and architect, data platform and application development, Microsoft MVP.

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

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