Max - Put It Together

Here’s another few PowerShell short script to be executed under SQLPS.exe and/or if you already have the SQL Server provider loading on your WindowsPowerShell folder under your user profile. Look for the script “Microsoft.PowerShell_profile.ps1”.

Requirement to run script:

1. 1. Must have SQL Server 2008 in order the use the SQL Server provider.
2. 2. You could run this script in SQLPS.exe
3. 3. Optional-> load your SQL provider in your PowerShell V2 profile. Here's how: http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx (Add this code in your WindowsPowerShell profile)
4. 4. Results will be display on screen but you could add code to save result to a file. (" | Out-File ...")

This script will list all my SQL Agent jobs on one server or more servers. Remember you can expand this script to do more. This is a teaser code:

   1: ## Sample Listing SQL Agent jobs results

   2: cd SQLSERVER:\SQL\YourServername\YourDefaultOrInstanceName\jobserver\jobs\

   3: $SQLjob = $null ; $jobHist = $null ; $jobName = $null

   4: $SQLjob = dir

   5:  

   6: foreach($job in $SQLjob){

   7:     $jobHist += $job | select Parent, name, lastRunDate, lastRunOutcome | ft -auto

   8:     foreach($jobName in $job){

   9:         $jobFolder = "$jobName" + '.0\Jobsteps'

  10:         cd $jobFolder

  11:         $jobHist += dir | select SubSystem, Parent, name, lastRunDate, lastRunOutcome | ft -auto

  12:     }

  13:     cd SQLSERVER:\SQL\YourServername\YourDefaultOrInstanceName\jobserver\jobs\

  14: }

  15: cd\

Sample results:

More hints: (only if the SQL Provider is loaded)

1. Include the Instance-name or use “Default”.

2. With the right permission use cmdlet: cd SqlServer:\SQL\ Your-SQLServerName to move between SQL servers from one PowerShell console session.

3. This script will run under Windows Authentication.

4. MOST Important… While using both SQL Provider and/or SQLPS.exe you need to close/reopen the PowerShell session to refresh the information.

I always was curious about Windows Home Server (WHS) and during a Microsoft LiveMeeting on WHS with Jonas Svensson talked about wanted to see some  PowerShell in WHS.  Well, here’s a start.

I’m showing my Windows 7 (64bit) using Windows Virtual PC. I have been able to build my virtual network with Windows 2008 STD SP1(32bit), and Windows Home Server.

Installing PowerShell on the Windows Home Server:

What’s needed?

1. Make sure that WHS has all the Windows Updates (including the SP2)

2. Install the WMF (Windows Management Framework) for Windows Server 2003:

(Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0))

http://support.microsoft.com/kb/968929

What to do after PowerShell is installed?

1. If you’re creating a Windows 7 virtual machine, *install “Integration Features”.

2. Open the PowerShell Console.

3. Type “Enable-PSRemoting”, answer with a “Y”. This will setup your PowerShell remoting features to allow connectivity to other machines to run admin and/or other task oriented scripts.

4. Install WMF (Windows Management Framework) on all other non-Windows 7 computer and repeat step #3. (don’t forget to pick the correct installation package for your Windows version)

*Note: Few things about installing the Windows Virtual PC “Integration Features”:

1. It only support Windows 7, Vista, and XP.

2. It will BSD will happened on Windows 2008 SP1 virtual machine.

3. Installation will complete in Windows Home Server (it did work for me).

Where’s PowerShell Installed??

You will find PowerShell under “Start | All Programs | Accessories | Windows PowerShell”

What’s included with PowerShell?

1. The PowerShell Console prompt.

2. The free PowerShell editor or “PowerShell ISE (Integrated-Scripting-Environment)”.

PowerShell come with a very extensive documentation at your fingertips. Use “Help” in the PowerShell command prompt:

a. Try typing: Help Get-service –detailed

b. Or just type: Help

Welcome to PowerShell!!

Expanding your knowledge… Want to learn more about PowerShell?

Here’s some links:

1. http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx

2. http://edge.technet.com/tags/PSV2/

3. http://channel9.msdn.com/Search/?Term=PowerShell

4. http://powershellcommunity.org/Scripts.aspx

5. http://powershell.com/cs/

6. http://www.sapien.com/

7. http://www.powergui.org/index.jspa

Please, check the internet for more PowerShell information….  the truth is out there!!

As you have notice with my previous sample COMObject vs .NET, that is looks the same.  So, as one of the PowerShell MVP college (Marco Shaw) pointed out on an email to me, that “… .NET just wrapped COM for Office programming” and this is true.   Now, there’s a reason why I went two ways on my QuickBlogs:

1. Both sample works on my computers (Windows 7 RTM 64bit and Windows Vista Business 32bit).  And that’s awesome!!

2. I have a SQL Server Admin college that wanted me to help him with the script but was having an error using the COMobject sample.

He’s on a Windows 7 64bit with Outlook 2007 installed and here’s error message he was getting:

Error: “New-Object : Retrieving the COM class Factory for component with CLSID {0006F03A—0000-0000-C000-000000000046} failed due to the following error: 80080005…}

His “Outlook.Application” COMobject was not loading.  So, I provided him with the .NET version of the script and he was able to extract and produce his email list from his Inbox.

In this case, the .NET code was the solution bypass the problem.   Still simple and powerful.

In my previous QuickBlog I show a sample code of extracting email from Outlook but I was using COM object “Outlook.Application”.  Well, here’s the same code but using .NET “Microsoft.Office.Interop.Outlook” assembly.  The code look the same:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Outlook")
$o = New-Object Microsoft.Office.Interop.Outlook.ApplicationClass
$getmail = $o.GetNamespace('MAPI')
$olxFolder = $getmail.GetDefaultFolder('olFolderInbox');
$olxFolder.items | ? {$_.to -eq "ITDBA_Grp"} | select -first 25 SentOn, SenderName, Subject | fl

Still is a 5 liner to start getting something out of your Outlook Inbox.

Simple and powerful!

This is a quick-to-the-point sample of a PowerShell 5 liner that will extract email information from your Outlook Inbox.  I my current position I have to verify, via emails,  that the SQL Server scheduled tasks have successfully completed.  Also I need to report any failures.  So, I decided to use PowerShell to filter out all the email coming from the SQL Server.

If you all wondered… I got this concept from a VBscript code I found on the internet: (here’s the link)

http://blogs.msdn.com/deva/archive/2008/12/02/outlook-programming-looping-individual-mails-inside-the-inbox.aspx

Also, there’s a caveat with Outlook.  There’s a security box that will come up:

And here’s how to disable it,  from the main menu go to "Tools | Macro | Security | Programmatic Access" then "No Security check...".  

Here’s 5 line… the final result: 

   $GetOutlook = New-Object -com "Outlook.Application";

   $olName = $GetOutlook.GetNamespace("MAPI")

   $olxFolder = $olName.GetDefaultFolder('olFolderInbox')

   $olxItem = $olxFolder.items

   $olxItem | ? {$_.to -eq "SQLAdminGroup"} | select -first 25 SentOn, SenderName, Subject | FL

In this scenario, I’m using the “To” property to filter the email and then I grab the first 25 email because I have about less than 30 jobs running.

Now, keep in mind, that you can save results to an output file and/or send it to a HTML formatted file.

Just to show you this scripting technology is powerful.