Introduction
I was excited hear about powershell feature in SQL server 2008. Initially, it was not clear to me what it was, yet, I was enthused about learning something new. I imagined power shell would involve something in the command line. What happens after that was a complete mystery to me. Anyways, I managed to spend some extra time to learn about the powershell technology. I am going to split this article into multiple series. In the 1st part, I will cover how to work with variables and various helpful commandlets. The examples in this article assumes SQL server 2008, and windows powershell 1.0 is present.
Background
Powershell is an extended shell that allows us to manipulate objects and do various scripting tasks. It takes the adavantage of .NET. We will often run into CmdLet in powershell. A commandlet is a .NET object that exposes functionalities for us. For example, there is a cmdlet called "New-Variable" which can be used to declare variables.
Using the code
To start running powershell type in "sqlps" in command line prompt. If you do not know what cmdlets are available for use then simply type in "
get-alias". This handy little cmdlet can show you the shorthand for the available cmdlet. This will get you started so you can start exploring cmdlets. There is another cmdlet named "
get-command" that lists all available cmdlets.
PS SQLSERVER:\> get-alias
CommandType Name Definition
----------- ---- ----------
Alias ac Add-Content
Alias asnp Add-PSSnapin
Alias clc Clear-Content
When I started working with powershell, I struggled to find documentation on cmdlet. I looked in books online and it did not have much at that time. Only some references were there related to cmd-lets. Anyways, there is no need to despair. A cmdlet called "Get-Help" that can help you pull cmdlet related documentation from the command line. You need to supply the name of the cmdlet as the argument. Other useful arguments available for most cmdlets include "-detailed", "-full". Those flags allows you to bring additional information on the parameter. Amongst the, the "-full" flag is the most comprehensive. Also, if you type in "Get-Help *" then you can see a list of all available help topics. Powershell documentation reminded me of the manpages in unix computer and the RTM mantra.
Strings, variables, objects:
Declaring and working with variables is very simple. It can be achieved via cmdlets such as "New-Variable", "Set-Variable". Also, we can declare it like one would do it in scripting languages like perl, php.
PS SQLSERVER:\> $rnd = New-Object "Random"
PS SQLSERVER:\> $rnd.Next()
778277127
PS SQLSERVER:\> $str = "Me"
PS SQLSERVER:\> $i = 1
In the output above, we declared a variable namely
<var>$rnd</var> using the Random class in .NET framework. The next line invoked the
Next() method to generate the random number. The
New-Object cmdlet can help us instantiate any .NET framework classes. However, keep it in mind that we have access to a subset of .NET framework classes. We can refer to any class that SMO can refer to. That's the only constraint I found here. In addition to building reference type variables, we can also create primitive types such as string and integer. The example above
$str,
$i does that respectively.
How do I know which variable is of which type? It's fair question due to the fact that there are no enforcement of type declaration. We simply call the method
GetType() on the variable. See the output below.
PS SQLSERVER:\> $i.GetType()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Int32 System.ValueType
There are special variables in powershell. Here is a list of them and what they do:
1.
$_ - Contains the current object in the pipeline, used in script blocks, filters, and the where statement.
2.
$Args - Contains an array of the parameters passed to a function from command line.
3.
$Error - the object containing error information.
4.
$Home - user's home directory.
5.
$PsHome - Windows PowerShell installation directory.
Points of Interest
I presented the article materials at the SQL Server user group several months ago. I learned from one of the MS employee that powershell 2.0 was coming out. The new version is expected to cover remoting issues.
History
None Yet
