Discover Analysis Service's Properties using Windows PowerShell and AMODecember 3, 2008 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 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
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
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
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 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.
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
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
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
ConclusionPart II of this article has illustrated how to use Windows PowerShell and AMO to get the various Server properties of SQL Server Analysis Service. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
("Microsoft.AnalysisServices")
$servername=New-Object Microsoft.AnalysisServices.Server
$servername.connect("PowerServer3\SQL2008")
