Category: SQL Server

About SQL Server.

Codeplex StudioShell in SSMS 2012 – Try it!!

Posted on by admin

If you haven’t yet try it, go ahead download and install this Visual Studio Extension to your SQL Server Management Studio 2012.  This tool will integrate a PowerShell host into your SSMS which you will love for presentations and quick scripts developement from one envirment.  But Yes! this a just a simple host and you’ll find it useful for learning SQL PowerShell.

I will be presenting with this tool in my upcoming webinar with Patrick DeBlanc SQLLunch next week on February 15th “PowerShell Query for the T-SQL Developer” at 11:30am CST / 12:30pm EST.

To download StudioShell, here’s the link: http://studioshell.codeplex.com/

After you install this application, open SSMS,  from the top menu click on “View“, and select “StudioShell“.  Then, you can place this pane anywhere inside your SSMS application.  You can copy/paste t-sql code put remember to import the SQLPS module to have access to your SQL PowerShell commands.

A word of advice! if you venture to go, under ‘Tools | Options“, to change the “Console Choice” to be “Old School“, you will crash your SSMS application when you exit the StudioShell console.  So, DON’T make any changes to your Console Choice options, or you’ll end up loose all your work.

I’m a believer of Tools that can help you be productive, and this one caught my attention.  Please, try it!

Good Job JimChristopher (StudioShell Developer)/@beefarino !!

PowerShell quick list of SQL Users with SysAdmin Role

Posted on by admin

Here’s a quick way to start getting a list of SQL Server users having “SysAdmin” Role.  Basically, I’m using SQLPS module (now available with SQL Server 2012) which loads all the SMO needed to help you script against your SQL engine.

This script does the following:

  1. Import the SQLPS Module.
  2. Connect to a SQL Server Instance.
  3. Get the SQL Logins information.
  4. Search for SQL users with “SysAdmin” Role, and builds a customized information in a PSObject.
  5. Export the information to a CSV file.
  6. Open the CSV file, which by default could open an Excel application(if installed on machine).

Here’s the code:

[sourcecode language=”powershell”]
Import-Module SQLPS -disablenamechecking

$SQLSvr = "SQLServername\Instancename";
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;

$SysAdmins = $null;
$SysAdmins = foreach($SQLUser in $SQLLogins)
{
foreach($role in $SQLUser.ListMembers())
{
if($role -match ‘sysadmin’)
{
Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
$SQLUser | Select-Object `
@{label = "SQLServer"; Expression = {$SQLSvr}}, `
@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
Name, LoginType, CreateDate, DateLastModified;
};
};
};

$SysAdmins | Export-Csv -Path ‘C:\temp\SQLSysAdminList.csv’ -Force -NoTypeInformation;
ii ‘C:\temp\SQLSysAdminList.csv’;
[/sourcecode]

Eventually, you could make changes to this scritp to be capable to access a list of SQL Servers and build your custom report.

Bonus:

To add the functionallity to connect to multiple servers, we can add a list of Servers and then using the “Foreach” statement to loop through the list, and with little changes to the previous code.

Here’s how it will look with just adding a few more line of code:

[sourcecode language=”powershell”]
## – Loads SQL Powerhell SMO and commands:
Import-Module SQLPS -disablenamechecking

## – BUild list of Servers manually (this builds an array list):
$SQLServers = "Server01","Server01\InstanceNameA","Server03";
$SysAdmins = $null;
foreach($SQLSvr in $SQLServers)
{

## – Add Code block:
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;

$SysAdmins += foreach($SQLUser in $SQLLogins)
{
foreach($role in $SQLUser.ListMembers())
{
if($role -match ‘sysadmin’)
{
Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
$SQLUser | Select-Object `
@{label = "SQLServer"; Expression = {$SQLSvr}}, `
@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
Name, LoginType, CreateDate, DateLastModified;
};
};
};
## – End of Code block

}

## – BUild and open report:
$SysAdmins | Export-Csv -Path ‘C:\temp\SQLSysAdminList.csv’ -Force -NoTypeInformation;
ii ‘C:\temp\SQLSysAdminList.csv’;
[/sourcecode]

That’s it!

QuickBlog – Use PowerShell to submit SQLServicePack job to multiple Server

Posted on by admin

