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 Nov 18, 2008

Microsoft Windows PowerShell and SQL Server 2008 AMO

By Muthusamy Anantha Kumar aka The MAK

As you probably know, Windows PowerShell is the new command shell and scripting language that provides a command line environment for interactive exploration and administration of computers. In addition, it provides an opportunity to script these commands so that we can schedule and run these scripts multiple times.

Windows PowerShell depends on .NET framework 2.0 or higher.

SQL Server Analysis Management Objects, known as AMO, is an object model for SQL Server Analysis Service and its configuration settings. AMO-based applications use .NET Framework languages to program against this in-memory object model.

In this article series, I am going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2008.

Part I of this series is going to illustrate how to install and use a simple PowerShell command and a simple AMO command.

Assumption

a. The machine you use already has .NET 2.0 or higher installed

b. The machine you use already has SQL Server 2008 client installed.

Download and Install Microsoft PowerShell

a.  Download Microsoft PowerShell “WindowsXP-KB926139-x86-ENU.exe” from http://download.microsoft.com. You could install PowerShell 2.0 CTP2 instead of PowerShell 1.0.

Note: In this article, I am using PowerShell 2.0 CTP2.

b. If you install PowerShell 1.0, then you will see the following screens.

Step 1: Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable. [Refer Figure 1.0]

Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable
Figure 1.0

Step 2: Click “Run”. [Refer Figure 1.1]

Click Run
Figure 1.1

Step 3: Click “Next”. [Refer Figure 1.2]

Click Next
Figure 1.2

Step 4: Select the option “I agree”. [Refer Figure 1.3]

Select the option I agree
Figure 1.3

Step 5: Watch the progress of the installation. [Refer Figure 1.4]

Watch the progress of the installation
Figure 1.4

Step 6: Click Finish. [Refer Figure 1.5]

Click Finish
Figure 1.5

c. If you opt to install PowerShell 1.0, then you will see the following screens. [Refer Figure 1.6, Figure 1.7, Figure 1.8, Figure 1.9, Figure 1.10 and Figure 1.11]

Step 1: Click Run

Click Run
Figure 1.6

Step2: Click Next

Click Ne3xt
Figure 1.7

Step 3: Accept the agreement

Accept the agreement\
Figure 1.8

Step 4: Click Install

Start installation
Figure 1.9

Installing Windows PowerShell

Figure 1.10

Step 5: Click Finish.

Completing Windows PowerShell Setup Wizard
Figure 1.11

Launch PowerShell

There are few ways to launch PowerShell. One method is to go to the command prompt and type the following command. [Refer Figure 1.12]

PowerShell

Launch PowerShell from the command prompt
Figure 1.12

After a short pause, the PowerShell prompt appears. [Refer Figure 1.13]

The PowerShell prompt appears
Figure 1.13

If you use Windows PowerShell 2.0 CTP2, then the screen will look like Figure 1.14.

PowerShell 2.0 CTP2 screen
Figure 1.14

Alternatively, you can start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer Figure 1.15]

start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell
Figure 1.15

Command Help

Inside Windows PowerShell, you can access the command list by typing the command:

Get-command

This displays all of the commands available in PowerShell. [Refer Figure 1.16]

Add-Content
Add-History
Add-Member
Add-PSSnapin
Clear-Content
Clear-Item
Clear-ItemProperty
Clear-Variable
Compare-Object
ConvertFrom-SecureString
Convert-Path
ConvertTo-Html
ConvertTo-SecureString
Copy-Item
Copy-ItemProperty
Export-Alias
Export-Clixml
Export-Console
Export-Csv
ForEach-Object
Format-Custom
Format-List
Format-Table
Format-Wide
Get-Acl
Get-Alias
Get-AuthenticodeSignature
Get-ChildItem
Get-Command
Get-Content
Get-Credential
Get-Culture
Get-Date
Get-EventLog
Get-ExecutionPolicy
Get-Help
Get-History
Get-Host
Get-Item
Get-ItemProperty
Get-Location
Get-Member
Get-PfxCertificate
Get-Process
Get-PSDrive
Get-PSProvider
Get-PSSnapin
Get-Service
Get-TraceSource
Get-UICulture
Get-Unique
Get-Variable
Get-WmiObject
Group-Object
Import-Alias
Import-Clixml
Import-Csv
Invoke-Expression
Invoke-History
Invoke-Item
Join-Path
Measure-Command
Measure-Object
Move-Item
Move-ItemProperty

New-Alias
New-Item
New-ItemProperty
New-Object
New-PSDrive
New-Service
New-TimeSpan
New-Variable
Out-Default
Out-File
Out-Host
Out-Null
Out-Printer
Out-String
Pop-Location
Push-Location
Read-Host
Remove-Item
Remove-ItemProperty
Remove-PSDrive
Remove-PSSnapin
Remove-Variable
Rename-Item
Rename-ItemProperty
Resolve-Path
Restart-Service
Resume-Service
Select-Object
Select-String
Set-Acl
Set-Alias
Set-AuthenticodeSignature
Set-Content
Set-Date
Set-ExecutionPolicy
Set-Item
Set-ItemProperty
Set-Location
Set-PSDebug
Set-Service
Set-TraceSource
Set-Variable
Sort-Object
Split-Path
Start-Service
Start-Sleep
Start-Transcript
Stop-Process
Stop-Service
Stop-Transcript
Suspend-Service
Tee-Object
Test-Path
Trace-Command
Update-FormatData
Update-TypeData
Where-Object
Write-Debug
Write-Error
Write-Host
Write-Output
Write-Progress
Write-Verbose
Write-Warning

all of the commands available in PowerShell\
Figure 1.16

A simple AMO in PowerShell

Let us query the server level information of the Analysis service.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command.

PowerShell

Step 3

Execute the following cmdlets, one by one, as shown below.

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

Execute the following cmdlets
Figure 1.17

Note: PowerServer3 is the hostname and SQL2008 is the Analysis Service instance name.

Step 4

Now, Execute the following cmdlets to get the Server level property of the Analysis Service on “PowerServer3\SQL2008”.

$Servername

The result of the cmdlet is shown in Figure 1.18

Result

ConnectionString : PowerServer3\SQL2008
ConnectionInfo   : Microsoft.AnalysisServices.ConnectionInfo
SessionID        : F6F441C2-C152-43F9-BC31-5DA311FE299F
CaptureXml       : False
CaptureLog       : {}
Connected        : True
SessionTrace     : Microsoft.AnalysisServices.SessionTrace
Version          : 10.0.1600.22
Edition          : Evaluation
EditionID        : 610778273
ProductLevel     : RTM
Databases        : {}
Assemblies       : {System, VBAMDXINTERNAL, VBAMDX, ExcelMDX}
Traces           : {FlightRecorder}
Roles            : {Administrators}
ServerProperties : {DataDir, TempDir, LogDir, BackupDir...}
ProductName      : Microsoft SQL Server code name "Katmai" Analysis Services
IsLoaded         : True
CreatedTimestamp : 10/28/2008 11:06:19 PM
LastSchemaUpdate : 10/28/2008 11:06:19 PM
Description      :
Annotations      : {}
ID               : POWERSERVER3\SQL2008
Name             : POWERSERVER3\SQL2008
Site             :
SiteID           :
OwningCollection :
Parent           :
Container        :

Results
Figure 1.18

Conclusion

As I mentioned earlier, this article series is going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2008 Analysis Service.

Part I of this series illustrated how to install PowerShell and use a simple PowerShell command. It also illustrated how to use PowerShell in conjunction with a simple SQL Server AMO command to get the properties of 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