How to output everything to a log file in Invoke-SqlCmd
-
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!
-
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
-
Sorry for the delay in the post. For some reason, I didn't get an email when you replied.
Thanks, I will your suggestion!
-
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.aspxSenior 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