This was an interesting discussion in the LinkedIn “How to Install SQL Service Pack by PowerShell?”.  I got the chance to create and test this one out. I scratched the previous script I posted trying to show probe a concept, and ended up creating a new smaller script. Funny!! I created a smaller script!

Well, I just confirmed that’s possible to submit an unattended SQL ServicePack installer as a job using PowerShell Remoting. I’m using my Hyper-V Virtual Domain I ran the script from a Windows 7 VM and submitted one job to two servers. I could see the the jobs processing on the server Task Manager.

But, it was tedious? If you’re a newbie maybe it’s a little over your head but not impossible. That’s the intention of PowerShell Remoting, to be able to do these things like this, and I’m just scratching the surface. Please, Take advantage of these features in PS V2.0, and more enhancements has been done in PS v3.0 with the inclusion of Workflows.

Still, you will need to use PowerShell “Enable-PSRemoting -force” in all the servers. I know, this may be an issue but you need to configure it on all the servers in order to take advantage of PowerShell Remoting. Now, I’m creating session on each computer so I can run as jobs, and your credential is Important to be included. All this is done from your desktop, no more running to the server room.

This time I did test the new script using Sessions in PS Remoting:

[sourcecode language=”powershell”]
## – Get your credential information top connect to servers:
$getCred = Get-Credential ‘Domain99\UserName99’
$servers = "Server1","Server2";
$Jobsession = New-PSSession -Computername $servers -Credential $getCred;

## – display the sessions:
$Jobsession

## – Submit jobs to background process on selected servers:
Invoke-Command -Session $Jobsession -AsJob -JobName ‘TestBackgroundInstall’ `
-ScriptBlock {
new-psdrive -name SQLInstallDrive -psprovider FileSystem -root \\WIN8Server1\install;
cd SQLInstallDrive:;
& ./SQLServer2008R2SP1-KB2528583-x64-ENU.exe /allinstances;
};

## To Display jobs:
get-job

## – To Close PS Sessions and remove variabler:
Remove-PSSession $Jobsession
Remove-Variable Jobsession
[/sourcecode]

In the “Invoke-Command“, the “-ScriptBlock” parameter will hold the code you’re executing on the server as a background job.  Inside the ‘-scriptblock { .. }’ parameter, I’m executing three commands:

1. Creating the PSdrive to the “Install” shared folder.
2. Changing directory (this one could be optional).
3. Finally, run the SQLServer2008R2SP1-KB2528583-x64-ENU.exe SQL Service Pack,

This way you’re not holding the PowerShell Console hostage. You could even make this script more robust. You could add the parameter “-ThrottleLimit” to specify the max number of concurrent connections, to minimize the network traffic. This is just a start, this code can be improve a lot.

You'll need to supply your Windows Credentials
Submitting the job from Windows
 For more information about PowerShell Remoting, type at the PowerShell Prompt:
get-help About_Remoting -full
get-help Invoke-Command -full

Check out the TechNet Tip link on PS Remoting:
http://technet.microsoft.com/en-us/magazine/ff700227.aspx

Added: Please, check this TechEd 2011 video of my college Don Jones talking about “Windows PowerShell Remoting: Definitely NOT Just for Servers”:
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/WCL321

Of course, there are products out there to help manage/automate your Microsoft security, and service packs.  But you will still need to invest time configuring the application.

Well!! This was a good one.
🙂

PowerShell – Trap cmdlet errors.

Posted on by admin

Sometimes working with data can be challenging.  As a SQL Developer, creating ETL solutions, is our responsibility all this data makes it to our users.  Sometime data manipulation might be limited in SSIS, we are force to look for other alternatives.  In my case, I use PowerShell, and I need a way to trap some import processing errors during the execution of the “Invoke-SQLcmd” cmdlet.  Then, I will be able to investigate why some on my data wasn’t making it into my SQL tables.

I try to use PowerShell the “Try-Catch” to trap the error but it was wrong implementation to trap the error in the SQL Server PowerShell “Invoke-SQLcmd” command.  So, How can I trap the command errors?  Well, you can use “CommonParameters“, which are an additional set of parameters automatically provided by Windows PowerShell.  In these additional set of parameters you will find the “-ErrorVariable” parameter which allow you to stores the error information in a given variable.  The error variable you provide in any cmdlet will be overridden each time the cmdlet execute, unless you add an “+” in front of it so it can append to it and collect error information.

For more
Information about all “CommondParameters“, execute at the PS prompt:

[sourcecode language=”powershell”]

Help About_CommonParameters -full

[/sourcecode]

Now, my implementation was to use the “-ErrorVariable” parameter to trap and save the error message with the SQLquery script that failed. Also, I make sure to the variable to a null value after I saved the results to disk. In my scenario, the data imported could break my SQL query and I want to make sure I can identify these records.

So, I’m using the ErrorVariable parameter in my “Invoke-SQLcmd” command.  Here’s a code snippet sample of it:

[sourcecode language=”powershell”]
$reccount = 1; $sqlerr = $null;

:

Foreach($item in $Data)
{
:

$outfile = "C:\ImportError\TSQLQUERY_$($reccount).sql";
$SqlQuery =  "Insert into $tablename ($tblfields) values($fldsvalue);";
Invoke-Sqlcmd -Database $dbName -ServerInstance $ServerName `
-Query $SqlQuery -ErrorVariable sqlerr;

