PowerShell Pipeline Binding examples on SQL server?

Question

I am learning PowerShell, my training machine (VM) is a Windows Server 2012 R2, with SQL 2014 and PS 4.0.

I am try get a handle on the two Pipeline Binding methods ByValue & ByPropertyName. I am a hands on learner so I like to try things and play with them a bit to get the real feel of how they are doing what.

Some of the examples I am seeing are sub optimal for playing with

Get-Process | Stop-Process (**really bad idea, don't try this at home or work**)

Or they are focused on things not SQL (like IE)

I know how to read Get-Help x-x -Full looking for a couple of SQL centered pipelines I can play with that won't crash my server (nor be resume producing events).

Answer 1

Getting specific to SQL Server would require there be any cmdlets within SQLPS that actually use the ByValue or ByPropertyName binding. I only know of a few like those for the Backup or Restore, but don't really use them. I think the ones around Azure SQL might use them as well (e.g. Get-SqlInstance). So in the end this is really nothing to do with SQL Server itself, just the way PowerShell works...but still worth learning about I think.

The best way for you to see how they work is just use Trace-Command to look at the binding metadata as PowerShell does it. A simple example would be piping something to a cmdlet like Get-Date or Get-Service.

I am by no means an internal's guy when it comes to PowerShell. Up front from what I can find, ByValue is not actually used or maybe just has a lower precedence than ByPropertyName. If you look at use of this command on my local laptop:

Trace-Command -Name ParameterBinding -Expression {'mssql$number14' | Get-Service | Restart-Service} -PSHost

The output of this command shows a few things:

DEBUG: ParameterBinding Information: 0 : BIND PIPELINE object to parameters: [Get-Service] DEBUG: ParameterBinding Information: 0 : PIPELINE object TYPE = [System.String]
DEBUG: ParameterBinding Information: 0 : RESTORING pipeline parameter's original values DEBUG: ParameterBinding Information: 0 : Parameter [Name] PIPELINE INPUT ValueFromPipeline NO COERCION
DEBUG: ParameterBinding Information: 0 : BIND arg [mssql$number14] to parameter [Name] DEBUG: ParameterBinding Information: 0 : Binding collection parameter Name: argument type [String], parameter type [System.String[]], collection type Array, element type [System.String], no coerceElementType

The one thing I get form this is that the ValueFromPipeline was not forced, PowerShell went with the ValueByPropertyName and bound the text string I passed in to the Name property.

If you want to see the full output of this: