Pester showed me a bug in our existing build process. Can you find it?

Words: 729

Time to read: ~ 4 minutes

Continuous Improvement

Working on the goal of continuous improvement of our processes, I got given access to the PowerShell scripts for our Build Process.

Credit where credit is due, these PowerShell scripts were created by people unfamiliar with the language.

They have done a great job with their research to build scripts that do what they want so I’m not going to nit-pick or critique.

I’m just going to improve and show/teach my colleagues why it’s an improvement.

Original State

The current state of the script is monolithic.

We have a single script that defines functions and then calls them later on. All mixed in with different foreach () and Write-Hosts.

Here’s a rough outline of the script.

$param01 = $args[0]
$param02 = $args[1]
$param03 = $args[2] -replace 'randomstring'

... Generic PowerShell commands ...

function 01 {
    function 01 definition
}

function 02 {
    function 02 definition
}

function GetPreviousTag {
    function GetPreviousTag definition
}

... More generic PowerShell commands ...
... that call our GetPreviousTag function ...
... etc ...

That was it.

1 giant script.
0 tests.

Extracting Functions for Tests

Now scripts are notoriously hard to test, I’ve written about how I’ve done that before but, honestly, if you really want to know then you need to check out Jakub Jares ( blog | twitter ).

Knowing how difficult testing scripts are, the first thing I decided to do was take the functions in the script and split them out. This way they can be abstracted away and tested safely.

I also didn’t want to take on too much at one time so I choose a random function, GetPreviousTag, and only actioned that one first.

Taking a look at GetPreviousTag

The simplest test that I can think of is a pass/fail test.

What do we expect to happen when it passes and what do we expect to happen when it fails.

To figure that out we’ll need to see the GetPreviousTag function. So I copied and pasted the code over to its own file GetPreviousTag.ps1. (sanitised, of course)

function GetPreviousTag {
    # Run the "git describe" command to return the latest tag
    $lastTag = git describe
    # If no tag is present then return false
    if ([string]::IsNullOrEmpty($lastTag)) {
        return $false
    }
    else {
        # If a tag is returned then we need to ensure that its in our expected format:
        # If a commit has taken place but the tag hasn't been bumped then the git describe command will return 
        # refs/tags/1.1.0.a.1-33-gcfsxxxxx, we only want the 1.1.0.a.1 part of the tag so we split off everything after
        # the "-" and trim the "refs/tags/" text.   
        $lastTagTrimmed = $lastTag.Split("-") | Select-Object -First 1
        $lastTagTrimmed = $lastTagTrimmed -replace 'refs/tags/',''
        # Verify that last tag is now in the expected format
        if ([regex]::Match($lastTagTrimmed,'\d+\.\d+\.\d+\.\c\.\d+')) {
            return $lastTagTrimmed
        }
        else {
            return $false
        }
    }
}

It’s nicely commented and glancing through it, we can see what it does.

  • Gets the output of git describe
    • If there’s no output:
      • return $false
    • If there is output:
      • Split on a dash, and get the first split
      • Remove the string ‘refs/tags/’
        • If the remainder matches the regex:
          • Return the remainder
        • If the remainder does not match the regex:
          • return $false

So we have our pass outcome, the remainder, and fail outcome, $false.

More importantly, can you see the bug?

The Pester Test

Here is the Pester test I created for the above function.

It’s relatively simple but even this simple test highlighted the bug that had gone hidden for so long.

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path) -replace '\.Tests\.', '.'
. "$here\$sut"

Describe "GetPreviousTag" {
    Context -Name 'Pass' -Fixture {
        Mock -CommandName git -MockWith {
            'refs/tags/1.1.0.a.1-33-gcfsxxxxx'
        }

        It -Name 'returns expected previous tag' -Test {
            GetPreviousTag | Should -BeExactly '1.1.0.a.1'
        }
    }

    Context -Name 'Fail : empty git describe' -Fixture {
        Mock -CommandName git -MockWith {}

        It -Name 'returns false' -Test {
            GetPreviousTag | Should -BeFalse
        }
    }

    Context -Name 'Fail : regex does not match' -Fixture {
        Mock -CommandName git -MockWith {
            'refs/tags/NothingToSeeHere-33-gcfsxxxxx'
        }

        It -Name 'returns false' -Test {
            GetPreviousTag | Should -BeFalse
        }
    }
}

Thanks to the above Pester test, I was able to find the bug, fix it, and also be in a position to improve the function in the future.

If you can’t find the bug, run the above test and it should show you.

Finally

If there’s one thing to take away from this post, it is to test your scripts

