0

There's a question on this already (here). But I'm hoping that because I'm using PowerShell 2, and SQL Server 2008, that I might get different answers if I ask again.

Basically, I have a SQL Server Agent job, which runs a cmd script like so:

powershell "&D:\SQL\Job\JobDir\Upload.ps1 (various arguments including log file name)"

When it runs start-transcript, the file is created, but nothing except a header is written to the file. However, when I run on the command line, everything is logged properly.

I've already got lots of logic to manage old log files in the powershell script, and don't want to just use the default logging that SQL Server Agent provides.

To write out info, I use invoke-sqlcmd with the verbose flag, and also write-message and write-error.

Thanks for any pointers! Sylvia

Community
  • 1
  • 1
Sylvia
  • 2,578
  • 9
  • 30
  • 37

4 Answers4

0

Permissions were not an issue for me. I think there's ways of manipulating the output from powershell very exactly, with all the different output streams (error, verbose, etc), but instead of getting into those details, I just used the default logging that SQL Server agent provides for each job step. It works okay. I still use the logic that I wrote to manage and archive log files in my script, I can do that even though the log file itself is generated by SQL Server.

Sylvia
  • 2,578
  • 9
  • 30
  • 37
0

I believe that start-transcript does not work when a console was not started (e.g. in a service): outputs are not generated and that would explain why the file is empty.

See start-transcript causes script to fail in a background job

Community
  • 1
  • 1
Cyann
  • 23
  • 3
0

As already stated above the Start-Transcript will create a file in the location specified (as long as agent has permission to create files there). However nothing will be streamed to the transcript file because technically no console window is running.

When running a powershell file through the SQL agent (pre-2012) like C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe C:\myscript.ps1 "SOMEPARAMETER"

I just use the SQL Agent's native Output File option in the SQL steps advanced properties like C:\LOGGING\myfile_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt and skip powershell's transcript ability.

Include Write-Host where necessary. Write-Host "rn" will create new line appropriately. Also include things like -Verbose where allowed and try / catch with error output and the file will record appropriately.

GP Van Eron
  • 21
  • 1
0

start-transcript creates a text file

it is likely that the permissions under which the agent job is running does not have permissions to create the text file where powershell wants to create it

Jimbo
  • 2,529
  • 19
  • 22
  • Thanks for the comment. I modified the question to make it clearer, but the file is actually being created, just almost nothing written to it. – Sylvia Apr 03 '12 at 13:42
  • I would still suspect permissions - try running the job under your own credentials. – Jimbo Apr 03 '12 at 18:22