My Function Won’t Accept Parameters? Get-Help!

Getting Get-Help Help

The following is a recounting of an issue that I had and how I went about resolving it. No computers were harmed in the making of this post.


Ask me for one PowerShell command that everyone should know and I can answer you: Get-Help.

Fairly descriptive name if you ask me. Today I’m focusing on using Get-Help selectively to help me figure out why my custom function just won’t accept parameters!

You say Test Case. I say Basket Case.

We are going to need a custom test function for the audience to play along with at home, luckily Shane’s got you covered.
This is a Tactical Estimation of Shane’s Test function – aka T.E.S.T. function; very simple but all the important parts are there.

Function Test-FunctionByParameter {
    [cmdletbinding()]
    Param(
        [Parameter(Mandatory = $true,
                   ValueFromPipelineByPropertyName = $true)]
        [string]$Parameter
    )
    process {
        "Success, I'm [$Parameter]"
    }
}

If I’ve done my maths right, and I always do my maths right (as far as you know), then this function should take input from the pipeline and output it in the string “Success, I’m …”

Do I do my maths right?

Get-Service -Name *sql* |
Select Name -first 1 |
Test-FunctionByParameter
FirstFailure
It’s the “carrying the 1” that always gets me!

Huh, parameter problem.

I thought this was supposed to work like this. You pipe in information, magic happens, and functions work, no?

Well, when in doubt, Get-Help.

Get-Help:

Before I go any further though, just so that everyone knows how to use Get-Help, I’m going to show you one of the secret techniques for using Get-Help.

Get-Help *help*
GetHelp_Help

Yup, I use dbatools

Why does help exist?

When you think about it, why is there even a function called help?
As far as I’m aware it’s basically the same as Get-Help except it automatically pipes the output to | more so we get pages rather than a wall of text.

Is there more that we can do with Get-Help though? Is there a way that we can return the examples only? Syntax only? Parameters only?

Is there not a way that we can do such things?!

Yessum, the Possums cousin

Okay I cheated on the first one; examples are pretty easy. PowerShell actually already takes care of that for you.

Get-Help Get-Help -examples
GetHelp_GetHelp_Examples
Help me if you can I’m feeling examples…I mean down!

The other two, while not laid out for you as pretty as that, are not that difficult to do. What needs to be remembered about Get-Help is that it is a cmdlet. And what do cmdlets normally output?…

What?! No! Objects!
They normally output Objects! Wow…next time just pipe it to Get-Member if you don’t know.

I Object!

Now I first saw this done in a blog post by Adam Bertram ( blog | twitter ) but I do believe that it warrants further highlighting.

If you did pipe Get-Help to | Get-Member you would have seen a NoteProperty called syntax, so if we want the syntax for a cmdlet, we can specify that using:

(Get-Help Get-Help).syntax
GetHelp_GetHelp_Syntax.PNG
Syntax, useful for all languages

So for parameters we need…yup .parameters.

(Get-Help Get-Help).parameters
GetHelp_GetHelp_Parameters
Parameters…languages use them as well I guess

Hmm, not as handy as I thought it would be. What happens if we pipe that to Get-Member (Alias gm as I’m getting lazy here)?

(Get-Help Get-Help).parameters | gm
GetHelp_GetHelp_Parameters_GM.PNG
Well lookie here, another NoteProperty!

Let’s try that and see what we get, shall we?

(Get-Help Get-Help).parameters.parameter
GetHelp_GetHelp_Parameters
…the exact same ūüė° Fine, have the same screenshot then!

It’s always brightest before the dawn

And the answer comes always before you smash your screen in rage.

If we pipe the above information to Get-Member again, we get more useful information this time (I’m not going to show it, you know how to pipe to gm by now).

GetHelp_GetHelp_Parameters_GM_Useful
This looks like something we can work with ūüôā

I’m from a database background so can we make this pretty, all I care about is the name and the pipeline input.

(Get-Help Get-Help).parameters.parameter |
    Select-Object -Property name,pipelineinput
GetHelp_NamePipelineInput.PNG
ByPropertyName…what’s that?

By Odin’s Beard! I mean PropertyName

You know one of these days I should really read this help file (you should too) because half way down the results of the following code is some interesting info…

help about_pipelines

METHODS OF ACCEPTING PIPELINE INPUT

Cmdlets parameters can accept pipeline input in one of two different ways:

— ByValue: Parameters that accept input “by value” can accept piped objects
that have the same .NET type as their parameter value or objects that can be
converted to that type.

For example, the Name parameter of Start-Service accepts pipeline input
by value. It can accept string objects or objects that can be converted to
strings.

— ByPropertyName: Parameters that accept input “by property name” can accept piped
objects only when a property of the object has the same name as the parameter.

For example, the Name parameter of Start-Service can accept objects that have
a Name property.

(To list the properties of an object, pipe it to Get-Member.)

Some parameters can accept objects by value or by property name. These parameters are
designed to take input from the pipeline easily.

So that’s the problem?! The names need to match up! I can do that with Select-Object!

All I need to do is add a custom label using @{Label='<custom label>';Expression={'<custom expression>'}}

Try{}Catch{}Finally{}

Get-Service -Name *sql* |
Select-Object -First 1 -Property @{l='Parameter';e={$_.Name}} |
Test-FunctionByParameter
ParameterName_TestFunction
I always do my maths right!

So now when I run a command and get the crazy…

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

I can just run:

(Get-Help <cmdlet name>).parameters.parameter |
Select-Object Name,pipelineInput

And know exactly where to fix! ūüôā

T-SQL Tuesday #88 – The daily (database related) WTF! The Biggest Danger to your Database: Me.

That is more of a blurb than a title…and this is more an apology than a blog post…

Kennie Nybo Pontoppidan ( blog | twitter ) has the honour of hosting this month’s T-SQL Tuesday and has decided to base this month’s topic on ‘The Daily (database related) WTF‘.

Now I have great time for Kennie and T-SQL Tuesday since my very first blog post was in reply to a T-SQL Tuesday and it happened to be a topic where Kennie blogged about the exact same thing!

Now, truth be told, I wasn’t planning on participating in this one and this wasn’t because of not having a WTF moment, but rather having too many of them. However, reading through most of the entries, I see a vast majority of them are about moments well in the past and caused by other parties.

This is not the case for me. My WTF moment happened recently and the culprit was … myself.

Sorry Kennie ūüė¶

Friday:

A request came in from our Developers about a slow performing query and my Senior DBA identifies an index that can be safely modified to improve this ones performance.
So a Maintenance Window was set and it fell to me, in my role of Junior DBA, to create a SQL Agent Job to create this index.

No worries so far right?

I create a once-off SQL Agent Job to create this index, scheduled it appropriately, and I’m off on my merry way for the weekend.

Monday:

I come in on Monday morning, ¬†check my email, and I see an alert in my inbox about my job as well as an email from my Senior DBA; He’s not angry…WTF?

My whole job had failed!

Unable to connect to SQL Server ‘(local)’. The step failed.

01. SQLAgentError
WTF!

He is not angry as he has seen this error message before, has dealt with it before, and sees it as a case of “well you’ve seen it now, investigate it and you won’t fall for it again”.

A quick investigation later pointed to this in the Error Log the moment before the SQL Agent Job Step was supposed to run:

[165] ODBC Error: 0, Connecting to a mirrored SQL Server instance using the MultiSubnetFailover connection option is not supported. [SQLSTATE IMH01]

04. ErrorLogMessage
WTF?

Long sub-story short (i.e. Google-fu was involved), the main reason that this failed is that the SQL Agent Job Step has been configured to use a Database that is currently a mirrored one.
And SQL Agent does not like when you try to start off a step in a database that is mirrored.

02. WrongDBSetUp
WTF is wrong with this?

So the solution for me was to set the Job Step property ‚ÄėDatabase‚Äô to a non-mirrored database (preferred: [master]), then include a ‚ÄúUSE [<mirrored database>]‚ÄĚ in the ‚ÄėCommand‚Äô property.

03. RightDBSetUp
WTF!

Knowing what to do now, and having identified another maintenance window for the next morning, I make the required changes to the job step and continue on with my day.

Tuesday:

I come in on Tuesday morning, ¬†check my email, and I see an alert in my inbox about my job as well as an email from my Senior DBA; He’s angry…WTF?

My final job step had failed!

CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). ¬†The step failed

05. SecondSQLAgentError
WTF!

Now I’m angry too¬†since I count these failures as personal and I don’t like failing, so I get cracking on the investigation.
Straight away, that error message doesn’t help my mood.
I’m not indexing a view!
I’m not including computed columns!
It’s not a filtered index!
The columns are not xml data types, or spatial operations!
And nowhere, nowhere am I using double quotes to justify needing to set QUOTED_IDENTIFIER on!

SO WTF SQL SERVER, WHY ARE YOU GIVING ME THESE ERRORS???

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

I’ve talked about stupid error message before…¬†but in my current mood I wail, beat my breast, and stamp my feet!
The error message above was not complaining about the index I was creating, it was complaining about indexes already on the table!
In my case, we had filtered indexes already created on the table and, as such, every single index on this table from then on requires SET QUOTED_IDENTIFIER ON.

USE [TEST];
SET QUOTED_IDENTIFIER ON;

CREATE ...

Third Time’s the Charm?

No, not this time.

Luckily the Senior DBA had come in while the maintenance window was still running and manually ran the create index script.

He wasn’t angry that my job step failed. He was angry that my first job step succeeded!

Are you going “WTF? Why is he angry about that?” Let me enlighten you…

Remember at the start of this blog post I said that he had identified an index that could be safely modified?
Well, on Monday, in my haste to fix my broken job I had focused too much and thought too granular.
My second job step that created the index had failed, but my first job step, the one that dropped the original index had succeeded.

There’s not really much more to say on this. In my rush to fix a broken job, I created a stupid scenario that was luckily caught by the Senior DBA.

Wrap Up:

Yeah…so thought it would be a nice, little counter-example to the other posts out there about third parties coming along and wrecking havoc, and the DBAs swooping in to save the day.

I could make up excuses and say that, as a Junior DBA, I’m expected to make mistakes but I’m not going to.

