Listing Fonts used in an SSRS Report (.rdl)

June 18, 2013

When deploying SSRS reports, one needs to check whether the fonts used in a .rdl report are actually installed on the SSRS server, as a developer may select fonts installed by software that is not available on the server (I’m looking at YOU, Arial Narrow).

A font declaration inside an .rdl file looks like this

<Style>
    <FontFamily>Arial Narrow</FontFamily>
    <FontSize>8pt</FontSize>
</Style>

and those elements reside in the

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">

namespace.

First, we load the .rdl as XML and declare the namespace

[xml] $rdl = get-content "c:\path\to\my.rdl"
$ns = @{ r ="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" };

As we need to select all Style elements containing the FontFamily AND the FontSize child elements, we query the XML

$rdl.Report.Body | select-xml "//r:Style[r:FontFamily and r:FontSize]" -namespace $ns

and then select the text values

    | foreach-object { "$($_.Node.FontFamily) $($_.Node.FontSize)" }

This returns a list of font specifications like this

Arial Narrow 8pt
Arial Narrow 8pt
Arial Narrow 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt

Grouping the list and counting the items

    | Group-Object | select-object name, count

returns

Name                                  Count
----                                  -----
Arial Narrow 8pt                         20
Arial Black 8pt                           6

So this is the script:

[xml] $rdl = get-content "c:\path\to\my.rdl"
$ns = @{ r ="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" };
$rdl.Report.Body 
    | select-xml "//r:Style[r:FontFamily and r:FontSize]" -namespace $ns
    | foreach-object { "$($_.Node.FontFamily) $($_.Node.FontSize)" }
    | Group-Object | select-object name, count

Deploying SSRS Reports with PowerShell

June 17, 2013

Deploying SSRS Reports can be a painful task. 2012 is a bit better than 2008. It’s even worse if you do not have BIDS installed. And more worse if you use shared data sources. (telling from my recent experience)

Fortunately I found this PowerShell script, but unfortunately it lacks any sort of documentation.

After a bit of googling, and removing the lines

if ([Convert]::ToBoolean($ConnProps.IntegratedSecurity)) {
    $Definition.CredentialRetrieval = 'Integrated'
}

which always caused the error