if($sqlerr -ne $null)
{
"/*Error Found – on record# [ $reccount ]:> `r`n"+$Error[0] + "*/ `r`n" `
|  Out-File $outfile -Encoding ASCII;
$SqlQuery | Out-File $outfile -Encoding ASCII -Append;
"`r`n /* "+"".padleft(45,"=")+" */`r`n" `
| Out-File $outfile -Encoding ASCII -Append;
$sqlerr = $null;
}
:
$reccount++;
}
[/sourcecode]

Basically, I want to make sure my “$sqlerr” Error Variable is initialized as $null in both the beginning and after the error have been saved to the output file. Then, we can view later the Error message and the SQL script that error out to later view the output file displaying it in your SSMS or your favorite editor.

Keep in mind, this will not stop the errors from been displayed on screen.  But, you could use another Common Parameter: ” -ErrorAction SilentlyContinue” to prevent displaying the errors.

Sample output  file image:

I hope you find this code snippet useful!

SQLPASS PowerShell Virtual Chapter – “Extending T-SQL with PowerShell” Posted Slides & Scripts..

Posted on by admin

For all whom attended the SQLPASS PowerShell Virtual Chapter – “Extending Your T-SQL Scripting with PowerShell” session on Wednesday November 16th, I finally got it posted here.

My sincere Apologies for the long delay in posting my session slides and demo scripts.  I thought I lost the material after having a disk drive corruption but I was able to recorver it.

Please, click on the link :< download now>: from SkyDrive. (rename file *.zip.txt to *.zip before extracting)

Don’t hesitate to contact me if you have any issues downloading it.

Windows 8 Hyper-V 3.0 – My Personal 8 Tips for the Newbie

Posted on by admin

Well, Here’s some tips to those who are first timers with Windows 8 Server (or Client) Preview edition.  I have to say, after been using the previous version of Hyper-V, I’m very happy for what i’ve seen so far in Windows 8 Server with Hyper-V 3.0.  As I finally completed updating/rebuilding all my VM’s for upcoming PowerShell and SQL Servers presentations, I have compiled 8 tips that might help the newbies get started using Microsoft Virtualization Technology.

From the Hyper-V Manager console, click on the VM Connect option to work with your VM's.

Tip #1 – Memory is important

Yes, I totally agree with this one.  After I upgraded my memory from 4GB to 8GB, all my VM’s are working OK.  I have experience giving 1.5GB to a Virtual SQL machine is ideal for development.

VM Memory Setting

Tip #2 – Enabling Hyper-V

First, There’s some differences between Hyper-V 3.0 server and Client.  But, let me be clear, that every machine might be different.  In my case, I have a 4 year old HP Pavilion dv7-* Entertainment laptop with Virutalization option enabled. I created a dual boot Windows 8 Server and Client version.

On the Windows 8 Server enabling “Hyper-V” role using the new “Server Manager” Dashboard was easy.  But, in my case, I had to manually enable “Hyper-V Core” on my Windows 8 client by turning it “ON” in Windows Features.  Here’s the command:

C:\Dism /online /enable-feature /featurename:Microsoft-Hyper-V

Tip #3 – Create your Hyper-V 3.0 Virtual Network Switch

