2008-08-14

ISO 8601 date formatting using PowerShell

My favorite timestamp is the ISO 8601 format [YYYY]-[MM]-[DD]T[hh]:[mm]:[ss.sss]Z.
The ISO 8601 standard is very well described in a Wikipedia article (ISO 8601).

I do miss a ISO formatting option in .NET/PowerShell, but using DateTime formatting, an acceptable result is found:
$theDate = Get-Date
"{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffff'Z'}" -f $theDate.ToUniversalTime()
$theDate.ToUniversalTime().ToString( "yyyy-MM-ddTHH:mm:ss.fffffffZ" )
The formatting is shown twice using different syntax.
I've chosen seven digits on the second because I have logging in mind while working with time stamp. I have (once) seen that three digits wasn't enough - it was in a technical database log on a z/Server "mainframe".

A execution gives this result:
2008-01-13T10:02:36.8992896Z
I would like to get the week of the year, but again this is a problem. Is it because a week number is more used in Europe than on the other side of the Atlantic?
Thomas Lee has tried (eggheadcafe conversation), and I tried his examples. I regret to say he's right – again.

Using the formatting specifier 'o' (oscar) from the DateTimeFormatInfo Class to the CmdLet Get-Date will generate a output with seven digits, but also a a part with a reference to the timezone. Mark Puckett has made a comment to this post about this possibility. And he is right that this specifier wil give a output that is very precise. But I choose not to use this as I go for UTC time in a log as I think it makes the log entries more readable on senior management level.

History

2008-01-14 Blog post created
2017-02-05 Section on -Format o added inspired by comment by Mark Puckett.

SQL Server Agent PowerShell job step

This seems like a valid and simple way to execute a PowerShell script in a SQL Server Agent job step.
The script I've been testing with looks like this (ExecuteablePS.ps1):
param( [string]$script:myParam = $( throw "Error: Command statement is missing." ) )

Write-Output "Hello to output."
Write-Host "Hello to host."

Write-Output "The parameter 'myParam' was given the value '$myParam'."
It is on purpose that the script requires a parameter, while most - if not all - my automation scripts is parametrized on the physical elements like server (host).
The script also demonstrates that both the Output stream and the Host stream is caught by SQL Server Agent.

The SQL Server Agent job step is defined as a CmdExec step.
The step only has one line:
powershell.exe -NoLogo -NoProfile -Command "&'C:\SQLAdmin\Script\ExecuteablePS.ps1' 'CoolValue'"
I spend some time getting the " and ' right...

When the job is executed, the history is:
Executed as user: Sandbox\Frodo. Hello to output. Hello to host. The parameter 'myParam' was given the value 'CoolValue'. Process Exit Code 0. The step succeeded.

I use CmdExec in stead of PowerShell as job step type, so that the solution can be used below SQL Server 2008.
Also I like to use powershell.exe instead of sqlps.exe. This makes the solution valid on PowerShell v2.

2008-08-13

Write to user defined file with PowerShell

I'm working on a general solution for PowerShell job execution on SQL Server Agent. This generates a lot of small tasks, which mostly will be described here with a solution.

Right now it's about logging, and for this I'm thinking in several directions.
This task is for a solution using files, that are defined in the executing script.

The script writes to the same file in each execution, but the file will only contain the last execution:
$FileName = $MyInvocation.MyCommand.Name.split(".")[0] + ".Log"
Write-Host $FileName
"Hello to file..." > $FileName
"Another hello to file" >> $FileName

The execution generates this content (Get-Content Write2File.Log):
Hello to file...
Another hello to file
Another execution gives the same content - two lines, not four.

PowerShell include

Inclusion of a script file in a PowerShell script is done with dot-include.

Given the file "Include.ps1":
Write-Output "Hello from Include.ps1"
and the file "Test-Include.ps1":
Write-Output "Hello from Test-Include.ps1"

.'D:\My Documents\SQLAdmin\Script\Include.ps1'


An execution from a PowerShell prompt in the path "D:\My Documents\SQLAdmin\Script":
PS D:\My Documents\SQLAdmin\Script> .\Test-Include.ps1
Hello from Test-Include.ps1
Hello from Include.ps1
PS D:\My Documents\SQLAdmin\Script>

An execution from another path:
PS D:\> .'D:\My Documents\SQLAdmin\Script\Test-Include.ps1'
Hello from Test-Include.ps1
Hello from Include.ps1
PS D:\>

An execution from Windows Shell (cmd.exe):
H:\>powershell.exe .'H:\My Documents\SQLAdmin\Script\Test-Include.ps1'
Hello from Test-Include.ps1
Hello from Include.ps1

H:\>

These examples are delibrately using a path with a space in a folder name.