ForEach-Object : Die IntegratedSecurity-Eigenschaft wurde für dieses Objekt nicht gefunden. Stellen Sie sicher, dass sie vorhanden ist. Bei D:\projects\wms\Deploy-SSRSProject.ps1\Deploy-SSRSProject.ps1:150 Zeichen:19 +     ForEach-Object <<<<  {
+ CategoryInfo          : InvalidOperation: (.:OperatorToken) [ForEach-Object], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFoundStrict,Microsoft.PowerShell.Commands.ForEachObjectCommand

ForEach-Object : Property ‘IntegratedSecurity’ cannot be found on this object; make sure it exists
At D:\projects\wms\Deploy-SSRSProject.ps1\Deploy-SSRSProject.ps1:150 char:19
+     ForEach-Object <<<<  {
+ CategoryInfo          : InvalidOperation: (.:OperatorToken) [ForEach-Object], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFoundStrict,Microsoft.PowerShell.Commands.ForEachObjectCommand

because my project uses SQL Authentication rather than integrated security to log on to the database.

I got the script to run using the following PS code:

$pwd = ConvertTo-SecureString "ThePassWord" -asplaintext -force
$cred = new-object System.Management.Automation.PSCredential "TheUser", $pwd
& ./Deploy-SSRSProject.ps1 -path "C:\path\to\my.rptproj" 
    -configuration "Debug" -credential $cred -verbose

where “Debug” is a configuration defined in the .rptproj.

After running the script, all .rdl files had the current timestamp on http://localhost/Reports/Pages/Folder.aspx?ItemPath=%2fMyProject&ViewMode=Detail.

The shared data source in the Data Sources directory are not being updated because the command line parameter -OverwriteDataSources is not passed to the New-SSRSDataSource function.


Resolving Assembly Bindings in Powershell

July 2, 2011

After configuring PowerShell to use an assembly’s config file and successfully loading assemblies and their dependencies, I found that assemblies referenced by the assembly’s config file were not found by the loader, even though the same referenced assemblies were already loaded by the original assembly.

To understand what is going on during Assembly.Load and Assembly.LoadFrom, read this MSDN article explaining the different Load Contexts that .Net uses for the various Assembly.Load*** methods.

This answer on StackOverflow provides C# code implementing the AssemblyResolve event for the current AppDomain to retrieve assemblies that the runtime does not find directly.

The original code stores a list of assembly names and their actual paths, and the AssemblyResolver class loads an assembly if it is found in this list.

I added code to handle binding assemblies in pre-defined paths, and posted the resulting code under the AssemblyResolver package here.

To use this assembly resolver in PowerShell, first load the assembly containing the assembly resolver, define known assemblies by adding assembly filenames and/or assembly paths, and then load the assemblies you originally wanted to load.


Invoke .Net 4 assembly with the assembly’s .config file using PowerShell

June 29, 2011

In a project developed using .Net 4, we needed to make some functionality available for scripting in PowerShell.

  • The first task was to configure PowerShell to be able to load .Net 4 assemblies:

The $pshome variable is the directory where the powershell.exe and its configuration file powershell.exe.config reside (the .config does not seem to be required on Win7 as it was not on my system). On 32-bit systems, the path is C:\Windows\System32\WindowsPowerShell\v1.0, 64-bit systems have C:\Windows\SysWOW64\WindowsPowerShell\v1.0.

Create a file called powershell.exe.config in a temp directory, and paste this code (found on SO)

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0.30319"/>
    <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>

Open a command prompt as administrator, and copy the file to PowerShell’s directory

copy .\powershell.exe.config
  c:\windows\system32\windowspowershell\v1.0\powershell.exe.config

Restart PowerShell, and you are able to load .Net 4 assemblies.

  • Next, we need to set up the .Net environment so that it uses the assembly’s configuration file instead of PowerShell’s.

Since the executable that is loading the assembly is powershell.exe, it is thus looking for the powershell.exe.config. To use the correct config file, we need to set the current AppDomain’s APP_CONFIG_FILE property (found on SO):

$filename = "c:\path\to\MyAssembly.dll"
[System.AppDomain]::CurrentDomain.SetData(
    "APP_CONFIG_FILE", $filename + ".config")
$assembly =
    [System.Reflection.Assembly]::LoadFrom($filename)

Exploring IIS with PowerShell

April 19, 2011

I need to write a Web Installer with a bit more complex logic than the standard setup provides, and came across pages like Creating Sites and Virtual Directories Using System.DirectoryServices that deal with .Net classes I have never seen before. (Note: while the page deals with IIS 6, the classes also work on IIS 7 if the IIS 6 Management Compatibility feature is installed)

Trying to figure out what System.DirectoryServices can accomplish, I chose PowerShell to poke around in IIS.

PowerShell needs to be run as administrator, otherwise you will get an “access denied” error (unless you provide login information in the DirectoryEntry constructor)

First, we load the DirectoryServices.dll:

> [system.reflection.assembly]::loadwithpartialname(
    "System.DirectoryServices.dll")

Next, we can retrieve the IIS service entry using a DirectoryEntry

> $service = new-object system.directoryservices.directoryentry(
    "IIS://127.0.0.1/W3SVC")
> $service.SchemaClassName
IIsWebService

See this chart for the IIS Admin Object Hierarchy.

The children of the IIsWebService are

> $service.Children | select-object  SchemaClassName, Path, AppRoot
SchemaClassName       Path                                    AppRoot
---------------       ----                                    -------
IIsFilters            IIS://127.0.0.1/W3SVC/FILTERS
IIsApplicationPools   IIS://127.0.0.1/W3SVC/APPPOOLS
IIsWebInfo            IIS://127.0.0.1/W3SVC/INFO
IIsWebServer          IIS://127.0.0.1/W3SVC/1                 {}

Let’s get the web server root

> $root = new-object system.directoryservices.directoryentry(
    "IIS://127.0.0.1/W3SVC/1/Root")
> $root.Children | select-object  SchemaClassName, Path, AppRoot

SchemaClassName   Path                                    AppRoot
---------------   ----                                    -------
IIsWebVirtualDir  {D:\MyVirtDir}                          {}
IIsWebDirectory   IIS://127.0.0.1/W3SVC/1/Root/MyWebApp   {/LM/W3SVC/1/ROOT/MyWebApp}

An IIsWebDirectory has a defined AppRoot property, whereas an IIsWebVirtualDir has a file system directory as Path, but no AppRoot.

You can instantiate a DirectoryEntry for both IIsWebDirectory and IIsWebVirtualDir using the value of the ADsPath property:

> $root.Children | select-object  SchemaClassName,  ADsPath, Path
SchemaClassName   ADsPath                                  Path
---------------   -------                                  ----
IIsWebVirtualDir  IIS://127.0.0.1/W3SVC/1/Root/MyVirtDir   {D:\MyVirtDir}
IIsWebDirectory   IIS://127.0.0.1/W3SVC/1/Root/MyWebApp    IIS://127.0.0.1/W3SVC/1/Root/MyWebApp

Searching for String Literals in T-SQL Code

December 25, 2010

If you have ever worked in a multi-language (i.e. localization-aware) project, you know that you should avoid string literals embedded in code.

To make sure that no untranslatable error messages, warnings or other text that might end up in the user interface is embedded in T-SQL code (MS SQL Server stored procedures, functions, and triggers), you’d need to search each of the code blocks for the string delimiter ‘ (single quote).

I’ll sketch a solution called tsqlfindstrings implemented in PowerShell using SMO to access the code stored in a MS SQL Server database, and .Net regular expressions to find string literals. The script tsqlfindstrings.ps1 needs to be configured similarly to my previous PowerShell solution automssqlbackup.

First, the SQL Server-specific SMO libraries are loaded

$mspath = "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\"
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.ConnectionInfo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.Smo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.SmoExtended.dll")

and connection parameters $dbhost, $dbname, $username, and $password are set.

Next, we define string literals to be excluded from the search results, either complete strings or string beginnings, as string arrays:

$excludedStrings = @(" (", ")", "%", "*", " - ", ", ", "(", "true", "false", " ", """", "=""",
  "uniqueidentifier", "datetime", "int", "decimal", "bit", "float", "Z",  "string", "boolean", "guid")
$excludedBegins = @("'")

The example shows T-SQL keywords as excluded strings and the single quote as first character in a string literal (both used for dynamic SQL, so we want to ignore these).

We connect using the standard SMO objects ServerConnection and Server:

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
[void] $srv.Initialize($false) 
[void] $srv.Refresh() 
[void] $srv.SetDefaultInitFields($true)

to find our database $db in $srv.Databases.

In its current version, the script checks these SMO properties for string literals:

  • Tables.Checks.Text
  • Tables.Columns.Default
  • Views.TextBody
  • StoredProcedures.TextBody
  • UserDefinedFunctions.TextBody
  • Triggers.TextBody

If a single quote is found in the property values, the Extract subroutine is called to extract the string literal and match it with the defined excluded strings:

foreach($m in [RegEx]::Matches($s, "'(.+?)'[^']") |
  Where-Object { ($excludedStrings -notcontains $_.Groups[1]) })
{
  $g = $m.Groups[1].Value
  $do = $true
 
  foreach($b in $excludedBegins)
  {
    if ($g.StartsWith( $b ))
    {
      $do = $false
    }
  }

  if ($do)
  {
    Write-Host ($type + " " + $name + ": '" + $g + "'")
  }
}

The first released version of tsqlfindstrings is available for download here.


Preparing web.config for Deployment using PowerShell

September 7, 2010

After you build your Web Project in Visual Studio, it would be nice to automatically process the developer’s web.config to remove any sensitive information, such as login credentials, and other information that sits in the config file for debugging or testing purposes.

As described in the previous entry, the build batch generates a compiled web application, and renames the web.config to web.publish.config, to avoid accidentally overwriting an existing web.config during an upgrade.

This PowerShell script opens the web.publish.config, replaces the connection string with a place holder, and removes two config sections (the XML manipulations shown are for illustration only):

$config = "c:\path-to\published\app\web.publish.config"

[xml]$cfg = Get-Content $config

"$config loaded"

$node = $cfg.SelectSingleNode("//connectionStrings/add[@name='default']")
$node.connectionString = "Data Source=LOCALHOST;Initial Catalog=THECATALOG;Persist Security Info=True;User ID=USERNAME;password=PASSWORD"

$node = $cfg.SelectSingleNode("//appSettings")
if ($node) { $node.RemoveAll() }

$node = $cfg.SelectSingleNode("//system.web/pages/controls")
if ($node) { $node.RemoveAll() }

$sr = New-Object System.IO.StreamWriter $config
$cfg.Save($sr)
$sr.Close()

"$config saved"

The script loads the web.config, performs some XML operations (notice the inline addressing of the connectionString attribute), and saves the XML document to its original name.


Code Generation with PowerShell and TFS

October 15, 2009

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

September 15, 2009

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

August 1, 2009

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.


Follow

Get every new post delivered to your Inbox.