This is very Important and one this you need to setup your Virtual Network connections in either Server or Client. Open “Hyper-V Manager” and look the “Virtual Switch Manager”.

Hyper-V Virtual Switch Settings

As you can see in my picture, I have a Loopback adapter Virtual Switch (External), and a Wireless Virtual Swtich(Externa) both setup as Legacy Adapters).   These two combination works great when VM’s talk to each other and, at the same time, have access to the internet for Windows Update.  Keep in mind, you can disable the wireless by going into the VM Settings option, going into the “Legacy Network Adapter”, and changing the “Virtual Switch” to “not Connected”.

VM Settings menu - Changing Virtual Switch to "Not Connected"

Tip #4 – Don’t need a SAN Storage

FYI. Hyper-V 3.0 will allow you to create your VM’s practically anywhere.  In my case, I have an External USB 1.o TB Storage drive.  This is good new because you don’t need a SAN storage unit, and can save Vm’s outside your Physical server drives.

Tip #5 – Set VM’s TimeZone correctly

This might be only in my case, but after creating my VM’s and trying to connect them to join my Virtual Domain Controller, I experience connectivity issues between my VM’s due to their Time Zones not been the same.  So, If you have a Virtual Domain Controller, make sure both Time Zone machines are the same.

Here’s an interesting link on “Time Synchronization in Hyper-V” (by the “Virtual PC Guy’s Blog”) you may find useful: http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/11/19/time-synchronization-in-hyper-v.aspx

Tip #6 – Learn to use the VM Snapshot Feature

Yes! This feature is a life saver.  It can help you troubleshoot VM issues by you taking the snapshot of a VM machine before doing any irreversible updates that could force you start over to rebuild the machine over.  It’s perfect when creating multiple scenarios for in test machines and adding descriptions to it.  Also, you can also revert to a previous snapshot, or apply the changes.

List of a server Snapshot showing different stages during a SQL Server Installation
Adding a Description to your Snapshot

 

Tip #7 – Bringing Legacy Microsoft VM to Hyper-V3

If you want to move all your existing VM’s from: Virtual PC, Windows Virtual PC, and Hyper-V, just remember to uninstall all “Virtual Components”, or “Integration Services” to prevent any misbehaviour in your VM.  Then, you will be able to install the Hyper-V 3.0 “Integration Services” so that Hyper-V can manager your VM’s services such as: Snapshot, Start, Shutdown, or Save.

*hint*: I have experience some issues with Windows Servers 2008, and Windows 2003 SP1 after mvoing them to Hyper-V 3.0.  The mouse won’t work even after installing “Integration Services”.

Tip #8 – Start using Use PowerShell

Yes! YES! This is your opportunity to start using PowerShell.  You can user the Hyper-V module and start managing your VM’s.  Also, don’t forget that PowerShell comes with over 1695 and about  63 Modules.  Keep in mind,  This numbers will varies depending on the enabled Windows features and/or installed Server Applications containing PowerShell modules.

Here’s some one-liners command you may want to try get the estimate numbers of PowerShell commands and modules:

1. Get total number of available  commands and list their names:

(get-command -Module *).count; (get-command -Module *) | Select name;

2. Get total numbers of available (installed) Modules, and list their names:

(get-Module -ListAvailable).count; (get-Module -ListAvailable) | Select name;

I hope this information is helpful.  That’s it for now!

SQL Server PowerShell SMO – Simple way to Change SQL User Passwords

Posted on by admin

Here’s a simple PowerShell SMO code that shows you How-To change a SQL user password.  Keep in mind, SMO needs to be installed and the assemblies loaded before using this code.

To load SMO, you can:

1. Install the SQLPS Module (is using SQL Server 2012 “Denali”), or Chad’s Miller SQLPS module for SQL Server 2008/R2.

Import-Module SQLPS -DisableNameChecking

2. Or, Load the SMO Assemblies using the PowerShell V2 “AddType” command: (but carefull is you have multiple SQL Server versionsin the same box)

Add-Type -AssemblyName “Microsoft.SqlServer.Smo”

3. Or, use the still reliable the old PowerShell V1 load assemblies line using:

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

In the following example show how to connect to a SQL Server using both the default Windows Authentication (with high Administrator privileges already set), or SQL Server Authenbtication to later change a user password.

*Hint*: If you want to include special characters, you need to use the single qoutes or PowerShell will think that it’s a variable name.

