Code Generation with PowerShell and TFS

If you use PowerShell to automatically generate code for your project (e.g. during the build process) and you work in TFS-based code, you need to check out existing files before overwriting them. Otherwise the files are read-only and/or not stored in TFS after code generation.

The PowerShell stub script to handle this situation looks like this (assuming the .ps1 file is also inside a TFS directory):

$scriptdir = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
$basepath = $scriptdir.Substring(0, $scriptdir.Length - "path\from\tfs-base\to\script".Length)

$scriptdir stores the directory name of the currently executed script. If the script file is stored inside your TFS project, you can calculate the file path to checkout from $scriptdir.

Next, we call TFS checkout, generate code, and check in again:

& .\tf-checkout.cmd $basepath
... Code generation is here ...
& .\tf-checkin.cmd $basepath

tf-checkout.cmd needs to set the Visual Studio environment variables (as in Visual Studio Command Prompt) to execute the “tf checkout” command:

@echo off
setlocal
call "c:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" x86

echo.
echo checking out...

tf checkout %1path\to\file1.cs
tf checkout %1path\to\file2.cs
...

endlocal

tf-checkin.cmd looks similar:

@echo off
setlocal
call "c:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" x86

echo.
echo checking in...

tf checkin /comment:autogenerated /noprompt %1path\to\file1.cs
tf checkin /comment:autogenerated /noprompt %1path\to\file2.cs

endlocal

Command-line XSLT processor with PowerShell

There are already a lot of XSLT processors out there, such as MSXSL, but without downloading and installing an application you can create your own processor using a couple of PowerShell lines and the System.Xml.Xsl namespace of .Net:

param ($xml, $xsl, $output)

if (-not $xml -or -not $xsl -or -not $output)
{
	Write-Host "& .\xslt.ps1 [-xml] xml-input [-xsl] xsl-input [-output] transform-output"
	exit;
}

trap [Exception]
{
	Write-Host $_.Exception;
}

$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;
$xslt.Load($xsl);
$xslt.Transform($xml, $output);

Write-Host "generated" $output;

What does this code do:

• Declare command-line parameters $xml, $xsl, $output
• Check parameters are passed to the script
• Set a trap to display detailed error message in case an exception is raised
• Load the XSLT file
• Transform XML and write result to output file

automssqlbackup Update 0.30

A user of automssqlbackup notified me of two problems with the program:

• If you try to backup a database running on a named SQL Server instance (server\instance), creation of the log file caused an error as the “\” is handled like a directory separator, and the server directory could not be found.
• If there is an exception during backup (executed by SMO’s SqlBackup method), the exception message is not displayed.

Both problems are fixed in the latest version 0.30 of automssqlbackup.

Note: By default, automssqlbackup performs a full backup on Sundays, and incremental backups on all other days. So if you run automssqlbackup during the week, and you never (fully) backed up your databases before, you will get an exception for the databases without full backups.

automssqlbackup is available for download here.

automssqlbackup Update

The latest version 0.29 of automssqlbackup manages to backup large databases which timed out in previous versions.

File sizes are handled as [long] values now, and exceptions during SMO backups are handled correctly, fixing two bugs in the notification email.

automssqlbackup is available for download here.

Handling SMO SqlBackup Timeout

I hope I have finally tracked down a bug in automssqlbackup which caused an exception when creating backups of huge databases (DB ~40GB, backup file ~4GB): The SqlBackup operation simply timed out as the default command timeout is set to 10 minutes.

I found that there is a property called StatementTimeout in the ServerConnection class, so that you can set the timeout to infinity like so:

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($dbhost)
$conn.StatementTimeout = 0
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)

$bk = New-Object Microsoft.SqlServer.Management.Smo.Backup
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$bk.Database = $dbname
$bk.Initialize = $False
$bk.Incremental = -not $full
$bk.Devices.AddDevice($backupfile, Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$bk.SqlBackup($srv)

Sending PowerShell Transcript by Mail

PowerShell provides the Start-Transcript and Stop-Transcript commandlets to record logging information in a log file.

Creating the log file

The simplest way to create a log file based on the current date is like this:

$now = Get-Date
$logfile = "c:\path\to\log\dir\file-" + $now.ToString("yyyy-MM-dd") + ".log"
Start-Transcript -path $logfile -force

Any output in the PowerShell console will now also be copied to the logfile.

To end logging, simply use

Stop-Transcript

Sending plain text email

After the log file has been closed, we parse it line-by-line, and append each line to a StringBuilder. Finally, the string contents of the StringBuilder is passed to an SmtpClient object to be sent:

$log = Get-Content $logfile

$body = New-Object System.Text.StringBuilder
foreach($line in $log)
{
    [void] $body.AppendLine($line.ToString())
}

$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom, $emailTo, $subject, $body.ToString())

