Tag Archives: Help

Hitting the Wall – #TSQL2sDay

Posted on by

farrel-nobel-97504-unsplashPhoto by Farrel Nobel on Unsplash

Welcome to another edition of T-SQL Tuesday!

This T-SQL Tuesday is hosted by Wayne Sheffield ( blog | twitter ) and he has asked us to talk about

[…] a time when you ran up against your own brick wall, and how you worked it out or dealt with it.

Hitting The Wall

When stuck in a problem in the past, I could often be found glued to a keyboard and screen for many hours. I would try this way and that way, I would Google and read Stack Overflow looking for ways around the particular issue I was seeing trying different things and finally I would get frustrated and fed up and stop.

Maybe I would go and walk the dog, maybe just sit somewhere else but I would often find that I had an idea how to solve my problem and quickly go back to the keyboard frustrated that I hadnt thought about this earlier and frequently rinse and repeat.

A Different Way

It took me many years to realise this and I wish I had done so sooner but once I made the connection that leaving the problem to one side for a little while meant that I often found a way to a solution for a problem I started setting a time limit.

30 minutes

If I have been stuck on a problem for 30 minutes, I (mostly, I still sometimes fail at this) stop, take a break, go for a walk or do something different and the number of times that I arrive if not at a solution then at a path to a solution is remarkable.

Ask

The other thing to do at this point in the troublesome problem solving is to ask. Twitter, Google, Slack, Stack Overflow. These are all excellent resources where you can quickly find people who are willing and capable of helping.

Don’t be like me and take years to work this out 🙂

VS Code – Automatic Dynamic PowerShell Help

Posted on by

VS Code is my coding tool of choice. I love that one lightweight editor can do so much and as PowerShell is usually the language that I write in I really love the PowerShell extension

Help

When you write a PowerShell function that is going to be used by someone other than you, you don’t want to be the guy or gal that has to support it indefinitely. You should write good help to enable your users to simply type

Get-Help NAMEOFCOMMAND

and get all of the help that they need to use the command

If we look at one of my favourite dbatools commands Get-DbaLastGoodCheckDB we can see this in action.

Get-Help Get-DbaLastGoodCheckDb -full

This returns

NAME
Get-DbaLastGoodCheckDb

SYNOPSIS
Get date/time for last known good DBCC CHECKDB

SYNTAX
Get-DbaLastGoodCheckDb [-SqlInstance] [[-SqlCredential] ] [-Silent] []

DESCRIPTION
Retrieves and compares the date/time for the last known good DBCC CHECKDB, as well as the creation date/time for the database.

This function supports SQL Server 2005+

Please note that this script uses the DBCC DBINFO() WITH TABLERESULTS. DBCC DBINFO has several known weak points, such as:
– DBCC DBINFO is an undocumented feature/command.
– The LastKnowGood timestamp is updated when a DBCC CHECKFILEGROUP is performed.
– The LastKnowGood timestamp is updated when a DBCC CHECKDB WITH PHYSICAL_ONLY is performed.
– The LastKnowGood timestamp does not get updated when a database in READ_ONLY.

An empty ($null) LastGoodCheckDb result indicates that a good DBCC CHECKDB has never been performed.

SQL Server 2008R2 has a “bug” that causes each databases to possess two dbi_dbccLastKnownGood fields, instead of the normal one.
This script will only displaythis function to only display the newest timestamp. If -Verbose is specified, the function will announce every time
more than one dbi_dbccLastKnownGood fields is encountered.

PARAMETERS
-SqlInstance
The SQL Server that you’re connecting to.

Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false

-SqlCredential Credential object used to connect to the SQL Server as a different user

Required? false
Position? 2
Default value
Accept pipeline input? false
Accept wildcard characters? false

-Silent []
Use this switch to disable any kind of verbose messages

Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

INPUTS

OUTPUTS

NOTES

Copyright (C) 2016 Jakob Bindslet (jakob@bindslet.dk)

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see .

————————– EXAMPLE 1 ————————–

PS C:\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\sql987

Returns a custom object displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated, DaysSinceLastGoodCheckDb, Status and
DataPurityEnabled

————————– EXAMPLE 2 ————————–

PS C:\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\sql987 -SqlCredential (Get-Credential sqladmin) | Format-Table -AutoSize

Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated, DaysSinceLastGoodCheckDb, Status
and DataPurityEnabled.
Authenticates with SQL Server using alternative credentials.

RELATED LINKS
DBCC CHECKDB:
https://msdn.microsoft.com/en-us/library/ms176064.aspx
http://www.sqlcopilot.com/dbcc-checkdb.html
Data Purity:

CHECKDB From Every Angle: How to tell if data purity checks will be run?


https://www.mssqltips.com/sqlservertip/1988/ensure-sql-server-data-purity-checks-are-performed/

So anyone who needs to use the command can see what it is, a full description, what each parameter is for, some examples and some links to more information

So what I used to do was put a snippet of code like this at the top of my function and then fill in the blanks

<#
.SYNOPSIS
Short description
.DESCRIPTION
Long description
.EXAMPLE
An example
.PARAMETER
Parameter Help
.NOTES
General notes
.LINK
Link to more information
#>

The latest release of the PowerShell extension for VS Code has made that process so much simpler 🙂 Thank you David and Keith

Now you can simply type ## (edited May 2018 you can configure this to <# in the user settings) and your help will be dynamically created. You will still have to fill in some of the blanks but it is a lot easier.

Here it is in action in its simplest form

Pester - Simple.gif

But it gets better than that. When you add parameters to your function code they are added to the help as well. Also, all you have to do is to tab between the different entries in the help to move between them

