PowerShell SQL Server – Pipeline Binding Examples

powershellsql server

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).

Best Answer

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: enter image description here

Related Question