This script provides my PowerShell translation of C# code created by the authors of Microsoft SQL Server 2008 Analysis Services Unleashed.  I have tested this code on 64-bit Windows 7 running SQL Server 2008 R2 with Analysis Services, and the Windows PowerShell version 2 IDE (Integrated Development Environment).  This code requires the sample “Food Mart 2008” database available from the authors’ website at http://www.informit.com/store/product.aspx?isbn=0672330016

I shared this code as part of my data mining blog.  I have been reviewing this book, and I made comments on this code at http://www.marktab.net/datamining/2010/07/22/adomd-net-with-powershell-2-0-and-c-4-0/

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.

PowerShell
Edit|Remove
# 3330_AdomdErrorHandling.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection] 


################################################################
function AdomdCellErrors 
{
	write-host "AdomdCellErrors"
	   
	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Datasource=localhost; Initial Catalog=foodmart 2008;")
	$con.Open()

	# Create a command that contains a calculated member with an error.
	# The Method Max accepts only numerical values as parameters, and we try to pass strings.
	$command = $con.CreateCommand()
	$command.CommandText = "WITH member measures.x AS Order('string',4) select {x} ON COLUMNS FROM [Warehouse and Sales]"
	$cellset = $command.ExecuteCellSet()

	# Iterate all the cells and print their values
	foreach ($cell in $cellset.Cells)
	{
		try
		{
			[int] $test = $cell.value
			write-host $cell.Value
		}
		catch [Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]
		{
			write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]"
			# If there was an error, iterate the error collection and print the error message
			write-host " Error in the cell "
			foreach ($cellError in $_.Exception.Errors)
			{
				write-host $cellError.Message
			}
		}
		catch [Microsoft.AnalysisServices.AdomdClient.AdomdException]
		{
			write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdException]"
			write-host $_.Exception.Message
		}
	}

	# Close the connection
	$con.Close();
}


################################################################
function AdomdErrorHandling 
{
	write-host "AdomdErrorHandling"
	
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Datasource=Fake_host; Initial Catalog=foodmart 2000;")
	try
	{
		# Open a connection to the server
		$con.Open()

		# do some operations
		# ...
		
	}
	catch [Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException]
	{
		write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException]"
		write-host $_.Exception.Message
	}
	catch [Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]
	{
		write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]"
		# If there was an error, iterate the error collection and print the error message
		foreach ($cellError in $_.Exception.Errors)
		{
			write-host $cellError.Message
		}
	}
	catch [Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException]
	{
		write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException]"
		write-host $_.Exception.Message
	}
	catch [Microsoft.AnalysisServices.AdomdClient.AdomdCacheExpiredException]
	{
		write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdCacheExpiredException]"
		write-host $_.Exception.Message
		# refresh the controls
		# ...
	}
	finally
	{
		write-host 'finally'
		# Close the connection
		if ($con.State -eq [System.Data.ConnectionState]"Open")
		{
			$con.Close()
		}
	}
}


################################################################
# Main
AdomdCellErrors
AdomdErrorHandling