Sending HTML email

Depending on the email client you use, mails containing plain text log files need not necessarily be displayed with a fixed-width font. To force monospace fonts, we need to enclose the text inside a <pre> tag within HTML, and explicitly create an HTML message object:

$body = New-Object System.Text.StringBuilder
[void] $body.AppendLine("<pre>");
foreach($line in $log)
{
    [void] $body.AppendLine($line.ToString())
}
[void] $body.AppendLine("</pre>");

$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $body.ToString())
$msg.IsBodyHTML = $true
$smtp.Send($msg)

Of course, the variables $emailFrom, $emailTo, $subject, and $smtpServer have to be defined according to your needs.

Setting PowerShell window width

Start-Transcript is a PowerShell commandlet which causes PowerShell to log all output to a file. It uses the current window size (i.e. column count) to calculate line breaks in the generated file.

The PowerShell window is 120 characters wide by default. Thus a log file generated by Start-Transcript will reflect this 120 character limit.

You can even change the PowerShell window size using the Get-Host commandlet:

$h = get-host
$win = $h.ui.rawui.windowsize
$win.width  = 120  # change to preferred width
$h.ui.rawui.set_windowsize($win)

However, if PowerShell is invoked from a batch file being executed in cmd, the PowerShell window is only 80 characters wide, since the cmd window is (typically) 80 characters. Calling set_WindowSize will have no effect (apart from an error message), because the PowerShell window cannot have a greater size than the parent window.

To adjust the cmd window width before executing a PowerShell script, use the good old MODE command from DOS era:

mode con cols=120
powershell path-to\myscript.ps1

PowerShell will now inherit the desired window width, and line breaks in the log occur where you expect them from working in the PowerShell window.

automssqlbackup sends notifications as HTML

As automssqlbackup works fine on the machines that I installed it, there’s always opportunity to fine-tune. This time it’s the notification emails being sent.

I modified the PowerShell script to send HTML emails.

The first part of the email contains a table with the list of selected databases, the status (“OK” or message of an exception), the name of the generated file or archive, and the sizes of the .bak and .zip files.

The second part contains the script’s transcript (i.e. the original mail contents).

automssqlbackup is available for download here.

Updating automssqlbackup

A little bit of fine-tuning automssqlbackup:

After changing the zip functionality to SharpZipLib I modified the mail notifications to include the backup schedule (daily, weekly, monthly) in the subject, and also added an indicator whether the databases were backup up successfully.

automssqlbackup 0.27 is available for download here.

Zipping Files with PowerShell

When I developed automssqlbackup, one of the key features was to optionally zip the generated database backup file.

I found solutions like this which essentially create an empty zip archive, then create a Shell.Application object to access this archive, and add files using the CopyHere or MoveHere methods of that object.

However, this approach has some severe drawbacks:

• Zip file creation and access via Shell.Application can collide occasionally, so that the COM object cannot access the zip file

• If such a collision occurs, the shell will display a message box which the user has to confirm/cancel, and the caller (the Powershell script, in this case) is not notified about the error situation

• The MoveHere method does not seem to *move* in the sense that the original file is deleted after being added to the zip archive

• CopyHere and MoveHere execute asynchronously. The caller does not receive any feedback when the operation is complete, and has to query the number of files in the archive to find out.

(short rant: this is automation the Microsoft way. Provide a programming interface, and if things go wrong, display a message box. Provide functionality as asynchronous operation and not tell the caller when it’s finished. If this was *real* automation, the caller would receive an error notification and a success notification from the callee, and the caller would decide how to react)

Since these effects showed up soon after I tested my script on different machines, it was clear that zipping had to be solved more reliably: using SharpZipLib.

First SharpZipLib is loaded into the Powershell script:

[System.Reflection.Assembly]::LoadFrom("C:\path-to\ICSharpCode.SharpZipLib.dll")

I found there are 2 simple ways to create a zip archive and add a file to it:

Using ZipFile.Create() and *Update()

$zip = [ICSharpCode.SharpZipLib.Zip.ZipFile]::Create($zipname)
$zip.BeginUpdate()
$zip.Add($filename)
$zip.CommitUpdate()
$zip.Close()

Quick and easy, but with a drawback: the filenames are stored including their original path information.

Using FastZip.CreateZip()

$zip = New-Object ICSharpCode.SharpZipLib.Zip.FastZip
$zip.CreateZip($zipname, $filenamedir, $false, "\.bak$")

$filenamedir is the base directory of all files to be included in the zip, followed by the recursive flag.

The final parameter is a bit tricky: it is named FileFilter, but it is not a DOS-like filter (“*.*”), but rather a regular expression being evaluated on each file. In the example above, all files with extension .bak are selected.