It should be the aspiration of every Junior DBA to strive to improve and move upwards, and one of the key aspects of this is responsibility.

You should be responsible for looking after the data, looking after the jobs, and looking after the business.
And if all else fails, you should be responsible for your actions.

I have been properly chastised by my Senior and am still chastising myself for this. It’s been a long week so far…

… and it’s only Tuesday…wtf?

[PowerShell] Getting More From Generic Error Messages.

There’s more to $error than meets the eye.

What we know already:

SQL Server has some really stupid, generic error messages.
Case in point…

String or binary data would be truncated.

Yes, we know what it means but what column would be truncated? What value would be the offender here?
I am okay with not having the exact answer but it would be nice to have more!

What I learned:

PowerShell actually has some pretty generic error messages as well.
Since I am using PowerShell mainly for interacting with multiple SQL instances, my PowerShell errors mainly revolve around SQL Server.
So this error message is not helpful.

initialerrormessage

(I’m slightly colour-blind so I can barely read red on blue, I find this green (yellow?) easier)

Can we get more?

Sure we can but let’s set up an example so you can play-along at home too.

First of all, what PowerShell version are we using?

$PSVersionTable.PSVersion
psversion
Latest as of…when I updated it

Great! So let us add in our assemblies that will allow us to connect to SQL Server using SMO.

# Load the assembly since we probably do not have it loaded
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')
assembly_loading
This is technically depreciated but I’m not going to remember that whole location…

Now I like the results showing up but if you don’t want them, just throw a $null = ¬†before the [System.Re...¬†bit.

# SILENTLY load the assembly since we probably do not have it loaded
$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')

Now let us connect to mine (or your) database to run some scripts against it.

# Connect to the instance and database
$SQLInstance = New-Object Microsoft.SqlServer.Management.Smo.Server 'localhost'
$Database = New-Object Microsoft.SqlServer.Management.Smo.Database
$Database = $SQLInstance.Databases.Item('Pantheon')

Everything is getting thrown into a variable/object here so there is going to be no output. Just change the ‘localhost’ bit to your server and ‘Pantheon’ to your test database.

Now, let’s get our T-SQL on!

# Create our T-SQL statement.
$sql = 'SELECT SERVERPROPERTY('ProductLevel') AS What?, SERVERPROPERTY('ProductVersion') AS Huh?;'
initialsqlerror
PowerShell & SQL…

You can see the first problem we run into here. The single quotation marks are breaking up our statement.
There are 2 fixes for this; we can double quotation mark the start and end of the string e.g. "SELECT ..." or we can do what we normally do in SQL Server and double up the single quotation marks e.g. (''ProductLevel'').
I’ve gone with the latter but hey, you choose, go crazy, whatever you want!

So now we have this:

initialsqlfix
You can probably already spot the error here from a T-SQL viewpoint…

Now let us run this against our database and see what happens.

# Execute with results...kinda like it says...
$Database.ExecuteWithResults($sql).Tables
initialerrormessage
GENERIC MESSAGE ALERT!

The whole reason for this blog post i.e. stupid, generic error message.

Now ignoring the fact that you already know what is wrong, this tells me that there is either something wrong with the $Database variable, the $sql variable or the syntax statement. Maybe even something else though!
This is not helpful and I’m going to have a bad time.

I encountered this lately and thanks to Chrissy LeMaire ( b | t ), I was introduced to the $error variable.
You can look up what this guy does by running the following on PowerShell,

help about_automatic_variables -showwindow

but the main point is that $error¬†…

Contains an array of error objects that represent the most
recent errors. The most recent error is the first error object in the
array ($Error[0]).

So we want more information about our error message so we go…

$Error[0]

And we get…

initialerrorvariable
…well at least I can read it easier…

the same…
This…this is not what I wanted.
Thankfully, the defintion states that it is an error object and we know that objects can have more properties than what is shown be default.

So we try again, making sure that we return everything

# More than Generic
$Error[0] | Select-Object *
initialerrorvariableall
Great, now “More than a Feeling” is stuck in my head…

Bingo, that is a lot more helpful! Especially when we scan the results and we see this guy (highlighted)

initialerrorvariableallhighlighted
You saw that that was going to be it, right?

We may be working with PowerShell but we still have to obey SQL Server’s rules. So if we want to have a column with a question mark, we’re going to need to wrap it in square brackets.
So let’s fix up our $sql¬†variable and try again.

# fix me!
$sql = 'SELECT SERVERPROPERTY(''ProductLevel'') AS [What?], SERVERPROPERTY(''ProductVersion'') AS [Huh?];'

We re-run out execute…

#Execute with results...kinda like it says...
$Database.ExecuteWithResults($sql).Tables

Lo-and-behold!

results
Those are stupid columns names, to be fair…

Like a sheepdog, let’s round it up:

I’m liking PowerShell more and more as I use it.

That is mainly outside of work but I’ve already turned my gathering of daily checks data from a half hour long process to a 2 minute one.

So it’s nice to know that, while it may have stupid, generic error messages, it also has the tools to help you with them.

Now if we could only get the tools to deal with “String or binary data would be truncated”…