May 25, 2010 at 6:47 am
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
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
June 7, 2010 at 12:16 pm
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
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy