ConvertTo-SQLSelect

Words: 651

Time to read: ~ 3 minutes

Update 2021-06-17: It now accepts pipeline input

It’s been a busy month for me so there’s not a lot of outside work research that has been going on.

That being said, there has been quite a gap since I wrote a blog post so I figured that I might as well write something

So what do I have to write about?

SELECT Statements

There are times when I want to mess about with data in SQL Server, data that I have obtained in PowerShell. This will require some way to get the information from PowerShell into SQL Server.

I know of a few ways to do this.

dbatools

There is the dbatools module and the Write-DbaDbTableData function.

Get-Help -Name Write-DbaDbTableData -Full

If I wanted to write the properties of 50 modules from PSGallery into SQL Server, I can use the function handy enough.

Find-Module | Select-Object -First 50 | Write-DbaDbTableData -SqlInstance localhost -Database WAT -Table dbatools_Insert -WhatIf

ImportExcel

There is also the ImportExcel module and the ConvertFrom-ExcelToSQLInsert function.

Get-Help -Name ConvertFrom-ExcelToSQLInsert -Full
Find-Module | Select-Object -First 50 | Export-Excel -Path .\Documents\Excel\temp_20210614.xlsx;
ConvertFrom-ExcelToSQLInsert -TableName ImportExcel_Insert -Path .\Documents\Excel\temp_20210614.xlsx -UseMsSqlSyntax

Being Picky

Both of these were a bit too much for me though. I only wanted a quick and easy way to have the data available in a SELECT statement.

I can use ImportExcel and ConvertFrom-ExcelToSQLInsert but that is dependent on the table already existing, never mind having to save the data in an Excel file first.

Don’t get me wrong – I’m aware that you don’t need Excel installed on the computer where you’re running these commands from. You still need to save the files somewhere though. The function doesn’t take data from variables.

I can use dbatools and Write-DbaDbTableData. This function is not dependent on the table having to already exist. It will create the table for you if you tell it to. Thank you -AutoCreateTable; even though I recommend pre-sizing your columns if you want to go with this method.

However, I don’t want to have to create the table beforehand.

ConvertTo-SQLSelect

So I wrote a primitive function to have the data available in a SELECT statement that I can run in an SSMS or Azure Data Studio window.

You can find the code for it here on Github:
ConvertTo-SQLSelect

I can pass a bunch of objects into it and it will create the SELECT for me using the good ol’ VALUES clause.

Although I’m pretty sure this is basically what ORMs do under the cover before people who knew what they were doing looked at them…

ConvertTo-SQLSelect -Data (Find-Module | Select-Object -First 50)
… there’s more data here….

Caveats

There are a couple of caveats to be aware of…

  • It doesn’t allow pipeline input.

It probably could but that would require a sit-down and think about how to do it. Like I said; this was a quick and dirty put-together function.

It now accepts pipeline input – although I’m sure it isn’t the best way I could have implemented that…

-999..1000 | ForEach-Object -Process { (Get-Date).AddDays($_) } | ConvertTo-SQLSelect
  • There are no data types.

There are strings and they get inserted as strings but that’s okay for me for a quick playthrough. Any data conversions, I can do once I have the data in an SSMS window.

  • It doesn’t like single quotes

Yeah, I have no real excuse for this one. I should really fix that before I use this function again…

It can handle single quotes now

  • There is also no help comments for this.

There should be, even though there is only one parameter. There should also be tests! I am filled with good intentions that are yet to see fruition though…

That being said, I’ve had to use it a few times already that has meant that writing it has already paid off.

So feel free to use, abuse, and/or improve it as you see fit.

I hope you find it useful.

Pester 5 and Group-Object – Best Friends

Figuring out how to group the output of your Pester tests

Words: 830

Time to read: ~ 4 minutes

I’ve been working with Pester v5 lately.

Pester v5 with PowerShell v5 at work & Pester v5 with PowerShell Core outside of work.

There are quite a few changes from Pester version 3, so it’s almost like learning a new language… except it’s based on slang. I think that I’m speaking eloquently, and then I’ve suddenly insulted someone and Pester no longer wants to play nice with me.

Initial Tests

I’ve got the following data that I’m using to test Pester v5.

