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.

Displaying a list of tables with their rows count:

1. You need to have installed an instance of SQL Server 2008 (or 2008 R2) in order to use SQLPS.

2. To connect to another SQL Server remotely, you need to make sure the server(s) are properly registered in your SQL Management Studio (2008/2008-R2). Look in the *“Registered Servers” pane.

*Hint: You should be able to query SQL Server 2000, and 2005.

3. To start using SQLPS, there’s three ways to open it:

• a. In the Windows Start/Search box, typing SQLPS and then Enter.

• b. SQL Management Studio, right-click on any of the objects and look for “Start PowerShell”

• c. Use your User PowerShell Profile to load SQLPS automatically (follow Michael’s Blog to learn how: http://www.idera.com/richardsworld/post/PowerShellPlus-and-SQL-Server-2008-SQLPS.aspx )

4. Start SQLPS session:

5. Check your SQL Registered Servers:

6. Here’s a very useful one-liner cmdlet to get tables –rowcounts and generate some output:

The first line use the “CD” to change directory to the remote server tables folder. Then, use the “dir” to list all tables. If you need to find more information about that folder then use the “Get-Member” (or the alias “gm”) so you can see that properties and methods you have available at your finger tips.

Example, use the following approach to save the results on a variable and then use “Get-member”:

Then, using the Cmdlet “Select” (short for “Select-Object”), you can work with the properties: “Name” and “RowCount” to be displayed in the PowerShell Console:

dir | Select name, rowcount …

Results:

Have fun with SQLPS!!

Microsoft PowerShell team have only giving a more complete PowerShell and including a scripting editor included in their latest OS’s.  But now, there’s two interesting tools emerging for those venturing deeper in PowerShell scripting:

1. “PowerShell Cmdlet and Help Designer” by the Microsoft PowerShell Team:

Brief description:

The Cmdlet Designer makes it much easier for teams to concentrate on the design, naming, and consistency of their cmdlets, while also guaranteeing name registration and collision avoidance across a project.

Download at: http://cmdletdesigner.codeplex.com/

2. “PowerShellpack” by one the Microsoft PowerShell Team member – James Bundage:

Brief description:

Windows PowerShell Pack contains 10 modules to help supercharge your Windows PowerShell scripting. The PowerShellPack lets you write user interfaces in PowerShell script, manage RSS feeds, schedule operating system tasks, and much more.

Download at: http://code.msdn.microsoft.com/PowerShellPack

Good job guys!!

Special Thanks to Andy Warren for allowing both me and Chad to present our PowerShell SQL Part 1 and 2 at this Awesome event.

Here’s some pictures:

View Full Album

Here’s downloads for both sessions:

1. PowerShell and SQL Server part 1: http://cid-a034d6a0ddc4e64e.skydrive.live.com/self.aspx/SQLSaturday21%5E_10172009%5E_Demo/SQLSaturday21%5E_10172009.zip

2. PowerShell and SQL Server Administration part 2: http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!562.entry

Here’s THE PHOTO of the Florida PowerShell Power Leaders ( Only SW Florida – Jeff Truman is missing in this picture ):

From left to right: Buck Woody (MS Seattle, WA), Ron Dameron (Tampa), Chad Miller (Tampa), and me – Max Trinidad.

Thanks to all attendees and organizers for having us!!

FLPSUG - Florida PowerShell User Group

Starts:
Thursday October 29, 2009 at 6:00pm

Ends:
Thursday October 29, 2009 at 8:00pm

Event Type:
Training/Seminar

Region:
Miami/Fort Lauderdale Area

Location:
New Horizons
100 S Pine Island Rd
Fort Lauderdale, FL 33324 US

Price:

Website:
http://www.flpsug.com

Industry:
computer software

Intended For:
developer, system administrator, database administrator, DBA, and any one who want to now about automation technology.

Organization:
Florida PowerShell User Group

Maximo Trinidad, Microsoft MVP, is the Florida PowerShell User Group's founder and leader. He is truly passionate about sharing his knowledge of PowerShell with others.

Meetings: tentative on 3rd Wednesday of every month (change of dates are possible)

Website: http://www.FLPSUG.com

Blog: http://max-pit.spaces.live.com

Twitter: MaxTrinidad

From one of the MVP’s:

Windows isn’t just about the GUI. Starting with Windows 7, you have built-in access to PowerShell version 2, an object-oriented scripting language and command shell. Please join PowerScripting Podcast hosts Jonathan Walz and PowerShell MVP Hal Rottenberg as they interview Distinguished Engineer Jeffrey Snover on launch day! Jeffrey is the chief architect responsible for PowerShell at Microsoft, and he’ll be covering what’s new with the tool and why every system administrator on the planet needs to be using it. If you’ve never attended PowerScripting Live, you are missing out on a great time. The show will be streamed live via Ustream, and viewers can chat with each other, as well as submit questions for the guest.

· When: Thursday, October 22nd, 9:30 PM EDT (GMT-4)

· Where: PowerScripting Live on Ustream, and follow the blog and podcast atPowerScripting.net

Please join us in this GREAT DAY!!

Thanks Hal for all the hard work!!

Get on the PowerShell bandwagon!!! Don’t be left behind old technology!!

Here’s some Microsoft site that will provide you with more information about PowerShell to get you going:

And, there’s a lot out there!!

First, Special THANKS!! to the Naples .NET Community/Organizer John Dunagan, and Microsoft Developer Evangelist Joe Healy for allowing to get all my session approved.  http://codecamp.swfldev.net/Schedule.aspx

View Full Album

Meet the Florida PowerShell presenters:

Max Trinidad, and Chad Miller.

So, It is possible...  Yesterday, I gave three hour sessions about PowerShell, and co-host the fourth one with Chad Miller.  So, in total, it was a 4 hour PowerShell track.   I had the same people stay for most of the sessions.

I created and presented the following topics:

• Title: Working with Access in PowerShell V2. (Download All 3 Presentations)
  Description: Check out what you can do with Access and PowerShell. I will give you some samples that might help even in your VBA coding and managing your Access databases. 
• Title: Using Namespaces and Embedding .NET languages in PowerShell V2.
  Description: This session will show you the ability to extend the PowerShell scripting from its basic use. Using .NET namespaces and embedding C#/VB into your script. It doesn't matter the level of experience.
• Title: PowerShell and SQL Server (Part 1 of 2).
  Description: See how you can use SMO and the SQLPS (PowerShell mini-shell) to manage your SQL Servers from your console. This will increase your productivity. Then, make sure to follow the second part by Chad Miller - PowerShell and SQL Server Administration. This is a part 1 & 2 most see sessions.

And, Chad Miller presented

• Title: PowerShell and SQL Server (Part 2 of 2). (Download Presentation)
  Description: In this session we will look at automating common SQL Server tasks through Powershell. An overview of the CodePlex project SQL Server Powershell Extensions will be provided. Specific topics covered include SSIS administration, replication/agent monitoring, and building security auditing reports.

Also, we had a special visit from Alex Reidel (CTO) from Sapien Technologies, who distributed some Scripting Tools to all attendees:

One of our FLPSUG Sponsors making presence - Sapien Technologies, Inc.

Thanks Alex for your support.

It was a great opportunity to see how PowerShell audience is on the rise.

VERY IMPORTANT...  During this event, I had a person (Jeff Truman) who want to start a PowerShell User Group in Naples/Fort Myers area.  AWESOME!!!

I just wanted to share the news.  I will blog more next week and post some pictures.

And very excited to be part of the MVP program.

Showcasing Windows PowerShell

PowerShell Means “Powerful Automation”!
Announcing a new webcast series for both Developers and IT-Pro’s.

PowerShell is a Windows management technology designed for ease-of-use by both system administrators and application developers.  PowerShell Version 2 (V2) is available with both Windows Server 2008 R2 and Windows 7 as well as previous Windows releases via an optional update.

For Developers specifically, Windows PowerShell in combination with the Windows Management Infrastructure (WinRM, WS-Management, WMI) provides a great way to automate server hosted solutions.   For example, if you implement all your administration logic via PowerShell, then layer the MMC GUI over the top (i.e. MMC calls PowerShell to get the work done) - you will have given your Enterprise customers the absolute best of all worlds; GUIs, scripting, and delegated, remote automation.

PowerShell V2 introduces many new features including remote sessions, an integrated script environment, debugging tools, and much more. 

Start your video tour of PowerShell V2 via MSDN Channel9 and TechNet Edge.  Find reusable scripts and techniques at the PowerShell Script-Center.  Subscribe to the RSS feeds at both the PowerShell and the Windows Management team blogs.  Get demo scripts from MSDN Code Gallery.

Download and share the new Windows Server 2008 R2 Developer Training Kit with PowerShell HOLs!

We hear it from the main source @JSnover… RC (Release Candidate) PowerShell V2 for Windows Server 2003 and XP are available:

“XP and W2K3 RC versions of PS V2 and WINRM are now available for download http://tinyurl.com/mqnkm6 - SNOOPY DANCE!”

Let’s all do the Snoopy Dance!!!

Applies To: Windows Server 2008 R2

System Center Virtual Machine Manager 2008 R2 Cmdlet Reference

http://tinyurl.com/lm2rd2

WHat's New in Windows PowerShell Cmdlets for Roles and Features

http://tinyurl.com/knnyjy

Applies To: Windows Server 2008

System Center Virtual Machine Manager 2008 Cmdlet Reference

http://tinyurl.com/l8ngroo

When available… Look at the recorded livemeeting TechNet Webcast “Using Windows PowerShell with Hyper-V and Virtual Machines (broadcast on 09/10/2009)

Thanks to Marco, Hal, Darin.

Here’s my experience installing my Windows 7 RTM which I was very excited to do.   After experiencing an almost perfect Beta and RC then it was a little more work with RTM.  Please, don’t get me wrong but I put myself in the spot.  

My headaches after installing Windows 7.   I had some roadblocks when putting back all my applications, 

So, this is what I learn from my own mistakes:  (Is not Microsoft fault!)

1. And, this is common sense, Create backup image of your system and/or backup your files on another drive

2. In my case again,  I couldn’t upgrade from RC to RTM but the good new is that, if you on Vista, then you will be able to upgrade.  So I did a clean install.

3. I had problems installing applications,  they will failed no matter what you do.   My problem was cause because I didn’t connect my machine to the internet.  So, this setup errors was cleared after my connected to the internet and download the Windows Updates.

4. SQL Server 2008 RTM (not SP1) installation failed on my first try.  Then, I decided to install Visual Studio 2010 (which include SQL Server 2008/Express).  Then, I was able to Install SQL 2008.

5. I had problems Installing my Windows Live components (mail, writer, meeting) the whole packages failed due to a program conflicting behind the scene.  Found the program and did an uninstall to clear the condition.  The program was one of the components in Visual Studio 2010 “Tools for Application” that somehow (in my case) was affecting my Windows Live installation.

So, after I clear all my application installation issues, then I was to complete loading all my programs.

Now, for those who are still experiencing SQL Server 2008 installation issues, please check the following blog:  ( it may help you ) http://bit.ly/g6iSZ

I truly love my Windows 7!!!… its been running Smooth!!!

Thanks to all SQLSaturday#16 organizers, fellows SQL Speakers from all over Florida for coming over, and one guy in particular that brought his wife from South Carolina just to speak the conferences (Chris Eargle).  Both Chad Miller and myself appreciate to have is participate in a Part 1 and part 2 sessions about PowerShell and SQL Server.  We all have a good time.

View Full Album

Here’s our “PowerShell and SQL Server” part 1 and 2 sessions (Demos and Presentations are included):

Part 1: PowerShell and SQL Server (intro) – Part 1 by Max Trinidad (Port St. Lucie):

Part 2: PowerShell and SQL Server Administration – Part 2 by Chad Miller (Tampa):

Chad and I will be presenting our 1 – 2 session at the next SQLSaturday #21 in Orlando – October 17th.

This was a great experience and very successful.

Forgot to mention...  Special THANKS to Quest - PowerGUI and Software FX - PowerGadget for Providing the giveaways for all our attendees which was distributed in both PowerShell session.

Thanks for your support.

Thanks to the Microsoft PowerShell and Springboard/Windows 7 Team, Windows 7 RTM is out with PowerShell v2 RTM included.  With a great tools comes greater responsibility.  PowerShell v2 comes its more powerful than ever and comes with lots of tools (…such as remoting, ISE, modules, advanced functions, enhanced WMI support, and a variety of new cmdlets…) that will help your servers administration.  Don’t forget, you can extend your PowerShell scripts to be more powerful thanks to .NET Framework.

PowerShell Team blog: http://blogs.msdn.com/powershell/archive/2009/07/23/windows-powershell-2-0-rtm.aspx

Checkout the Windows 7 RTM Springboard series and more information: http://technet.microsoft.com/en-us/windows/dd361745.aspx

Job Well DONE Microsoft.

Yes, finally is out.  I like “PrimalForms Community Edition” but the new “PrimalForms 2009 v1” will make you fall in love for it.  This new version with the added editorm and more features will integrates with their  “PimalScript Studio”… its the best companion and tool for any PowerShell Scripters/developers. 

Here’s more resource links:

Sapien Technologies: http://www.primaltools.com/products/info.asp?p=PrimalForms

The Lonely Admin: http://jdhitsolutions.com/blog/2009/08/04/primalforms-2009-now-available/#utm_source=feed&utm_medium=feed&utm_campaign=feed

This is a most have tool.