I’ve found Pester so useful that I decided to put my money where my mouth is…literally.

It’s more than deserved. Now back to continuous improvement…

Hiding Warnings in dbatools.

Words: 726

Time to read: ~ 4 minutes

Warning by default

dbatools warns by default.

From what we’ve seen, beginners to PowerShell do not want to be greeted by a sea of red when they use, mis-use, or abuse a dbatools command.

PowerShell errors normally contain useful information on what went wrong. With this information, the “what went wrong” can be fixed.

That being said, if you are writing PowerShell scripts and not raising valid error messages then I highly advise you to go back and fix that.

dbatools raises these error messages as friendly warning messages since we’ve found people will read a warning message quicker than they will read an error message.

Enabling Errors.

dbatools has a common parameter called -EnableException which changes this behaviour. When you use this switch parameter with a dbatools command, it should change the lovely, user-friendly warning to a lovely-but-unfairly-treated error message.

Here is the default process that raises a warning.

# Get the first backup from this path.
$BackupPath = (Get-ChildItem -Path D:\BACKUPS\ -Filter *.bak)[0]

# Restore it to a SQL Server instance that does not exist.
$BackupPath | Restore-DbaDatabase -SqlInstance DoesNotExist -OutputScriptOnly
Works on PowerShell 7.0

Here is the process when we use -EnableException that raises an error.

# Restore to a SQL Server instance that does not exist with errors.
$BackupPath | Restore-DbaDatabase -SqlInstance DoesNotExist -OutputScriptOnly -EnableException
Even shows the line.

Hiding the messages.

Depending on whether you use the default parameters or add in -EnableException parameter, you have ways to hide these messages.

Hiding default warnings

# Create a splat to save me typing over and over again.
$RestoreParam = @{
    SqlInstance      = 'DoesNotExist'
    OutputScriptOnly = $true
}

# Warnings actions
$WarningToDo = @{
    WarningAction   = 'SilentlyContinue'
    WarningVariable = 'RestoreWarning'
}

# Hide and capture the warning.
$BackupPath | Restore-DbaDatabase @RestoreParam @WarningToDo
Warning! Warning!

Here we can see that the warning message was not output but it was captured to our variable called “RestoreWarning”.

Hiding Errors

Errors are slightly different and I’ll show you why, although you can do the same thing, I use a try ... catch ... block instead.

I also don’t try and hide errors but relay them out.

try {
    $BackupPath | Restore-DbaDatabase @RestoreParam -EnableException
} catch {
    "Error happened! Here is the error message"
    $_
}
Try and catch

I was originally worried that the above method meant that you couldn’t reference the error that just occurred like we could with the warning methods and $RestoreWarning but I had forgotten about the $error variable!

# What was the latest error that we had?
$Error[0]
Tada

I highly recommend that you capture that latest error message though $RestoreError = $Error[0] because the next error will be put on that stack and our error will be pushed down!

Hide the error

However, you can hide the error message that is raised by either an empty catch block or an -ErrorVariable.

Empty catch block

Please don’t do this.

I’m happy that PSScriptAnalyzer has a way to check for empty catch blocks so it is incentive to not do this. However it is possible.

# Empty catch = Boo!
try {
    $BackupPath | Restore-DbaDatabase @RestoreParam -EnableException
} catch {

}
Nothing!

Do I like it? No. It is possible? Yes.

ErrorVariable

I mentioned that capturing errors with -ErrorVariable is a bit different and I’m going to show you why.

First let’s show that using -ErrorVariable can work to capture the error stream and stop it showing on the console.

$BackupPath | Restore-DbaDatabase @RestoreParam @ErrorsToDo -EnableException
Captured!

And now here is the difference between -WarningVariable and -ErrorVariable.

# Count the number of objects in these variables.
$RestoreWarning.Count
$RestoreError.Count
order by int or string, it works…

Let’s take a look at $RestoreError so.

2 different types of objects
All the info

If you are wondering why this is I believe it’s because when we use -WarningVariable method we are letting dbatools do the commands, bubble up the main error into a single warning, and then we are capturing that warning message.

When we use -ErrorVariable method, we are saying “give me everything”, there is no bundling happening, and we are seeing all the errors that occur – including internal functions and cmdlets that dbatools use.

All in All

I like the way that dbatools shows errors as warnings by default for everyday and beginning users.

I appreciate the fact that dbatools allows us to turn of that behaviour for our scripts and tool making purposes.

However, if you want nothing, you can have that too.

Finally, like everything, know your tools so you can get the most use out of them!

dbatools 1.0. The tools to break down barriers.

Words: 780

Time to read: ~ 4 minutes

