Calling Reporting Services’ CreateReport() from Powershell

The specification for CreateReport() provided by Microsoft is inaccurate, as far as I can tell.  In c# it won’t matter, but it does in Powershell, due to the oddities in how Powershell handles empty arrays.

The specification says that CreateReport() will return an empty array if there are no errors or warnings.  I believe that in SQL Server 2008 it actually returns a null.  Maybe later I’ll find a flaw in my thinking…but maybe not!

Look at this code:

$result = @($reportingServicesProxy.CreateReport($reportFile.BaseName, $serverPath, $true, $reportDefinition, $null))
write-host $result.Count
if ($result[0] -eq $null) {write-host "result is null"} 

According to Powershell rules and the CreateReport specification, this code should output a zero for $result.Count if the call succeeded with no errors or warnings.  However, $result.Count turns out to be one, with $result[0] being $null.  This is not the behavior of returning an empty array.  It is the behavior of a function returning null.

You might ask, “who cares?”

Well, I do.  Look at the code below:

foreach( $warning in $result)
{
	if ($warning.Severity  -eq "Warning")
	{
		...
	}
}

If CreateReport() returned an empty array, this code would work properly.  But since it returns a null, this code ends up trying to access $warning[0].Severity, which doesn’t exist.  The deployment script crashes with an error of “Property ‘Severity’ cannot be found on this object. Make sure that it exists.”

I wish Microsoft’s specification were correct, but since it’s not, here’s code that is messier but works:

$result = $reportingServicesProxy.CreateReport($reportFile.BaseName, $serverPath, $true, $reportDefinition, $null)
if ($result -ne $null)
{
		foreach( $warning in $result)
		{
			if ($warning.Severity -eq "Warning")
			{
				...
			}
		}
}

Hopefully this will help someone out.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: