Month: September 2011

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:

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

Although, PowerShell give the whole remoting experience, you may still need to ask you network administrator to let you configure remoting on all the machines you need to access.  Of course, unless you hold the keys of the kingdom.  But, it is much  simpler without PowerShell Remoting.

When you use the SQLPS Module it automatically let you connect to any of your SQL Servers on your network.  So, YES!! As long as you have permission to manage these servers, SQLPS already gives you the ability to remotely connect to other machines.  There’s no need to setup the Remoting with  Enable-PSRemoting or Set-WSManQuickConfig commands.

Simple Remoting #1

So, How can I change from my SQL Server local prompt to another instance?  Here’s an example how:

1. First load the SQLPS Module:

[sourcecode language=”powershell”]
Import-Module SQLPS -DisableNameChecking
[/sourcecode]

2. Confirm you have the SQL Server drive available:

[sourcecode language=”powershell”]
Get-PSDrive SQL*
[/sourcecode]

3. Let’s navigate to your SQL Server Instance:

[sourcecode language=”powershell”]
cd SQLServer:\SQL\Your-Machine-name\Default  #or

cd SQLServer:\SQL\Your-Machine-name\Your-InstanceName
[/sourcecode]

4. Now you can continue and navigate through your SQL Server drive like in DOS filesystem.

[sourcecode language=”powershell”]
Dir Databases -force
[/sourcecode]

5. Finally, to remote connect top another session just change the “Your-Machine-name”, and your “Your-InstanceName”.

Simple Remoting #2

One thing you need to understand, after you load SQLPS module, now you have access to all SMO (SQLServer Management objects) .NET Classes. This will help you build short and very effective .NET that extend the scripting of PowerShell.  Also, you don’t need to be at the SQLServer: drive to use it.

Here’s a basic example using SMO:

1. First load the SQLPS Module:

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

2. Create a variable to hold your local SQL Server objects:

[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server
[/sourcecode]

3. Now that your .NET object has been create you need to see all what you can grab from it:

[sourcecode language=”powershell”]
$MySQL | Get-member | Out-GridView
[/sourcecode]

4. I decided to grab the “Information” property so I can display all the information, and verify that this is my local SQL instance:

[sourcecode language=”powershell”]
$MySQL.Information
[/sourcecode]

5. Finally, to remote connect to another machine, I just add the SQL Server InstanceName to the line creating my variable:

[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name"Â
[/sourcecode]
Or
[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name\Your-Instancename"
[/sourcecode]

Keep in mind, you can another variable holding your SQL Server name with the instancename and do something like:

[sourcecode language=”powershell”]
$SQLInstanceName = "Your-Machine-name\InstanceName"
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstanceName
$MySQL.Databases[$SourcedbName].tables | sort -desc dataspaceused | select Parent, `
@{Label="DisplayName";Expression={$_.Schema+"."+$_.Name};} | `
sort DisplayName;
[/sourcecode]

Again, no need to setup PSRemoting.

Simple Remoting #3

And this is the simplest of all, if you like to reuse your T-SQL scripts.  This will be your most used PowerShell SQLPS cmdlet “Invoke-SQLcmd“.

1. Load the SQLPS module:

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

2. Just start using the SQLPS “Invoke-SQLcmd” command: (basic form)

[sourcecode language=”powershell”]
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "."  #or

$SaveResult = Invoke-Sqlcmd -Query "SELECT * FROM [AdventureWorks].[dbo].[AWBuildVersion];" -ServerInstance "Your-Machine-name\InstanceName"

$SaveResult
[/sourcecode]

Again, there’s no need to be at the SQLServer: drive in order for this to work.  As you can see, with just a few lines of code you can connect to another SQL Server instance.

Go ahead and try it!

[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.12
# Created on: 8/31/2011 6:33 PM
# Created by: Max Trinidad
# Organization: PutItTogether
# Filename: DivideNow3.ps1
# http://thepowershellguy.com/blogs/posh/archive/2008/05/20/hey-powershell-guy-how-can-i-run-a-powershell-script-from-cmd-exe-and-return-an-errorlevel.aspx
# http://msdn.microsoft.com/en-us/library/system.environment.aspx
#========================================================================
Param([Int] $arg1, [Int] $arg2)
Try
{
[Double] $result = $arg1 / $arg2;
}
Catch
{
#Write-Host "Error in function";
$err1 = 1;
}
Finally
{
if($err1 -eq 1)
{
## – Custom Exit Code:
Exit 99;
}
Else
{
Write-Output $result;
Exit 0;
}
}

[/sourcecode]

Sample Script 2 – DivideNow2.ps1 – using “Exit(“N”).
[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.12
# Created on: 8/31/2011 6:33 PM
# Created by: Max Trinidad
# Organization: PutItTogether
# Filename: DivideNow2.ps1
# http://thepowershellguy.com/blogs/posh/archive/2008/05/20/hey-powershell-guy-how-can-i-run-a-powershell-script-from-cmd-exe-and-return-an-errorlevel.aspx
# http://msdn.microsoft.com/en-us/library/system.environment.aspx
#========================================================================
Param([Int] $arg1, [Int] $arg2)
Try
{
[Double] $result = $arg1 / $arg2;
}
Catch
{
#Write-Host "Error in function";
$err1 = 1;
}
Finally
{
if($err1 -eq 1)
{
## – Custom Exit Code:
Exit("99");
}
Else
{
Write-Output $result;
Exit("0");
}
}

[/sourcecode]

Results for DivideNow2.ps1

Sample Script 1 – DivideNow.ps1 – using .NET “[Environment]::Exit(“N”)”.
[sourcecode language=”powershell”]
# Organization: PutItTogether
# Filename: DivideNow.ps1
# http://thepowershellguy.com/blogs/posh/archive/2008/05/20/hey-powershell-guy-how-can-i-run-a-powershell-script-from-cmd-exe-and-return-an-errorlevel.aspx
# http://msdn.microsoft.com/en-us/library/system.environment.aspx
#========================================================================
Param([Int] $arg1, [Int] $arg2)
Try
{
[Double] $result = $arg1 / $arg2;
}
Catch
{
#Write-Host "Error in function";
$err1 = 1;
}
Finally
{
if($err1 -eq 1)
{
## – Custom Exit Code:
[Environment]::Exit("99");
}
Else
{
Write-Output $result;
[Environment]::Exit("0");
}
}

[/sourcecode]

Results for DivideNow.ps1

This is the results we want to get.  Both DOS and PowerShell will show the correct assigned Exit Codes.  This script and executable will work and can be integrated in an SSIS package.

Bonus Script – Executing a PS Script from PowerShell.exe in the PowerShell Console to execute DivideNow.ps1

[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.12
# Created on: 9/2/2011 9:21 AM
# Created by: Max Trinidad
# Organization: PutItTogether
# Filename: DivideNow_test.ps1
#========================================================================

# .\DivideNow.ps1 40 5 – This will execute and close PowerShell immediately
# .\DivideNow.ps1 40 0 – This will execute and close PowerShell immediately

# But if I execute with PowerShell.exe
PowerShell -noexit -command { `
cd ‘C:\Users\Max\Documents\SAPIEN\PrimalForms 2011\Files’; `
& ./DivideNow.ps1 40 5}

$LastExitCode

PowerShell -noexit -command { `
cd ‘C:\Users\Max\Documents\SAPIEN\PrimalForms 2011\Files’; `
& ./DivideNow.ps1 40 0}

$LastExitCode
[/sourcecode]

Happy PowerShelling