As you can see,  with just a few line of PowerShell SMO code you can start orchestrating a solution that can be applied to your SQL Server environments.

Just try it!

Creating a Standalone Windows 8 Server Virtual Machine with SQL Server 2012 CTP3

Posted on by admin

Here’s how I build my version of a standalone workgroup Windows 8 Server Virtual Machine(VM) with SQL Server 2012 (“Denal;i”) CTP3. When you create this VM, make sure to give enough memory.

How-To create a VM in Hyper-V Manager console:

I’m not going deep on this topic but the Hyper-V Manager GUI it’s easy to use.  I’m assuming you already got an *.ISO image of both: Windows 8 Server Preview and SQL Server 2012 CTP3.  If not you’ll have to find it at Microsoft website and/or your MSDN/Technet subscribtion.

Before Creating your new Virtual machine:

Keep in mind, you’ll need to setup you Hyper-V environment.  Meaning, if you’re using a laptop and/or a desktop computer (not a server),  still you need to make sure it meets the Hyper-V requirements or it won’t work.

So, at least you will need to use the “Virtual Switch Manager…” to assist you setting up your virtual network card to use by any VM you create.

Notice, in my environment, I have created three virtual network adapters:

  1. Wired Internet
  2. Wireless Internet
  3. Loopback

The first two adapters serve my purpose to be able to connect to my physical machine Internet connection so I can do windows update.  The loopback adapter is for my internal network connection to my virtual Domain Controller.

*Hint*: In order to allow your VMs to access your external wireless adapter, you need to enable in “Server Manager” the “Wireless LAN Service” feature before you create the virtual wireless adapter.

Now, you are ready to create a New Virtual machine, and just follow the wizard:

The “New Virtual Machine Wizard” will help you configured everything you need.   Make sure you create this VM with enough memory.  In my case I assigned 1.5GB of memory.

Opening your Virtual Machine Connection:

There’s two ways to open your VM Connection in your “Hyper-V Manager” console:

  1. By double-clicking at the actual virtual name.
  2. Or, double-click at the actual virtual machine preview pane at the bottom left side of the “Hyper-V” console.

Now that your connection GUI is open, it’s a good time to start doing a VM Snapshot in case you need to go back and troubleshoot in case of problems. Here’s some pictures on How-To create a VM Snapshot:

If a Snapshot box asking to add a Name to your snapshot, go ahead and do it.  This box only comes when there has been changes done to your VM.

Now, Ready for SQL Installation.

Installing SQL Server 2012 (“Denali”) CTP3:

After building the virtual server, if you try to immediately install SQL Server 2012, it won’t work.  And, when you try to run the setup.exe, you won’t have access to the “SQL Server Installation Center” to view the “Hardware and Software Requirements” information.

Check SQL Server Requirements:

So first, you may want to check this link to read about what’s required to install this new version of SQL Server: http://msdn.microsoft.com/en-us/library/ms143506(v=SQL.110).aspx

Need Role(s) and Feature(s):

In order to make my SQL Server installation work, I had to open Windows 8 “Server Manager” and follow the wizard to do the following steps:

  1. Install the Application Server Role (you can add more roles as you need during this process).
  2. Then, you need to add the following features: (again, you can add additional features as needed)
    a. Enable the .NET Framework 3.5
    b. Enable the PowerShell ISE

After doing this steps, then I was able to get the SQL Server setup to work and allow me to start my installation.  Use the VM Connect GUI to allow you to attached the SQL Server  *.ISO image for your VM to start the SQL Server Installation.

I’m not going to show all the SQL Server installation screens but here’s to show that I’m able to proceed with the installation.

On the previuos picture, notice that I selected most of the features to install except the for the two Distributed Replay services. At the same time, I took a live snapshot of my VM before the actual installation process.

Installation Completed Successfully:

Yes! I got my SQL Server 2012 installed without a glitch!

Testing SQL PowerShell:

Now, I immediately testing the PowerShell SQLPS module. I was so excited that forgot to do something first.  Here’s my result of my first try:

Yes! I forgot to set my “Set-ExecutionPolicy” to “RemoteSigned“, then close and reopen my Windows Console.  I also I was able to SQLPS.exe from SSMS Database option just to test that there’s no errors.  So, everything works at least for now.

