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

• rnunez

  Right there with Babe

  Points: 762

  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!

• Laerte Poltronieri Junior-367636

  Hall of Fame

  Points: 3311

  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]

• rnunez

  Right there with Babe

  Points: 762

  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!

• Ronen Ariely

  SSChasing Mays

  Points: 613

  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