02 - detailed.gif

Now when we run

Get-Help Invoke-AmazingThings -Full

We get this

03 help.PNG

Nice and easy and a great feature added to the VS Code PowerShell extension

Write Good Help.png

PowerShell Function – Validating a Parameter Depending On A Previous Parameter’s Value

Posted on by

I was chatting on the SQL Community Slack with my friend Sander Stad b | t about some functions he is writing for the amazing PowerShell SQL Server Community module dbatools. He was asking my opinion as to how to enable user choice or options for Agent Schedules and I said that he should validate the input of the parameters. He said that was difficult as if the parameter was Weekly the frequency values required would be different from if the parameter was Daily or Monthly. That’s ok, I said, you can still validate the parameter.

You can read more about Parameters either online here or here or by running

Get-Help About_Parameters
Get-Help About_Functions_Parameters

You can also find more help information with

Get-Help About_*Parameters*

01 more help.PNG

This is not a post about using Parameters, google for those but this is what I showed him.

Lets create a simple function that accepts 2 parameters Word and Number

 function Test-validation
{
    Param
    (
         [string]$Word,
         [int]$Number
    )
Return "$Word and $Number"
}

We can run it with any parameters

02 any parameters

If we wanted to restrict the Word parameter to only accept Sun, Moon or Earth we can use the ValidateSetAttribute as follows

 function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [int]$Number
    )
Return "$Word and $Number"
}

Now if we try and set a value for the $Word parameter that isn’t sun moon or earth then we get an error

03 parameter error.PNG

and it tells us that the reason for the error is that TheBeard! does not belong to the set sun, moon, earth.

But what Sander wanted was to validate the value of the second parameter depending on the value of the first one. So lets say we wanted

  • If word is sun, number must be 1 or 2
  • If word is moon, number must be 3 or 4
  • If word is earth, number must be 5 or 6

We can use the ValidateScriptAttribute  to do this. This requires a script block which returns True or False. You can access the current parameter with $_ so we can use a script block like this

{
    if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
    elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
    elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
}

The function now looks like

function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [ValidateScript({
            if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
            elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
            elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
        })]
        [int]$Number
    )
Return "$Word and $Number"
}

It will still fail if we use the wrong “Word” in the same way but now if we enter earth and 7 we get this

04 parameter error.PNG

But if we enter sun and 1 or moon and 3 or earth and 5 all is well

05 working

I would add one more thing. We should always write PowerShell functions that are easy for our users to self-help. Of course, this means write good help for the function. here is a great place to start from June Blender

06 June.PNG

In this example, the error message

Test-validation : Cannot validate argument on parameter ‘number’. The ”
if($word -eq ‘Sun’){$_ -eq 1 -or $_ -eq 2}
elseif($word -eq ‘Moon’){$_ -eq 3 -or $_ -eq 4}
elseif($word -eq ‘earth’){$_ -eq 5 -or $_ -eq 6}
” validation script for the argument with value “7” did not return a result of True. Determine why the validation script failed, and then try the
command again.
At line:1 char:39
+ Test-validation -Word “earth” -number 007
+                                       ~~~
+ CategoryInfo          : InvalidData: (:) [Test-validation], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Test-validation

is not obvious to a none-coder so we could make it easier. As we are passing in a script block we can just add a comment like this. I added a spare line above and below to make it stand out a little more

function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [ValidateScript({
            #
            # Sun Accepts 1 or 2
            # Moon Accepts 3 or 4
            # Earth Accepts 5 or 6
            #
            if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
            elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
            elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
        })]
        [int]$Number
    )
Return "$Word and $Number"
}

Now if you enter the wrong parameter you get this

07 more help.PNG

which I think makes it a little more obvious

 

Use Twitter to get #PowerShell help

Posted on by

A quick post today just to add some weight to something that Mike Fal b | t has kicked off. The #SQLHelp hashtag is well known and well used with in the SQL world. It is a fantastic resource and one that I recommend to all SQL folk I meet who are not aware of it. Heres how it started

Mike has suggested that there should be a similar resource for PowerShell questions #PoSHHelp. We want to create a useful and positive place for people to go with their PowerShell queries and some good folks like Mike,  Shawn Melton(@wsmelton), Adam Bertram(@adbertram), Derik Hammer(@SQLHammer), Boe Prox(@proxb), myself  and others will be looking for your Powershell problems and try to assist you over Twitter with the same care and grace as Sqlhelp.

As with Sqlhelp we would like there to be a few rules that we all can follow to ensure that this remains a brilliant resource. Mike has suggested the following

  1. Questions should fit into 140 characters.
  2. If they don’t, put your question and information on another site (like ServerFault.com) and link to it.
  3. DO NOT SPAM THE HASH TAG. This is important, because in order to make it useful it needs to be kept clean. Don’t use it to advertise your blog posts or articles, but only for Q&A.
  4. Don’t be a dick, a.k.a. Wheaton’s Law. It’s all too easy to let the anonymity of the internet get the better of us. Be polite and respectful to those using and accidentally mis-using the hash tag.

I notice that Aaron Nelson had already suggested this a few years ago but it seems like it has fallen by the wayside. I would like to see this grow for all PowerShell folk so I ask you all to do two things.

Firstly, please add #PoSHHelp to your Tweetdeck column list or pin it to Tweetium (like I have) If you see a question you can help with then jump in and give your answer and help the community.

Secondly, let people know, if you see or hear a question about PowerShell then advise them to make use of the hashtag. If you blog about PowerShell then write a quick blog post like this one and let your readers know.

Pass on the word