Ooops! Except for PowerShell ISE.  Yes! If you try to do an “Import-Module SQLPS -DisableNameChecking” then you get an error:

Don’t Worry!  PowerShell ISE is not the only editor.  You can still use Notepad to create/modify your script(s).  Or, just try downloading one of the free community editors from: SAPIEN, and PowerGUI just to name a few.

To test SQLPS I use the following command line:

Invoke-SQLcmd -database ReportServer -Query “Select top 3 * from dbo.DBUpgradeHistory

Final comments:

I know I may have skip some steps but the bulk on How-To create Window 8 Server VM in shown in this blog.  One important thing to keep in mind, these are still Community Technologies Preview (CTP) and it will change.

So, Don’t be afraid to try it!  This is why we have the ability to create Virtual Machines in our own developement machines.  Again, take advantage of Hyper-V.

The opportunity we have is to learn from them, assist giving feedback, and MOST IMPORTANT, it help us to stay ahead in upcoming technologies.

Enjoy, Learn, and Collaborate!

Bonus!!… Bonus!!

If you want a HACK to fix the PowerShell ISE that here it is: (Hack provided by one of our PowerShell MVP’s – Joel “Jaykul” Bennett
http://HuddledMasses.org
http://PowerShellGroup.org
)

  1. Go to you x:\Windows\System32\WindowsPowerShell folder (make sure to access this folder “As an Administrator” or it won’t work)
  2. Create a blank text filename: powerShell _ise.exe.config (Yes! this file extension is “.config”)
  3. Then, add the following XML lines, and save the file when done:
(Updated: 10/26/2011 – I missed the “runtime” section)

<configuration>
<startup useLegacyV2RuntimeActivationPolicy=”true”>
<supportedRuntime version=”v4.0″ />
</startup>
<runtime>
<loadFromRemoteSources enabled=”true”/>
</runtime>
</configuration>

Reopen PowerShell ISE and try to use the “Invoke-SQLcmd” command.

That’s it!

SQL Saturday #86 Tampa BI edition: SSIS – Analyzing your data integrating PowerShell session

Posted on by admin

I’m excited to bring this new topic to #SQLSat86 Tampa for the BI community.  So, here’s a brief rundown of what I will be covering on Saturday, November 5th morning session:

Topic: SSIS – Analyzing your data integrating PowerShell

*Note: This is not an all PowerShell Session but it plays a big role in this SSIS solution.

What will be covering:

1. Some basic to intermediate SSIS and PowerShell.
2. How to pass arguments between PowerShell and SSIS steps.
3. How to use PowerShell to assist in analyzing our tables and/or Data.
4. How to Integrate PowerShell in our SSIS Solution (when needed).
5. Tool available to allow these two technologies to integrate in our ETL solution.

This is going to be an ALL DEMO session.

Requirements:

1. Have some interest in PowerShell.
2. Some basic SSIS ETL experience.
3. Willing to be open to new technologies.
4. Most Important: No PowerShell experience is not required.

I will Demo:

1. Passing Arguments between PowerShell Applications in SSIS.
2. Work with both Script Task and Script Components.
3. Use of both VB and C# (CSharp) .NET Scripting code.
4. Some PowerShell.

I hope yopu will enjoy this presentation

PowerShell working with SQLPS or SMO…

Posted on by admin

Today, while help @meltondba with his SQLPS question on the enumerating jobs history, @LaerteSQLDBA provided a oneliner to provide this result.  There’s one concern, should we use SQLPS instead of SMO.   For this answer I’m going to point out MSDN article regrading the future of SQLPS in upcoming SQL Server releases: http://msdn.microsoft.com/en-us/library/cc280450(v=SQL.110).aspx

This article states that “… This feature will be removed in a future version of Microsoft SQL Server.  Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use the sqlps PowerShell module instead. For more information about the sqlps module, see Import the SQLPS Module. …”

So, during this exercise I found out that I can out with the same number of line for either SQLPS and SMO.  Both giving me the same results.

Here’s some picture ilustrating basic code snippet of both SQLPS and SMO to get some SQL Server Jobs information:

SQLPS sample of getting jobs information
SMO sample of getting jobs information

This pictures shows, it look simple enough.  So, if you’re building SQLPS scripts, you can easily start transitioning your code to SMO.