Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 3, 2008

Discover Analysis Service's Properties using Windows PowerShell and AMO

By Muthusamy Anantha Kumar aka The MAK

Part I of this article series “Microsoft Windows PowerShell and SQL Server 2008 AMO” illustrated how to install Windows PowerShell and connect to an Analysis Service Servername. Part II of this article illustrates how to use Windows PowerShell and AMO to get the various Server properties of SQL Server Analysis Service.

Let’s connect to Analysis Server using Windows PowerShell as shown below.

[Reflection.Assembly]::LoadWithPartialName
  ("Microsoft.AnalysisServices")
$servername=New-Object Microsoft.AnalysisServices.Server
$servername.connect("PowerServer3\SQL2008")

Note: PowerServer3 is the Analysis Server with named instance SQL2008. Please replace the server name with Analysis server name in your own environment.

Now let’s query all the methods available for the Class ‘Microsoft AnalysisService.Server’. This can be achieved by using the Windows PowerShell cmdlet “get-member” with the parameter “ –membertype methods” as shown below.

$servername |get-member -membertype methods

This would display a result similar to the results shown below. [Refer Figure 1.1]

Partial Result:

Attach
BeginTransaction
CancelCommand
CancelConnection
CancelSession
Clone
CommitTransaction
ConcatenateCaptureLog
Connect
CopyTo
CreateObjRef
Disconnect
Dispose
Drop
EndXmlaRequest
Equals
Execute
ExecuteCaptureLog
GetConnectionState
GetDependents
GetHashCode
GetLastSchemaUpdate
GetLifetimeService
GetReferences
GetType
GetUpdateOverwrites
InitializeLifetimeService
NotifyTableChange
Reconnect
Refresh
Restore
RollbackTransaction
SendXmlaRequest
StartXmlaRequest
Submit
ToString
Update
UpdateObjects
Validate

get-member with -membertype methods results
Figure 1.1

Now let’s query all of the properties available for the Class ‘Microsoft AnalysisService.Server’. This can be achieved using Windows Powershell cmdlet “get-member” with parameter “ –membertype Properties”.

$servername |get-member -membertype properties

This displays a result similar to the results shown below. [Refer Figure 1.2]

Partial Result:

Annotations
Assemblies
CaptureLog
CaptureXml
Connected
ConnectionInfo
ConnectionString
Container
CreatedTimestamp
Databases
Description
Edition
EditionID
ID
IsLoaded
LastSchemaUpdate
Name
OwningCollection
Parent
ProductLevel
ProductName
Roles
ServerProperties
SessionID
SessionTrace
Site
SiteID
Traces
Version


Figure 1.2

Now let’s query some of the SQL Server Analysis Service properties by using the Windows PowerShell cmdlets below.

$servername.ConnectionString
$servername.Edition
$servername.ProductLevel
$servername.databases | select Name

The output is similar to the results shown below. Refer Figure 1.3

PS C:\> $servername.ConnectionString
PowerServer3\SQL2008
PS C:\> $servername.Edition
Evaluation
PS C:\> $servername.ProductLevel
RTM
PS C:\> $servername.databases | select Name
Name
----
Adventure Works DW 2008
MyCube
Test

query SQL Server Analysis Service properties results
Figure 1.3

Let’s create a Windows PowerShell script that displays some of the properties of any SQL Server Analysis server that we pass as a parameter.

Create the folder C:\AMOScripts using the following Windows PowerShell cmdlet as shown below. [Refer Figure 1.4] We could store all the AMO related scripts under the folder.

New-Item –Path C:\ -Name AMOScripts -Type directory

Create the folder C:\AMOScripts using the following Windows PowerShell cmdlet
Figure 1.4

Let’s create a file C:\AMOScripts\get-SSASproperty.ps1 using notepad and copy and paste the code below to that file and save it. [Refer Figure 1.5]

#Objective: To get properties of the given Analysis Server
#Created by: MAK
#Create Date: 9/9/2007
param
([string] $Servername)
## Add the AMO namespace
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName) 
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}
else
{
Write-host "Connection String = "  $server.ConnectionString
Write-host "Edition           = "  $server.Edition
Write-host "Version           = "  $server.version
Write-host "SessionID         = "  $server.SessionID
Write-host "Assemblies        = "  $server.Assemblies
Write-host "CreatedTimestamp  = "  $server.CreatedTimeStamp
Write-host "LastSchemaUpdate  = "  $server.LastSchemaUpdate
Write-host "Roles             = "  $server.Roles
Write-host "Connected         = "  $server.Connected
Write-host "SessionID         = "  $server.SessionID
Write-host "Databases         = "  $server.databases

}

Please copy the script from here.

create a file C:\AMOScripts\get-SSASproperty.ps1
Figure 1.5

Now let’s try to execute the get-ssasproperty.ps1 by passing the SQL Server 2005 Analysis server name as shown below. [Refer Figure 1.6]

set-location AMOScripts
./get-ssasproperty.ps1 "BUS-INTEL"

Note: BUS-INTEL is the SQL Server 2005 Analysis Server with default Analysis Service instance. Please replace the server name with the Analysis server name in your environment.

Result

Connection String =  BUS-INTEL
Edition           =  Enterprise
Version           =  9.00.3042.00
SessionID         =  E2AE10B1-A163-43C8-BF41-40817643FB7B
Assemblies        =  System VBAMDXINTERNAL VBAMDX ExcelMDX
CreatedTimestamp  =  11/29/2008 4:47:20 AM
LastSchemaUpdate  =  11/29/2008 4:47:20 AM
Roles             =  Administrators
Connected         =  True
SessionID         =  E2AE10B1-A163-43C8-BF41-40817643FB7B
Databases         =  Adventure Works DW

execute the get-ssasproperty.ps1
Figure 1.6

Now let’s try to connect to a SQL Server 2008 Analysis Server as shown below. [Refer Figure 1.7]

./get-ssasproperty.ps1 "PowerServer3\SQL2008"

Note: PowerServer3 is the Analysis Server with named instance SQL2008. Please replace the server name with the Analysis server name in your environment.

Result

Connection String =  PowerServer3\SQL2008
Edition           =  Evaluation
Version           =  10.0.1600.22
SessionID         =  03E59B7D-3A03-4989-99D3-48AA2DB4E1BF
Assemblies        =  System VBAMDXINTERNAL VBAMDX ExcelMDX
CreatedTimestamp  =  10/28/2008 11:06:19 PM
LastSchemaUpdate  =  10/28/2008 11:06:19 PM
Roles             =  Administrators
Connected         =  True
SessionID         =  03E59B7D-3A03-4989-99D3-48AA2DB4E1BF
Databases         =  Adventure Works DW 2008 MyCube test

connect to a SQL Server 2008 Analysis Server
Figure 1.7

When we execute the Windows PowerShell script by passing a server name that’s not available on the network, you would get an error message similar to the results below. [Refer Figure 1.8]

./get-ssasproperty.ps1 "ABC123"

Result

Exception calling "Connect" with "1" argument(s): "A connection cannot be made.
 Ensure that the server is running."
At C:\AMOScripts\get-ssasproperty.ps1:12 char:16
+ $server.connect <<<< ($ServerName)
Server 'ABC123' not found

error message
Figure 1.8

Conclusion

Part II of this article has illustrated how to use Windows PowerShell and AMO to get the various Server properties of SQL Server Analysis Service.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM