Link to home
Start Free TrialLog in
Avatar of D B
D BFlag for United States of America

asked on

PowerShell Force Output to Transcript File

I have a script I created that contains considerable output to the console using Write-Host. I start a transcript at the beginning of the script and all output from Wrote-Host is contained in the transcript file.
However, yesterday I made some revisions (primarily to not present message boxes) if the account that executed the script is disconnected, and sceduled the script to be executed as a job under SQL Agent. This morning, when I got in and examined the transcript file, it contained the following, with none of the output from Write-Host. I have executed the script multiple times manually, and disconnected from the machine without any loss of output, so not sure what I need to do, but I need to force the output to the transcript file, along with any output that the script might generate. Is there something I need to use instead of Write-Host?
**********************
Windows PowerShell transcript start
Start time: 20150311180206
Username  : computername\_sqlagent
Machine	  : computername (Microsoft Windows NT 6.1.7601 Service Pack 1)
**********************
**********************
Windows PowerShell transcript end
End time: 20150312013540
**********************

Open in new window

Avatar of Joshua Grantom
Joshua Grantom
Flag of United States of America image

When its run from the sql agent, I believe it's not opening a command window so there is nothing to transcribe. What you may need to do instead is output the results and append to a log file like so

$time = Get-Date -Format "MM-dd-yyyy_HH-mm-ss"
$logfile = "C:\logs\SQL_Log_$($time).log"
My command >> $logfile

Open in new window


or you can use

$time = Get-Date -Format "MM-dd-yyyy_HH-mm-ss"
$logfile = "C:\logs\SQL_Log_$($time).log"
Write-Host "My Stuff" | Out-File $logfile -Append -Force

Open in new window


If you could post what you are doing in the script, we may be able to help out a little more.
In general, don't use the transcript option. You should use Out-File, Write-Output and the like. Write-Host is only useful for the interactive console window; any other PS host might or might not take output with Write-Host.
Avatar of D B

ASKER

Basicly I am using write-host to output data to the console. It may be straight text such as:
write-host "starting the process"
a combination of text and a variable such as:
write-host "executing step $stepname"
or a variable such as:
write-host "$message"

In any case, is there a way to determine if a command window is available (e.g. it was run from SQL Agent)?
I assume looking at $host.ui.rawui will help. It has a window title and position etc., so having those should tell it is a host with window.
a way around it may be using a different option in SQL Agent

Type: Operating System (CmdExec)

and using something like this

powershell -windowstyle minimized -c "powershell -c c:\scripts\sqlscript.ps1 -verbose >> C:\logs\mysqllog.log 2>&1"
ASKER CERTIFIED SOLUTION
Avatar of D B
D B
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's correct.
Avatar of D B

ASKER

Should be "... | Tee-Object -File $TranscriptFile" (note -File not -Path).
If we get picky, then it should be
   Write-Output $message | Tee-Object -FilePath $TranscriptFile -Append

Open in new window

-File is specific enough to match -FilePath, so that works ;-).
Avatar of D B

ASKER

Doing more research, using tee-object was a more acceptable solution than those provided by other experts.