Version 1.0 is coming…

20th June 2019 and dbatools will release version 1.0

Now, I don’t remember how I started with dbatools.
I know that I was a Junior DBA at that stage and I was experimenting with PowerShell.

After being initially introduced to PowerShell when it was only Version 2, and dismissing it for something that wouldn’t catch on in a Windows environment (I never said that I was prophetic!), I was ready to attone for my actions and dive deep into this wonderful scripting language.

They say that the way to improve is to do and I was looking for a project that interweaved PowerShell with SQL Server.
I can only assume that was when dbatools found me.

Now some time has moved on and things have moved on as well.
PowerShell has released verison 7 on pre-release, I’ve taken a position as a Production DBA, and dbatools are about to release version 1.0!

In that time, I’ve learned that dbatools, more than just a PowerShell module, helps break down barriers that exist in organisations.

Barrier to learning…

This may come across as a bit banal, something that has been thrown around a lot, and something that you have all heard been said before but there are so many new things out there for data professionals.

How are we supposed to keep up-to-date with developments, how are we supposed to keep abreast of the latest technologies, how are we supposed to drink from the water hose of learning if we are not taking advantage of the automation that dbatools brings?

I cannot imagine a data professional taking advantage of containers, spinning up a few, and then saying

Hold on, I have to connect SSMS to the container to restore a database on it now. Oh wait, I have to copy and paste the backup file over first. Then I have to make sure permissions are there and correct and…yeah you need to wait.

Not me

Imagine it! Imagine the speed and progress taking place.

  • Find an image: docker search and its done!
  • Download it: docker pull and its done!
  • Create the container: docker create and its done!
  • Deploy the database: “ah hold on guys, this is going to take at least an hour...”

The flow is cut.
Its speed-bumped; road blocked by a data professionals lack of automation.

dbatools can help you, it has the commands. Restore-DbaDatabase, Copy-DbaLogin, and the work is done!

It is not that there is no time to learn the new technologies, it is not using automation to give yourself time to learn.

Use dbatools, push the speed-bump down the pipeline, and leverage that new found time to learn to become more valuable!

Barriers in roles…

DevOps is the union of people, process, and products to enable continuous delivery of value to our end users

Donovan Brown

Now imagine trying to implement DevOps in a organisation where the people are siloed?

There is no collaboration, no communication because people don’t have the knowledge or the tools to provide support to anyone else.

Developers have to wait for the DBA each time they want to restore a database on a dev environment.

Operations can’t troubleshoot an issue on the database because they don’t have the permissions or the know-how to connect to an instance.

Middle management have to go to every single instance to run the same query for information about their SQL Server estates.
RDP, SSMS, New Query, Paste, Run over and over and over again.

dbatools empowers these people.

Tools can be written using dbatools that restores a masked database for developers. This can be logged and as open or as restrictive as you want.

Operations can use Install-DbaWhoIsActive and Invoke-DbaQuery to check out issues on an instance without having the bother the DBA first.

Middle management can be given a script to query all the servers from Get-DbaRegServer and Invoke-DbaQuery to run what they want. What was once a multitude of mouse clicks can be reduced to the running of a single file.

Everything logged, everything known, and everyone empowered.

dbatools breaks down these barriers…

dbatools is about to publish version 1.0 but this does not mean that the work stops.

There are knowledge seekers to help, Issues to troubleshoot, Feature Requests to pour over, Documentation to create and update, Tests to write and verify.

Do not be put off; version 1.0 is something to celebrate, not something to be intimidated by.

Join in!
Learn, laugh, and lend a hand.
Version 1.0 is released but, thankfully, numbers don’t stop at 1.

Now this is not the end. It is not even the beginning of the end. But it is, perhaps, the end of the beginning.

Winston Churchill

Updating the Account Password for my Constrained Endpoint

Words: 448

Time to read: ~ 2 minutes

A while ago, I was asked about Automation and told a story about my friend James.

In that story I talked about Constrained Endpoints and how, by using them, I could do a take the best bits of automation & delegation and not have to worry about unlocking James anymore.

Well, I was wrong

A while after I created that Constrained Endpoint, I was greeted one day by James saying he was receiving a weird error when he tried to unlock his account.

Connecting to remote server ‘server_name’ failed with the following error message : The creation of a new
Shell failed. Verify that the RunAsPassword value is correctly configured and that the Group Policy setting “Disallow
WinRM from storing RunAs credentials” is Disabled or Not Configured. To enable WinRM to store RunAs credentials,
change this Group Policy setting to Disabled. For more information, see the about_Remote_Troubleshooting Help topic.

CategoryInfo : OpenError: (‘server_name’:String) [], PSRemotingTransportException
FullyQualifiedErrorId : CannotConnectRunAsFailed,PSSessionStateBroken

Unlock James has turned into Error James.

The fact that this occurrence came the day after I had reset my password, and the fact that the error message contained the words “[v]erify that the RunAsPassword value is correctly configured” was not something that was lost on me.

Luckily, PowerShell is fabulously easy to explore with it’s Get-Help command so it was a simple case to look for commands around Session Configurations – Get-Command -Name *Session*Configuration* – and look at the help contents of the Set-PSSessionConfiguration cmdlet.

Make sure you include proper help in your functions, it’ll help you immensely when you come back to it after some time.

Get-Help -Name Set-PSSessionConfiguration -Detailed
Also available by using help Set-PSSessionConfiguration -Parameter RunAsCredential

The Fix this Time

The evidence pointed to me needing to update the RunAsCredential of the endpoint and that was the overall fix for this issue.

Set-PSSessionConfiguration -Name UnlockJamesEndpoint -RunAsCredential (Get-Credential)

I threw in my username and my new password, did a quick test to see if the endpoint was available for me ( it was ), asked James to test that it was available for him ( it was ), and I closed off the ticket.

Aesop Out

Constrained Endpoints are not a technology that I am familiar with yet. It’s nice to know that I can take a look at the error messages, use some troubleshooting processes – check out the book “How to Find a Wolf in Siberia” by Don Jones ( blog | twitter ) – and figure it out.

Then again, the technology world is filled with new technologies and if you have a job where you know everything about your technology stack then congratulations to you.

For everyone else, get used to not knowing. Network, Search, Learn. You’ll be obliviously proficient in no time!

Speaking at DATA:Scotland

Words: 228

Time to read: ~ 2 minutes

Pinch me…

Having only spoken once before at the amazing Belfast SQL Server User Group in January this year, I was talked around to throwing in a submission into speaking at DATA:Scotland.

Then on the first of May this year, I got an epic “pinch me” moment.

https://platform.twitter.com/widgets.js

DATA:Scotland

DATA:Scotland is an annual, free training conference for all things DATA set in the lovely city of Glasgow, Scotland.

I stress the DATA in that sentence as it is not just confined to SQL Server but all things data related. Which is a sentiment that I am extremely fond of. We are Data Professionals after all.

Having visited Glasgow before for work but never having a chance to fully take it in, I’m looking forward to enjoying the conference and then playing the tourist in the city.

Need Validation?

I’ll be speaking about dbachecks in Conference Room 6, from 13:45 on the Friday. Come on in and check it out!

In case you can’t make my session, there’s many more other valuable sessions to check out and networking to do. Don’t miss out!

Now to obsessively prepare until then…

T-SQL Tuesday 114 – Puzzle Party

Words: 1,858

Time to read: ~ 5 minutes

It’s T-SQL Tuesday time and this time we have Matthew McGiffen ( blog | twitter ) who is asking us about a Puzzle Party!

Let’s see what exactly he is asking us though.

β€’ Present a puzzle to be solved in SQL and challenge your readers to solve it.

β€’ Or give us a puzzle or quiz about SQL or databases.

β€’ Show the SQL solution to a classic puzzle or game.

β€’ Provide a method for solving a classic sort of querying puzzle people face.

β€’ Show how newer features in SQL can be used to solve old puzzles in new ways.

β€’ Tell us about a time you solved a problem or overcame a technical challenge that was a real puzzle.

β€’ Or just make your own interpretation of β€œpuzzle” and go for it!

Matthew McGiffen

I didn’t have much in the idea department for most of them so I’m thankful that Matthew left us with the last own interpretation option. So here is my attempt at a puzzle.

Invoke-LollerCoaster!

Should I Cheat?

It’s a slightly strange story but I had already done a slight “puzzle” or “trick” in PowerShell where I created a Loller Coaster Don’t ask me why, blame Andy Mallon ( blog | twitter ).

Now, that implementation used PowerShell but I thought to myself…

You know what…it uses System.Data.DataTable…that’s nearly the same thing as querying from a database. Could I get away with posting that?

Me

In the end, I decided against using the PowerShell version and said is there anyway that I could port it over to SQL Server?

PowerShell

First, you can find the PowerShell version of this in my Github account here:
https://github.com/shaneis/RandomScripts/blob/master/Invoke-Lollercoaster.ps1

I’m more proud of that than this attempt…

Look at it go!!!

SQL Server

Now for the SQL Server version.

Let’s create our table in tempdb and populate it. Could this have been made more compact? Perhaps with some fancy string manipulation and some such?

Oh yeah! But this was a quick job done during my lunch break and I wanted it done more than I wanted it perfect.

Plus, if you don’t like the below, you’re really not going to like what I use in the actual script…


USE tempdb;
GO
IF OBJECT_ID(N'dbo.LollerCoaster', N'U') IS NOT NULL BEGIN
	DROP TABLE dbo.LollerCoaster;
END;
GO
CREATE TABLE dbo.LollerCoaster (
	loller_coaster_id tinyint IDENTITY(1, 1) NOT NULL,
	loller_coaster_stage varchar(4000) NOT NULL
);
GO
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
        __)
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
         __)
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          __)
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL\
           O\)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O\
            L\)      LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L\        LOL   LOL
             O\)    O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O\      O   O O   O
              L\)   L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L\    L     L     L
               O\) O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O\_)O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O__)O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L    )L     L     L
               O _/O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O      )O   O O   O
              L    /L     L     L
               O  /O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L        )LOL   LOL
             O      /O   O O   O
              L    /L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          ) 
            L        /LOL   LOL
             O      /O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL            )
           O          / 
            L        /LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          __)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O           __)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O            __)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          
            L         LOL\  LOL
             O       O   O\)   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          
            L         LOL   LOL
             O       O   O\O   O
              L     L     L\)   L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L\    L
               O   O       O\) O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O__)O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L    )L
               O   O       O _/O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O  \O
              L     L     L    )L
               O   O       O  /O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O  \O
              L     L     L    )L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O( \O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O/  O
              L     L     L()   L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L(    L
               O   O       O\) O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O__)O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O __)
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O  __)
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL\
                               O\)
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O\
                                L\)   LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L\    LOL
                                 O\) O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O\_)O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O__)O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L    )LOL
                                 O _/O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL        )
                               O      /
                                L    /LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O      __)
                                L     LOL
                                 O   O
                                  LOL')
GO

Next we’ll need to set up some things and create a few variables

SET NOCOUNT ON;
-- Set Ctrl + T to send results to text!

DECLARE @Counter tinyint;
DECLARE @End tinyint;
DECLARE @Stage varchar(4000); -- Horribly oversized, I know and apologise.
DECLARE @Pause varchar(12);
DECLARE @Clear varchar(4000); -- again, apologies...

Then we can set the variables that we have

SET @Counter = 1;
-- I could move this after the insert and use @@ROWCOUNT
-- but I created/inserted into the table in a different session.
SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster);
-- Just 15 line feeds
SET @Clear = REPLICATE(CHAR(13), 15);

Then… the rest is a WHILE loop. 😦

RAISERROR(@Clear, 0, 1) WITH NOWAIT;

WHILE @Counter <= @End BEGIN

	SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter);
	
	RAISERROR(@Stage, 0, 1) WITH NOWAIT;

	IF @Counter &gt;= 21 AND @Counter <= 29 BEGIN
	    SET @Pause = '00:00:00.100';
	END; ELSE BEGIN
		SET @Pause = '00:00:00.200';
	END;

	WAITFOR DELAY @Pause;
	RAISERROR(@Clear, 0, 1) WITH NOWAIT;
	SET @Counter += 1;
END;

There’s only a few things to mention here.

  • I’m clearing the screen at the start just for cleanliness.
  • There’s a tight loop between counters 21 and 29 where we want to simulate it speeding up.
  • We’re setting a pause between each one since we want the users to actually see the differences.
  • It can be improved so much!
USE [tempdb];
GO
SET NOCOUNT ON;


DECLARE @Counter tinyint;
DECLARE @End tinyint;
DECLARE @Stage varchar(4000);
DECLARE @Pause varchar(12);
DECLARE @Clear varchar(4000);

SET @Counter = 1;
SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster);
SET @Clear = REPLICATE(CHAR(10), 15);

RAISERROR(@Clear, 0, 1) WITH NOWAIT;

WHILE @Counter <= @End BEGIN

	SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter);
	
	RAISERROR(@Stage, 0, 1) WITH NOWAIT;

	IF @Counter &gt;= 21 AND @Counter <= 29 BEGIN
	    SET @Pause = '00:00:00.100';
	END; ELSE BEGIN
		SET @Pause = '00:00:00.200';
	END;

	WAITFOR DELAY @Pause;
	RAISERROR(@Clear, 0, 1) WITH NOWAIT;
	SET @Counter += 1;
END;
It's a sled riding down a rollercoaster made of the word LOL repeating.
No, I don’t know why the sizes change half-way through.