BeforeDiscovery -ScriptBlock {
    $Groups = @(
        [PSCustomObject] @{
            Server = 1
            Group = 'A'
            Value = '86b7b0f9-996f-4c19-ac9a-602b8fe4d6f2' -as [guid]
        }, 
        [PSCustomObject] @{
            Server = 1
            Group = 'B'
            Value = 'e02913f7-7dae-4d33-98c9-d05db033bd08' -as [guid]
        },
        [PSCustomObject] @{
            Server = 2
            Group = 'A'
            Value = '96ad0394-8e9e-4406-b17e-e7d47f29f927' -as [guid]
        },
        [PSCustomObject] @{
            Server = 2
            Group = 'B'
            Value = 'f8efa8b6-e21b-4b9c-ae11-834e79768fee' -as [guid]
        }
    )
}
Image showing the data in the Before Discovery block
Test data

Usually, I would only use -TestCases to iterate through the data. I know that in Pester v3, I could wrap the It blocks inside a foreach () {}, and it would be okay. Hell, in most of my testings, it was faster. It doesn’t matter; I liked using -TestCases, and the performance difference is negligible to me.

That is still an option with Pester v5. I can run the below code to confirm.

Describe -Name 'Attempt: 01' -Tag '01' -Fixture {
    Context -Name 'Server: <_.Server>' -Fixture {
        It -Name 'should have a guid for its value: <_.Value>' -TestCases $Groups {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
ForEach on the It block

If I look at the data, I can see that I’ve got two different values for Server; 1 and 2. It would be great if I could group the tests by those server values.

For me, Pester has three main blocks; Describe, Context, and It.
I know that Pester v5 has a -ForEach parameter for each of these blocks. I’ve already tried using the -ForEach parameter against the It block, and it didn’t do what I wanted.

Reminder of the ForEach on the It block

I’ll try it against the Context block instead and see if it works.


Describe -Name 'Attempt: 02' -Tag '02' -Fixture {
    Context -Name 'Server: <_.Server>' -Foreach $Groups {
        It -Name 'should have a guid for its value: <_.Value>' -Test {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
ForEach on the Context block

That kind of works but we’ve got the same server in two different groups. Let’s move the groups up to the Describe level.

Describe -Name 'Attempt: 03 - Server: <_.Server>' -Tag '03' -Foreach $Groups {
    Context -Name 'Server: <_.Server>' -Fixture {
        It -Name 'should have a guid for its value: <_.Value>' -Test {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
ForEach on the Describe block

We’ll that’s not what I wanted. Instead of 1 describe block, we have multiple blocks; 1 per group.

Grouped Data

Now, I’m going to start using Group-Object. My data by itself doesn’t seem to work.

$Groups = @(
    [PSCustomObject] @{
        Server = 1
        Group = 'A'
        Value = '86b7b0f9-996f-4c19-ac9a-602b8fe4d6f2' -as [guid]
    }, 
    [PSCustomObject] @{
        Server = 1
        Group = 'B'
        Value = 'e02913f7-7dae-4d33-98c9-d05db033bd08' -as [guid]
    },
    [PSCustomObject] @{
        Server = 2
        Group = 'A'
        Value = '96ad0394-8e9e-4406-b17e-e7d47f29f927' -as [guid]
    },
    [PSCustomObject] @{
        Server = 2
        Group = 'B'
        Value = 'f8efa8b6-e21b-4b9c-ae11-834e79768fee' -as [guid]
    }
)
Results of the Test Data

We can pass that data into Group-Object to group our data by a certain property. In my case, I want to group the data by the Server property.

$Groups | Group-Object -Property Server
Grouped Test Data

Taking a look at the first group, I only have the data for that single property value.

($Groups | Group-Object -Property Server)[0].Group
Inside the first group of Test Data

Now, I’ll try the Pester code again.

Grouped Tests

First, I’ll try putting the groups into the It blocks and see if that works.

Describe -Name 'Attempt: 05' -Tag '05' -Fixture {
    BeforeDiscovery -ScriptBlock {
        $GroupedGroups = $Groups | Group-Object -Property Server
    }

    Context -Name 'Server: <_.Name>' -Fixture {
        It -Name 'should have a guid for its value: <_.Group.Value>' -ForEach $GroupedGroups {
            $_.Group.Value | Should -BeOfType [guid]
        }
    }
}
Grouped on the It block

It doesn’t fully work. The data is grouped but the results seems to be concatenating the values. I’d like it better if they were split out to separate tests per value.

This time, I’ll group the data in the context blocks and then pass the groups into the It blocks. I’ll do this by passing the groups into the -ForEach parameter of the It block using $_.Group.

Describe -Name 'Attempt: 04' -Tag '04' -Fixture {
    BeforeDiscovery -ScriptBlock {
        $GroupedGroups = $Groups | Group-Object -Property Server
    }

    Context -Name 'Server: <_.Name>' -ForEach $GroupedGroups {
        It -Name 'should have a guid for its value: <_.Value>' -TestCases $_.Group {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
Grouped on Context and passed to It block

In the previous code blocks, I used the BeforeDiscovery block in the Describe block. If you don’t want to use that, you can pass the Group-Object cmdlet to the ForEach parameter as a subexpression.

Describe -Name 'Attempt: 06 - Server: <_.Name>' -Tag '06' -ForEach ($Groups | Group-Object -Property Server) {
    Context -Name 'Server: <_.Name>' -Fixture {
        It -Name 'should have a guid for its value: <_.Value>' -TestCases $_.Group {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
Without using BeforeDiscovery on the Describe block

Pass or Fail

I’ve encountered this obstacle of grouping objects in tests a couple of times. I’m hoping that by writing this down, I’ll be able to commit the information to memory.

Hey, if it doesn’t, I can always grab the code and figure it out.

Start-IncreasingPushback

Words: 498

Time to read: ~3 minutes

Intro

If you look back over some of the posts that I wrote in October this year, you may have realised that there was a motif going on.

I used a homebrew pushup tracker as a data source for a couple of blog posts. A group of friends and I were attempting to “push out” (excuse the pun) 3,000 pushups over the month.

Spoilers: We didn’t reach the target. 

Try Again

I’m okay with failure. If you learn from your failures, then I don’t even consider them as failures. This scenario didn’t fall into this case, though. The only reasons that I could think that I didn’t reach the target are:

  1. I started after nearly a week into the month had passed, and
  2. I tried to do too much, too fast, in as little rounds as possible per day.

So, with these lessons under my belt, I decided to try again.

Smarter

I figured that it was simple enough to fix my first mistake, I’d start on the first day of the month this time.

The second mistake was something that I figured would also be simple. Rather than attempting to do as many as I could in as little rounds as possible, I’d do ten sets a day and that was it. If I focus more on the process than the goal, I figured that it would get me over the line eventually.

Challenge 01

If I do a set every half hour, I’d have the ten completed in 5 hours. I mean, hey, we’re in lockdown. I have 5 hours to spare.

But I didn’t.

Work, meetings, calls, focus and flow all sapped the time away from me.

So I tried again.

I’ve started getting up early in the mornings do to research and blog posts (like this one for example), so I’d try and get them done then.

Ten sets every 5 minutes should have me completed in just under an hour; more than enough time to spare.

Challenge 02

Pushups are hard! Even when I’m not trying to rep out as many as I can, they still take a toll on the body. Soon a five-minute break is not enough, and I’m taking longer and longer rests.

Fine, if that’s the way we’re going to do this, then I’m going to go with the flow.

Scripting

Seeing as I needed a little extra rest each round, I decided to create a PowerShell script that would help calculate that rest for me.

https://github.com/shaneis/RandomScripts/blob/master/Start-IncreasingBackoff.ps1

Using the Script

For once, I’ve added comment based help to my script so I can run

Get-Help -Name Start-IncreasingBackup -Examples

and get examples of what the script does!

Now, I can run this script and get a timer that will let me know when to start my activities and that will give me more and more rest each time!

Now to see what else I can use this for!

PIVOT in PowerShell

Words: 1151

Time to read: ~ 6 minutes

Apologies

I’m going to start this post off with an apology.

As Kevin Feasel ( Blog | Twitter ) mentioned about my last post Attempting SUM() OVER () in PowerShell:

It’d be a lot easier, though, with a properly normalized data model which includes date, attempt number, and push-ups in that attempt. Pivot those results at the end if you want this sort of report, but SQL is designed to work best with tables in first normal form or higher.

Kevin Feasel

I can’t very well give out to people for not doing the right thing first time, even if it’s more difficult, if I don’t do the right thing myself!

As Kevin mentioned, once the data was in a proper format, a format designed for SQL, the calculations were trivial.

However, outputting the results in the same way in PowerShell required a way to pivot results in PowerShell. Thanks to some heavy lifting from Joel Sallow ( Blog | Twitter ), I now know how to pivot in PowerShell!

Here’s hoping that this post will help explain it for you also.

Exploring our Data

SQL

First off, let’s check the current state of our table in SQL.

SELECT	POP.pushup_date,
		POP.attempt_number,
		POP.pushup_count,
		SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total_per_date,
		SUM(POP.pushup_count) OVER () AS grand_total
FROM	dbo.PushupsOctoberProper AS POP;
SQL style!

Pivoting

I want to get all possible 8 attempts horizontal like the last post. I find this fairly easy when I have the documentation for PIVOTs open in another tab.

/* Can we pivot these? */
SELECT	PVT_01.pushup_date,
		[1] AS attempt_1,
		[2] AS attempt_2,
		[3] AS attempt_3,
		[4] AS attempt_4,
		[5] AS attempt_5,
		[6] AS attempt_6,
		[7] AS attempt_7,
		[8] AS attempt_8,
		PVT_01.total,
		PVT_01.total_so_far
FROM
(
	SELECT	POP.pushup_date,
			POP.attempt_number,
			POP.pushup_count,
			SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total,
			SUM(POP.pushup_count) OVER () AS total_so_far
	FROM	dbo.PushupsOctoberProper AS POP
) AS SRC
PIVOT
(
	MAX(pushup_count) FOR attempt_number IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS PVT_01
ORDER BY	PVT_01.pushup_date;
Simples!

Simple, right? Once we have the data in the expected format then the above steps are the only steps necessary to calculate and show the data in the way that we want.

However, it becomes a bit more complicated in PowerShell.

PowerShell

Let’s grab the data from our SQL instance and take a look at it.

<# Populate our variable from the database #>
$invQueryParams = @{
    SqlInstance = $sqlInstance
    Database = 'LocalTesting'
    Query = 'SELECT * FROM dbo.PushupsOctoberProper;'
}
$data = Invoke-DbaQuery @invQueryParams

<# Show our data #>
$data | Format-Table -Autosize
So far, so good…

Grouping our Data

We have our data fetched, now we need to group it by the different dates. If only PowerShell had a way to group objects…what? Group-Object? oh!

<# Grouping our data #>
$dataGroups = $data | Group-Object -Property pushup_date
$dataGroups
Data.DataRow? * sigh* one of these days I’ll remember to use -AS PSObject with my Invoke-DbaQuery

Now that we have our data grouped by the different dates, we can loop through each date and pivot the data out horizontally.

Manual Pivot

The first way that came to mind was to manually list out all columns. I know that the maximum attempt_count that I have is 8 so let’s manually create 8 attempt columns.

<# Let's pivot this manually because it's the first way that came to mind #>
$ManualpivotedData = foreach ($dg in $dataGroups) {

    [PSCustomObject]@{
        pushup_date = ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString()
        attempt01 = ($dg.Group | Where-Object attempt_number -eq 1).pushup_count
        attempt02 = ($dg.Group | Where-Object attempt_number -eq 2).pushup_count
        attempt03   = ($dg.Group | Where-Object attempt_number -eq 3).pushup_count
        attempt04   = ($dg.Group | Where-Object attempt_number -eq 4).pushup_count
        attempt05   = ($dg.Group | Where-Object attempt_number -eq 5).pushup_count
        attempt06   = ($dg.Group | Where-Object attempt_number -eq 6).pushup_count
        attempt07   = ($dg.Group | Where-Object attempt_number -eq 7).pushup_count
        attempt08   = ($dg.Group | Where-Object attempt_number -eq 8).pushup_count
        total = ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum
        total_so_far = ($data | Measure-Object -Property pushup_count -Sum).Sum
    }
}

<# Let's make this pretty #>
$ManualpivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
Seems to work

In case you’re wondering what @{ Expression = 'attempt*' ; Width = 10 } does, I use it to narrow the width of the columns named like attempt since they’re integers. Since they don’t need as much space, I can narrow them down and then Format-Table won’t cut-off my later columns!

Dynamic Pivot

I’m not against the manual way. I just find it too bulky and repetitve. It works! Please don’t get me wrong on that accout but as I recently heard someone say: “It works, now clean it up

Our main problem is the attempt columns and our manually typing them out. They seem like a perfect candidate for a ForEach loop. But, when we try to slot that in….

foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
            @{ 
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count } 
            }
        }
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
    )

    $dg | Select-Object $props
}
Well that shouldn’t be empty!

Yeah, that’s a “no” from PowerShell. Why is this?

Why this is

Let’s investigate that $props variable. We’re creating a hashtable where the Key is our name and the Value is the expression we want. So let’s get the values.

$props | Format-List
Expression = $num

Do you see the way that each of the Expression keys have a value with the $num variable?

If you check $num now, you’ll see that it’s set to 8. It looks like we have found our problem, the $props variable isn’t keeping the value of $num when we define it!

Since only one date has a value for attempt 8, we should see some values there.

$pivotedData = foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
            @{ 
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count } 
            }
        }
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
    )

    $dg | Select-Object $props
}

<# Let's check the 14th #>
$pivotedData | Where-Object pushup_date -eq '14/10/2020'
All filled but all with value for the 8th attempt!

Yeah…that’s not correct. I did 30 on the first attempt. Believe me, I remember the pain. Looks like it’s putting the value for attempt 8 into each of the attempts.

Not cool…

Closures

If only there was a way to keep the value of $num when we defined the $props variable. Well, thanks to Joel and his post ScriptBlocks and GetNewClosure(), I now know that there is!

$pivotedData = foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
            @{ 
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count }.GetNewClosure()
            }
        }
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
    )

    $dg | Select-Object $props
}

<# Let's make this pretty #>
$pivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
It’s alive!!!!!

Summary

There’s nothing wrong with making mistakes; as long as you learn from them.

Thanks to Kevin for reminding me how things should be stored, and thanks to Joel for this (vast) knowledge sharing, I’ve been able to learn how to dynamically pivot in PowerShell from my mistakes.

Review your mistakes, you never know what you may learn.

Using PowerShell for Lazy Maths

Words: 317
Time to Read: ~ 2 minutes

Pre-Coffee Question

I was asked today if I could figure out how many ways I could arrange the following equation to get the number 18?

// Replacing the "?"s with either "+", "-", "*", or "/" how many ways can you get 18?

2 ? 2 ? 2 ? 2 ? 2

Now I’m sure with enough time and effort that I probably could. But if you ask me this before I’ve had any coffee, the best you’re going to get is a dirty look.

Again, that’s the best that you’re going to get…

So I got lazy.

I know PowerShell.

I know that I can create the formula in PowerShell.

I know that I can invoke the formula in PowerShell to get the result.

So with an icy glare at the offending message and a sip at my scalding coffee, I create the script.

The Script

I’m not saying that this script is any good. Like I said, I wrote the script before the caffeine had hidden the tiredness from my brain.

It works, and that was the main thing for me at the time.

$operators = '+', '-', '*', '/'

$Permutations = foreach ($op1 in $operators) {
  foreach ($op2 in $operators) {
    foreach ($op3 in $operators) {
      foreach ($op4 in $operators) {
        $MatsIzHard = [scriptblock]::Create("2 $op1 2 $op2 2 $op3 2 $op4 2")

        [PSCustomObject]@{
          Formula = $MatsIzHard.ToString()
          Result = $MatsIzHard.InvokeReturnAsIs()
        }
      }
    }
  }
}
Defining the script and then calling the `$Permutations variable to get the results.
Like I said, seems to work…

Now that I have the results in the $Permutations variable, I can look for any results where the Result property is 18.

$Permutations.Where({ $_.Result -eq 18 })
2 rows returned from the `$Permutations variable showing 18 as the result
Yes, I can answer your question.

Yes, I can get 18 from that expression 2 ways…

On to my Actual Job

Overall that took around 3 minutes to complete. Which was the perfect time required for the caffeine to kick in and for me to be ready to start my day.

Now, that PowerShell was not pretty, dynamic, or efficient.
It was probably only fast because the number of iterations was so low.

But I’m happy since even though I can’t do maths before coffee, I can at least write PowerShell.

T-SQL Tuesday #127 – Non SQL Tips and Tricks

Words: 412

Time to read: ~ 2 minutes.

T-SQL Tuesday time! This month we have Kenneth Fisher ( blog | twitter ) as the host and he’s asking us for our non-SQL related tips and tricks.

Short…

I will confess to only starting this post late. So my tips and tricks will not be well thought out or planned. They will involve PowerShell though, something that I think about daily.

What we know

I consider it to be common knowledge that you can open up PowerShell from the explorer.

By default, my PowerShell opens up to “C:\Users\Shane”.

But by typing “PowerShell” into the location bar of an explorer, you can open a PowerShell session.

The PowerShell session will open to the location the explorer was open.

Et Viola

Reverse it

Did you know that you can drag and drop onto a PowerShell console?

Let’s create an empty text file.

New-Item -Name TestEmptyFile.txt -ItemType File

And we can see that it shows up in the open explorer location.

If we were to drag and drop the file into our PowerShell console window, it will return the full path to that file

Learn from History

If you spend a lot of time in a PowerShell console, it’s not rash to presume that you’re going to be running some of the same commands over and over again.

That’s where PowerShell’s history comes into play.

By using the command Get-History or even its alias h , you can see the commands that you’ve run before:

#Hashtag

Claudio Silva ( blog | twitter ) mentions in his T-SQL Tuesday post about using PSReadline’s HistorySearchBackward and HistorySearchForward.

I’ve fallen into the habit of using #.

Get-History returns an Id that we can use with our #. On our PowerShell console, if we want to run the 2nd command in our history, we only need to type #2 and then press Tab.

If we don’t know the Id but know a word, phrase, or substring of the command we can use #<word | phrase | substring of the command> to look through our history for the command.

So to find the command Get-History that we ran, we can use #Hist and then press Tab.

If it’s still not the right command, we can keep pressing Tab until we find the previous command that we’re looking for.

..but Sweet

I’m pretty sure I haven’t blown your socks off in amazement with these tips and tricks. But they work, they’re semi-useful, and they should be helpful.

I hope you knock some use out of them.

Checking Job Step Output Mid-Job using PowerShell

Words: 627

Time to read: ~ 3 minutes

The XY Problem

Them: We have this job…

Me: Right…

Them: … and one of the steps in the job creates files…

Me: Okay…

Them: … and we need you to check if it creates the files, otherwise we don’t need to do any of the rest of the steps.

Me: Why don’t use just error out if that step fails?

Them: Cause there are other errors in that step but they don’t mean it failed

Me: … say what?

Pretty close representation of the conversation

Investigation

I’m going to ignore the whole “there are other errors” for the moment and actually attempt this task. First, let’s try to see if there is a way to get the last time a job step ran.

I already have a test SQL Agent job called “TestEmail” so let’s use that for our test.

(Get-DbaAgentJob -SqlInstance localhost -Job 'TestEmail').JobSteps

Glancing at the output, it appears that we’re looking for the LastRunDate property. In this screenshot, it shows 1/1/0001 12:00:00 AM which means it’s never run before.

Creating Files

We need a way to create files, and not create files, on demand.

Vaguely in the back of my head (and I apologise for not remembering whom), I remembered someone using the presence of a temp table to fire or not fire a trigger. We’re going to use that premise here.

In a SSMS window, we have this code:

USE __DBA;
GO

/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;

/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);

If we want to create files from the PowerShell script, we need to drop the table.
If we don’t want to create files from the PowerShell script, we need to ensure the table exists.

Next, we create this PowerShell file which I’ve called “CreatePowerShellFiles.ps1“.

$Query = @'
IF EXISTS (SELECT 1/0 FROM [sys].[tables] WHERE [name] = N'DoNotCreateFiles')
BEGIN
    SELECT CreateFiles = 0;
END; ELSE
BEGIN
    SELECT CreateFiles = 1;
END;
'@

[bool]$CreateFiles = (Invoke-DbaQuery -SqlInstance localhost -Database __DBA -Query $Query).CreateFiles

if ($CreateFiles) {
    [PSCustomObject]@{
        Name = 'CreatedFile'
        DateCreated = Get-Date
        Nonce = New-Guid
    } | Export-Csv -NoTypeInformation -Path "C:\Users\shane.oneill\Desktop\TestPowerShellCreatedCode_$(Get-Date -Format FileDateTime).csv"
}

Adding this file as a step in our job, it checks for the existence of our table – if the table exists it does nothing otherwise it creates a sample csv file.

Now for the main course

We’re going to add another step now. This one will check for files created after the previous step has run.

First, we’ll create a PowerShell file (“CheckPowerShellFiles.ps1“).

param (
    [Parameter(Mandatory)]
    $JobName,

    [Parameter(Mandatory)]
    $StepName,

    [Parameter(Mandatory)]
    [ValidateScript({ Test-Path -Path $PSItem })]
    $FileDirectory
)

$Jobs = Get-DbaAgentJob -SqlInstance localhost -Job $JobName

$LastStep = $Jobs.JobSteps |
    Where-Object Name -eq $StepName

$FilesExist = Get-ChildItem -Path $FileDirectory |
    Where-Object LastWriteTime -ge $LastStep.LastRunDate

if (-not $FilesExist) {
    $ErrorMessage = 'Files were not created after {0}' -f $LastStep.LastRunDate
    throw $ErrorMessage
}

And add it to the job, passing in the parameters that we want:

Test Run

We’ve got two states that we want to test

  1. The files get created.
    1. Job should succeed.
  2. The files don’t get created.
    1. Job should fail.

Let’s run the first test:

  • Make sure the table is dropped so we create the files:
USE __DBA;
GO

/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;
  • Now run the job:

Success!

Now to check that the job will fail if no files get created:

  • Make sure the table exists so no files get created:
/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);
  • Now run the job:
Congrats, you have successfully failed

Taking a look at the job history, we can see our error message:

Finally

Well, we’ve proved that this method works!

I can pass on “CheckPowerShellFiles.ps1” to the people who requested the check telling them that they only need to add in the right values for the parameters…

Along with a polite note along the lines of “you should really fix your errors”.

Getting into Python by Referencing PowerShell

Words: 653

Time to read: ~ 3 minutes.

Same Trap, Different Language

I’ve been trying to improve my knowledge of Python for the past while. I’ve fallen into the same trap that I fell into when I started my DBA learnings though.

When I began my DBA learning, I was a Blog Post Warrior.

I read every blog post and DBA StackExchange post I could find. I had RSS feeds and alerts all set up and firing. I still have these RSS feeds and alerts though. There was nothing wrong with them, the problem was with the way I was using them. That was the extent of my learning. It was all theory and no practice!

Me: Oh, that’s how you do a page restore. Good to know.
My Brain: Do you want to try that?
Me: No, I read how to do it so I know it now.

Me and Me

I hope I don’t have to point out how sub-optimal this is. I changed how I learned about DBA topics. I built a lab and starting doing some practical work alongside the theory.

Yes, it may mean a slow down in learning throughput but it also means that I only need to learn a topic once. By embracing the practical side, I’ve moulded the information into my brain. I’ve also learned more if I hit errors that aren’t in the blog posts and have to fix them.

Same with Python

I’ve set up RSS feeds. I’ve signed up to newsletters. I’m checking out questions online. But I was not practising.

That has to change now.

I have a dependency on PowerShell. I like it. I find it easier to think that way. So that is where I have to change. Anytime I find myself reaching for PowerShell and I have time in my deadline, I’ll try to do Python as well.
That’s where this blog post comes into play.

The Reason

We’re adding information to our internal wiki. We use TFS (Team Foundation Server) for this and TFS allows a variation of markdown. If you want to create a table in this markdown, then you follow the syntax

| column_01 | column_02 | column_N |
| --- | :--- | ---: |
| column_01 value | column_02_value | column_N_value |

• :--- means left-align the value
• ---: means right-align the value

The current work involves taking the contents of a file and outputting the content with | (pipes) between them, at the start of the line, and at the end of the line.

This is quite a manual process and one that I had done more than twice.

A perfect sign to start automating!

PowerShell

PowerShell came without any real effort or thought.

$file_path = '.\Documents\Versions.txt'
Get-Content -Path $file_path |
    ForEach-Object -Process {
        $login_date, $login_count = $_ -split '\t'
        " | $login_date | $login_count | "
    }

This seems pretty intuitive to me. Get the content and on each line split out the first part before the tab as the variable login_date and the second part as login_count.
After that, it’s a case of adding those variables into a string wrapped with pipes.

Copy and Paste

Python

Python was a bit more difficult to put together and, in the end, I’m not sure I’ve done more than a literal translation of PowerShell to Python. But, hey, it works!

with open(r'C:\Users\shane.oneill\Documents\Versions.txt', 'r') as f:
	for line in f:
		lg_mth, db_cnt = line.split()
		print(f"| {lg_mth} | {db_cnt} |")
Getting my Pyth-ON!

Now I can easily copy and paste this into TFS.

Hey, maybe even create a script as I’ll more than likely need to update this regularly going forward. The most important aspect I take from this is the fact that I can. I have the ability and the practical experience to try this and other examples like this again in the future.

Sin é

That’s it. It wasn’t so much an attempt at a revolutionary post as it was an attempt to get practical experience with Python. Also a chance to get stuck in to writing blog posts again.

Hopefully there’s more to come. Python, PowerShell, and all.

T-SQL Tuesday #123: Life hacks to make your day easier

Words: 557

Time to read: ~ 3 minutes

T-SQL Tuesday has come back around! Another month without me getting a separate blog post out. I’ll get a non-T-SQL Tuesday blog post out eventually! This month, Jess Pomfret ( twitter | blog ) asks us about our life hacks.

The Humble Tomato

There is not enough time in the day. Until somebody figures out how to squish a few more hours into a day, that’s something that we’re going to have to accept.

In an effort to have some sort of time management, I try the Pomodoro technique.

It can be summarised by the points in this Wikipedia article

• Decide on the task to be done.
• Set the pomodoro timer (traditionally to 25 minutes).
• Work on the task.
• End work when the timer rings and put a checkmark on a piece of paper.
• If you have fewer than four checkmarks, take a short break (3–5 minutes), then go to step 2.
• After four pomodoros, take a longer break (15–30 minutes), reset your checkmark count to zero, then go to step 1

Wikipedia

Two Problems

I have 2 problems with the above bullet points

  1. It’s a very manual process, and
  2. The first one says I have to decide on the task to be done first.

In the immortal words of Homer Simpson:

Can’t someone else do it!

Homer Simpson

PowerShell

So, in an effort to resolve my problems, I created a PowerShell script.

You can find it here on my GitHub.

This script implements the Pomodoro technique for me, with blocks broken down into ~20/25 minutes of work and 5 minute breaks for reading blogs.

Knowing that there is so much topics to learn and stay on top of, every 3 work blocks are scheduled to learning.

After every 5 blocks, I get a longer break to grab a coffee, go for a walk, or interact with the Data/Automation family.

I even get a Toast notification when it’s time for me to change actions!

Here’s how it’s worked out for me today.

FYI – I missed starting this script when I came in thanks to back to back meetings from 08:00!

This solves the problems that I had with the Pomodoro technique for me.

  1. It’s no longer manual, it’s giving me an alert each time for the time blocks
  2. I no longer have to decide on what to do next.

The most I have to decide on is the -Random switch. Which effectively means do I want to try and get through all the items in my learning list or just pick a random one each time.

Seeing as my learning list is currently…

[String[]]$Item = 'JavaScript','Research','PSKoans','Blog','Python','PowerShell','C#','dbachecks','DBAFundamentals','Entity Framework','Containers','R'

…with undoubtedly more coming, I should really make it -Random by default!

In case you’re wondering what the “Mark” column is for, I’m trying to improve the current documentation of the system. So every Work action with an entry in the Mark column means get documenting!

Caveats

This is not a law. 

If I’m working on something and I enter focus mode, I’ll ignore the suggested blocks and keep working. Same can be said for deadlines; I’ll take the 5-minute breaks and then get back to the task.

Also, I can’t think of anyone who’s at their desk all day (I’ve been double-booked for meetings today alone!). But it’s a nice way of coming back, glancing at the PowerShell console, and knowing I’ll be making the most of my time.

T-SQL Tuesday #121: Gifts received for this year

Words: 401

Time to read: ~ 2 minutes.

T-SQL Tuesday, the monthly blogging party created by Adam Machanic, is still going strong. This months T-SQL Tuesday is hosted by Malathi Mahadevan ( blog | twitter ) who asks us…

“[…] what are a few things would you consider as gifts, and why?”

Invitation Post

There are many items that I consider gifts. More than I can reasonably write down in a blog post. So, in the interest of brevity, I’m going to break it down into gifts of SQL past, the gifts of SQL present, and the gifts of SQL future.

Gifts of SQL Past

I have been gifted with being mentored by DBAs that were knowledgeable and passionate about the Data Community.

I have been gifted with mentors who encouraged me to go to User Groups, volunteer at conferences and soak up as much data learnings as I could.

I have been gifted with mistakes that I have made in the past that have allowed me to learn from them. (I have definitely been gifted with the knowledge of how important test labs are!)

Gifts of SQL Present

I am gifted with a job that seems to actively repel me away from any sort of comfort zone. 

I am gifted with an active Data Community on Twitter, Slack, and in person for any issues that I run into.

I am gifted with a PowerShell community on Discord and Slack who seem to be constantly battling for the right to call themselves the “most welcoming community”.

I am gifted with a shrinking of borders so that anyone anywhere can be considered a helpful resource to a data question.

Gifts of SQL Future

I’m being gifted with new technologies to help out with old and new problems.

I’m being gifted with a field that is ever-changing. Fluid, dynamic, and will never become stagnant nor boring.

I’m being gifted with putting names to welcome faces and meeting up with new Data Community friends.

Mostly though, I’m being gifted with the opportunity to help others out in the future. Whether that be with blog posts, tweets, talks, examples, or just encouragement.

As good as all the previous gifts are, I believe the best gift is the gifts that you can give back. So, here’s hoping that the next year sees us all help more than hinder, learn more than laze, and teach more than troll.

Those are what I consider gifts.