Max Trinidad – The PowerShell Front
A little of everything about PowerShell!!!
About PowerShell.
Here’s some links you check on available cmdlets for SQL Azure:
This something to keep in mind. Azure is here to stay!
While working on my book, on my chapter about working with objects, I went through a series of examples on using PSObject and Hash Tables which made me realized that creating NoteProperties is a thing of the past. Thanks to the “-Property” parameter whe using the New-Object which allows you to use your Hash Table content and build your object Property member type.
Take a look at this previous blog post by the PowerShell Team: http://blogs.msdn.com/b/powershell/archive/2009/12/05/new-object-psobject-property-hashtable.aspx
So, while answering the forum post about “NoteProperties on an ArrayList”, I found myself creating a Multi-Dimension Array on a PSObject. In my example, I’m trying the create two different Hash Tables, $o1 and $o2, containing the same property with some values. Then, I’m adding them to the my $a1 PSObject variable using the “+=” operator and this have allow me to create a Multi-Dimension array.
Here’s the code:
## Creating multi-dimension array
[Array] $a1 = $null
$o1= @{ Num = “12”,”34″}
$a1 = new-object PSObject -Property $o1
foreach($i in $a) { $i.num }
$o2 = @{Num = “2”,”4″}
$a1 += new-object PSObject -Property $o2
$a1
foreach($i in $a1) { $i.num }
# Display one of the Dimension Element:
$a1[1].num[1] # will return value = 4
$a1[0].num[1] # will return value = 34
Here’s the image with the results:
Now, let’s take a look at the $a1 list of member types using Get-Member:
Yes, although we use the “-Property” in the New-Object, it will create the NoteProperty for you.
So would you rather code using Add-Member:
Add-Member -InputObject $o1 -Type NoteProperty -Name Num -Value 12
Or, use the Hash Table concept:
$o1= @{ Num = “12”,”34″}
Well, just let your imagination go!!
a. Copy/Paste the “\en” folder to the “SQLPSv2” folder”.b. Copy/Paste these 2 *dll’s files: “Microsoft.SqlServer.Management.PSProvider.dll” & “Microsoft.SqlServer.Management.PSSnapin.dll”.c. Copy/Paste these 2 *ps1xml files: “SQLProvider.Format.ps1xml” & “SQLProvider.Types.ps1xml”.
| Directory: C:\Users\User01\Documents\WindowsPowerShell\Modules\SQLPSV2 Mode LastWriteTime Length Name —- ————- —— —- d—- 11/10/2010 9:59 PM en -a— 9/25/2010 8:32 PM 92000 Microsoft.SqlServer.Management.PSProvider.dll -a— 9/25/2010 8:32 PM 83808 Microsoft.SqlServer.Management.PSSnapins.dll -a— 9/21/2010 6:14 PM 84619 SQLProvider.Format.ps1xml -a— 9/21/2010 6:14 PM 15552 SQLProvider.Types.ps1xml -a— 7/8/2010 4:38 PM 374 SqlpsV2.ps1 -a— 11/10/2010 10:05 PM 1062 SqlpsV2.psd1 |
| PS C:\> PS C:\> Import-module SQLPSv2 -DisableNameChecking PS C:\> PS C:\> GCM *sql* -CommandType Cmdlet | Select nameName —- Add-SqlAvailabilityGroupDatabase Decode-SqlName Encode-SqlName Invoke-Sqlcmd Join-SqlAvailabilityGroup New-SqlAvailabilityGroup New-SqlAvailabilityReplica Remove-SqlAvailabilityGroup Resume-SqlAvailabilityGroupDatabase Suspend-SqlAvailabilityGroupDatabase Switch-SqlAvailabilityGroup PS C:\> cd SQLServer:\SQL\MAX-PCWIN1\MSQLDENALI01\Databases
PS SQLSERVER:\SQL\MAX-PCWIN1\MSQLDENALI01\Databases> |
Yes! As many SQL PASS members already know, the next SQL Server CTP1 is available for download (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9). Go and get it!
I was able to installed it on my Windows 7 64bit version without a glitch. I had a problem installing in a Windows Server 2008 SP2 Virtual Machine, but I’m sure it because I needed access to the internet. So, make sure you got internet connectivity.
The new SQL Server CTP1 has SQL Server Management Studio Denali GUI is based on Visual Studio 2010. It’s Impressive!!
There’s a few things to know:
1. Prior installing SQL Server Denali CTP1, you need the following:
1. Install PowerShell V2 – Look for Windows Management Framework: http://support.microsoft.com/kb/968929
b. .NET Framework 3.5
2. The Get-Command bug is fix and there’s are a few new PowerShell cmdlets:
|
Add-SqlAvailabilityGroupDatabase |
Of course, don’t forget to include the new set SQL cmdlets from the “MasterDataServices” snapin, already introduce in SQL Server 2008 R2 (See my blog: http://www.maxtblog.com/index.php/2010/09/surprise-sql-server-2008-r2-has-new-cmdlets/)
I’m very excited about this new product and expect to see more PowerShell cmdlets later in the process. KUDOS to the Microsoft SQL Server Team!!
I will be blogging more later. Happy PowerShelling!
Go ahead! Download my presentation and samples scripts from this weekend Worldwide Online Techday session. The slide will provide useful information and the scripts are loaded with interesting code you don’t want to miss.
Click on the link to download the file: (Please rename the file to a *.zip to extract content)
Again, Thanks to all the organizers and sponsor’s for having me participate in this event.
Happy PowerShelling!!
I would say that cleaning these left over *.svn folders can be a pain if you have then in lots of subfolders. When I realized using PowerShell, it can be very easy to cleanup. As you know, these folders are hidden and will be propagated to other folders. So, here’s a one liner that can get rid of these folders:
dir . -include *.svn -Recurse -force | Del -Recurse -force
Simple enough! The first half of the line is a “Dir”, the alias for “Get-ChildItem”, which will include all the *.svn objects repeatedly under the current folder and forcing to look for hidden ones.
dir . -include *.svn -Recurse –force
Now, the other half, when follow by the “|” pipe symbol, this will pass all the objects collected from the first command and pass it to the next command. In this case “Del”, the alias for “Remove-Item”, will get rid of all the objects found. You must have both parameters: “ –Recurse” and “ –Force” in order to work.
Del -Recurse -force
Put it together and there’s no registry hacking, or building a batch file.
Just plain and simple PowerShell!
Finally, after three years, I’ve seen a lot more DBA’s blogging about their solutions combining T-SQL and PowerShell. KUDOS To Everyone!!! One thing PowerShell has bring to the table is the ease of getting things done. Still, there’s a learning curve everyone has to overcome. The truth is, the more you use it, the harder is to let go. So, here’s some things that might help the DBA’s:
1. Be careful with PowerShell ISE. It does have a bug that you won’t notice until you may try to do a copy/paste of your code into a different editor or into your PowerShell Console. And, this it due to the editor saving as a “UNICODE” and not ASCII. You will seen your lines of code in one single line when viewing the script on a different editor, such as notepad . Again, just be careful.
By the way, this bug has been document in Microsoft Connect and there’s a few blogs about a work around: http://www.nivot.org/2010/05/21/PowerShellISEHackingChangeDefaultSaveEncodingToASCII.aspx http://dougfinke.com/blog/index.php/2009/07/20/how-to-work-around-a-powershell-integrated-scripting-environment-bug/
2. Do you know? If you don’t want to install SQL Engine but want to take advantage to use PowerShell and manage SQL Server, then just install “SQL Server Management Studio 2008 R2” and you can use SQLPS. Yes! Why adding unnecessary processes to consume your system resources. Here’s the link: http://www.microsoft.com/downloads/en/details.aspx?familyid=56AD557C-03E6-4369-9C1D-E81B33D8026B&displaylang=en
3. SQL Server 2008 R2 may not have lots of commands. But, try to use Invoke-SQLCmd, this is a very powerful cmdlet specially when you learn to use it to extract data using T-SQL and Stored-Procedures that generate results. Keep in mind, you may export the results to different file format. Check out this example:
| ## Make sure SQLPS is loaded in your session$s = Invoke-SQLCmd -database master -Query ‘exec sp_who’
$s | Select spid, status, loginame, cmd, blk | out-gridview |
This is a good sample of collecting SQL Servers information into a .NET variable and displaying the results using “Out-GridView”. Also, you could have use “Out-file” command and save it in your drive. PowerShell give you lots of options to handle information. Think out side of the box!
4. Don’t be afraid to learning new technologies, specially .NET Framework. You are probably getting into it if you’re working building SSIS packages. PowerShell syntax looks very similar to C# and you can extend scripting with .NET Framework. A good example would be if you want to add to send an email from your script. The issue is, you can’t run any of the new PowerShell V2 commands under SQLPS and/or SQL Agent. You will need to use straight .NET code to by pass this limitation. SQLPS is PowerShell V1 mini-shell (more info:http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!842.entry ).
Example:
| ## – This is the .NET Framework way to create a send email step and can be ##use for both PowerShell V1/V2 ## Send email with monitoring results $mailmess = new-object system.net.mail.mailmessage $mailmess.From = “FromUser@Company.com” $mailmess.To.Add(“ToUser@Company.com”) $mailmess.Subject = “Type Any Subject here…” $mailmess.Body = “Type Any Detail here…”; $mailmess.Attachments.Add($Filename) $mailclient = New-Object System.Net.Mail.SmtpClient $mailclient.host = “Your.SMTP.PostOffice” $mailclient.send($mailmess) ## – This is the PowerShell V2 cmdlet to send email. ( will not run under SQLPS.exe ## You can only run PowerShell V2 scripts in the SQL Server Agent if you include to |
There’s more but I think I will leave some for later. For those in the Florida area, I will seen you soon at the next SQLSaturday #49 in Orlando and at the SW Florida .NET Developers Group in Fort Myers.
Happy PowerShelling!!
Here’s a quick way you can use PowerShell to script a Stored-Procedure with a few one-liners. This is in its basic form and you can use this code to build your own functions. I will be including both an SMO and a SQLPS versions. This is how it’s done:
Here’s the SMO version: (copy/Paste code)
### – This is the SMO Version – ###
## Load the SMO Assembly V1 version (works in V2)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
## Connect to the SQL Server and get the Stored-procedures
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) “YourServerName”
$List_storedProc = $MySQL.Databases[“Master”].StoredProcedures
## List the first 10 procedures
$List_storedProc | Select -first 10 schema, name | ft -auto
## Display selected script
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script()
## Save the script to a file
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql
In the above section, you have all the necessary SMO code to create a SQL script of a Stored-Procedure. Just change the database name and you will be able to list all the SQL Stored-Procedures for that database.
Now, here’s the SQLPS version: (copy/Paste code)
### – This is the SQLPS Version – ###
## – Change directory to the Stored-procedures folder
cd SQLSERVER:\SQL\MAX-PCWIN1\DEFAULT\Databases\master\StoredProcedures
## – Directory list the first 10 Stored-Procedures using the “-force” parameter
dir -force | select -first 10
## – Stored the result in a variable
$s = dir -force
## Display selected script
($s | ? {$_.Name -eq “Sp_Configure”}).script()
## – save the scripted item to a files
($s | ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql
Using SQLPS.exe or loading the SQLServer provider in your profile to gain access to your SQL engine components, it gives you a slightly edge because all the needed SMO assemblies gets loaded for you. Even if you install SQL Server 2008, PowerShell itself will not load these SMO Assemblies, it has to be part of your PowerShell profile, a separate script, or in a module.
In SQLPS, or from the PS Console (or ISE prompt) with the SQLPS module loaded, you can use the change directory “CD” path provided in the sample and change the database name with the instance ( ..\Max-PCwin1\Default\..) to yours ( ..\YourServerName\Default_or_YourInstanceName\.. ). That’s it!
cd SQLSERVER:\SQL\SrvName\DEFAULT_or_InstName\Databases\master\StoredProcedures
Go ahead and try it! You will appreciate PowerShell a lot.
I was playing around creating a PowerShell script with the intention of add items to a custom PSObject using the Add-Member. Well, I couldn’t get it to work or maybe I was still doing something wrong. But then, I came up with a (kind-of) unorthodox method that I will share with you. Please, don’t tell anyone:
First, here’s a PowerShell script function “Get-SPInventory” I found in Technet; (http://technet.microsoft.com/en-us/magazine/2008.12.windowspowershell.aspx?pr=blog). There’s nothing wrong with it and work as expected:
Function Get-SPInventory {
PROCESS {
$wmi = Get-WmiObject Win32_OperatingSystem –comp $_ | Select CSName,BuildNumber,ServicePackMajorVersion
$obj = New-Object PSObject
$obj | Add-Member NoteProperty BuildNumber ($wmi.BuildNumber)
$obj | Add-Member NoteProperty CSName ($wmi.CSName)
$obj | Add-Member NoteProperty SPVersion ($wmi.ServicePackMajorVersion)
$wmi = Get-WmiObject Win32_BIOS –comp $_ | Select SerialNumber
$obj | Add-Member NoteProperty BIOSSerial ($wmi.SerialNumber)
Write-Output $obj
}
} 
So, I going to duplicate the same functionality in a different way without creating a new PSObject and not using Add-Member. I’m going to let PowerShell to create the PSObject for me and work a little with the Select-Object. Here’s my version:
Function Get-SPInventory2 {
Process{
$wmi1 = Get-WmiObject Win32_OperatingSystem –comp $_ | Select CSName,BuildNumber,ServicePackMajorVersion
$wmi2 = Get-WmiObject Win32_BIOS –comp $_ | select SerialNumber
$results = $wmi1|select csname,BuildNumber,@{Label=”SPVersion”;Expression={$_.ServicePackMajorVersion -as [int]};},@{Label=”SerialNum”;Expression={$wmi2.SerialNumber};}
Write-Output $results
}
}
Now, I got both functions generating the same results. I’m my script, the “Label=” will create the noteproperty and the “Expression=” will store the value for the $wmi2.SerialNumber.
Let say I want to use SQL Server Management Object (SMO) to collect various information from some SQL Servers on the network. Here’s another sample script: (make sure your SMO assembly is loaded)
$SQLSvr = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($false) | Select name [Array] $SQLinfo = ForEach($SQL in $SQLSvr){
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQL.Name
$MySQL | Select NetName, Product, EngineEdition, Version, Platform, ProductLevel
} $SQLinfo | select Netname, Product, EngineEdition, Version, ProductLevel | ft -auto
In this script, I’m using the SMO SQL enumerator to collect the SQL Server names so we can use the “foreach” to collect the information from each server into the output array “$SQLinfo”. After building the output variable then you can save the results in different output format such as: CSV, TXT, XML, or event back to a SQL table.
This is another way to collect information and let PowerShell do the rest for you! PowerShell gives Power to the scripters.
Check out this other blog about creating objects: http://powershell.com/cs/blogs/tobias/archive/2010/09/22/creating-objects-yourself-and-a-bunch-of-cool-things-you-can-do-with-them.aspx
Happy PowerShelling!!
Please feel free to download my presentation given at the “Southwest Florida .NET Code Camp 2010” today Saturday, Sept. 25th in the morning. Please, after downloading the file, rename it to a *.zip file.
Click here to download presentation:< >
I’m sorry that one hour wasn’t enough, but it was nice that the group wanted to see more. Let’s plan for another user group session in that area. I will be glad to give this session again.
Once again, Thanks to the organizers for letting me speak and to all attendees for coming over the sit at the Code Camp sessions.
Happy PowerShelling!!