T-SQL Tuesday #155 – Write to Read, Not to Run

Words: 861

Time to read: ~ 5 minutes

Welcome to T-SQL Tuesday, the monthly blogging party where we talk about a topic given to us by the host. This month, we have Steve Jones (blog | twitter) asking us about Dynamic SQL.

There are a myriad number of uses for Dynamic SQL – I’ve already read some of the published posts and I’m impressed with the range. (Yeah, I’m writing this late; I’m aware).

I’m aiming for something different. I want to talk about the things I believe Dynamic SQL should have. You can disagree with me if you’d like; I’d welcome it (as long as you can justify it) but here are my thoughts on writing Dynamic SQL.

DECLARE

To make a contrived example, I’ve taken Andy Mallons (blog | twitter) script to return SQL Agent Job statuses, and converted it to Dynamic SQL

DECLARE
@job_sql AS nvarchar(max),
@job_name AS nvarchar(128) = N'syspolicy_purge_history', = N'No existy',
@debug_mode_on AS bit = 1; 0;
DECLARE
@nl AS nchar(2),
@actual_job_name AS nvarchar(128);
/* Newline for formatting */
SET @nl = NCHAR(13) + NCHAR(10);
/* Job existence check */
IF @job_name IS NOT NULL
BEGIN
SET @actual_job_name = (
SELECT
[name]
FROM msdb.dbo.sysjobs
WHERE
[name] = @job_name
);
IF @actual_job_name IS NULL
BEGIN
DECLARE @err_msg AS nvarchar(max);
SET @err_msg = FORMATMESSAGE(N'Cannot find any job labelled: %s', @job_name);
RAISERROR(N'%s', 0, 1, @err_msg) WITH NOWAIT;
RETURN
END;
END;
SET @job_sql = N'USE msdb;
SELECT TOP (1)
is_running = CASE
WHEN ja.job_id is NOT NULL AND ja.stop_execution_date IS NULL
THEN 1
ELSE 0
END,
last_run_time = ja.start_execution_date,
next_run_time = ja.next_scheduled_run_date,
last_job_step = js.step_name,
job_outcome = CASE
WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL
THEN N''Running''
WHEN jh.run_status = 0
THEN N''Failed''
WHEN jh.run_status = 1
THEN N''Succeeded''
WHEN jh.run_status = 2
THEN N''Retry''
WHEN jh.run_status = 3
THEN N''Cancelled''
END
FROM dbo.sysjobs AS j
LEFT JOIN dbo.sysjobactivity AS ja
ON ja.job_id = j.job_id
AND ja.run_requested_date IS NOT NULL
AND ja.start_execution_date IS NOT NULL
LEFT JOIN dbo.sysjobsteps AS js
ON jh.job_id = js.job_id
AND js.step_id = ja.last_executed_step_id
LEFT JOIN dbo.sysjobhistory AS jh
ON jh.job_id AND j.job_id
AND jh.instance_id = ja.job_history_id;'
/* Add filter: job_name */
IF @actual_job_name IS NOT NULL SET @job_sql = CONCAT(
@job_sql, @nl,
N'WHERE
j.[name] = @ds_job_name'
);
/* Add sorting */
SET @job_sql = CONCAT(
@job_sql, @nl,
N'ORDER BY
ja.start_execution_date DESC;
'
);
IF @debug_mode_on = 1
BEGIN
RAISERROR(N'%s%s', 0, 1, @job_sql, @nl) WITH NOWAIT;
END;
ELSE
BEGIN
EXECUTE [master].[sys].sp_executesql
@stmt = @job_sql,
@param1 = N'@ds_job_name AS nvarchar(128)',
@ds_job_name = @actual_job_name;
END;
view raw JobStatus.sql hosted with ❤ by GitHub

I was going to apologise for how long and messy it looked but I realised that’s par for the course with Dynamic SQL.

SET

There is this maxim that I’ve heard bandied about regarding code:

Code is read much more often than it is written[…]

https://devblogs.microsoft.com/oldnewthing/20070406-00/?p=27343

There should be an addendum on that quote for DBAs:

DBAs troubleshoot Dymanic SQL more often than they write it

Probably others, but definitely me

Saying that, Dynamic SQL should have these items to help with that effort.

Proper Formatting

It’s extremely easy to write Dynamic SQL so that it comes out in one string. A hodge-podge of plus signs, variable assignments, and red text that sometimes it seems like a foreign coding language

>++++++++[<+++++++++>-]<.>++++[<+++++++>-]<+.+++++++..+++.>>++++++[<+++++++>-]<+ +.------------.>++++++[<+++++++++>-]<+.<.+++.------.--------.>>>++++[<++++++++>–
]<+.

The above code sample is apparently a working “Hello World” program in one of those languages.

Don’t do this, properly format your Dynamic SQL. It will help when the code shows up in your monitoring toolkits. You have them (and know how to use them), right?

To help check your formatting, Dynamic SQL should include…

A Debug Method

Troubleshooting is so much easier when you know what you are going to run. The amount of Dynamic SQL where you have to build that in your head while reading the code is ridiculous!

If you give a procedure filled with Dynamic SQL to a junior DBA, then you’re going to see how fast fear and resignation fills someones face.

It’s straightforward to create a debug method that shoots out the code that is going to be run. Secondary benefit is it ensures that you format your code properly because you can see how it is going to turn out

IF @debug_mode_on = 1
BEGIN
	RAISERROR(N'%s%s', 0, 1, @job_sql, @nl) WITH NOWAIT;
END;
ELSE
BEGIN
	EXECUTE [master].[sys].sp_executesql
		@stmt = @job_sql,
		@param1 = N'@ds_job_name AS nvarchar(128)',
		@ds_job_name = @actual_job_name;
END;

A.O.B

There are a few other things that I like to add to Dynamic SQL but I will grant are not necessary. I’ll leave them to you to make up your own minds about.

Sanitise inputs

If the user passes in an object, ensure it’s there

/* Job existence check */
IF @job_name IS NOT NULL
BEGIN
	SET @actual_job_name = (
		SELECT
			[name]
		FROM msdb.dbo.sysjobs
		WHERE
			[name] = @job_name
	);

	IF @actual_job_name IS NULL
	BEGIN
		DECLARE @err_msg AS nvarchar(max);

		SET @err_msg = FORMATMESSAGE(N'Cannot find any job labelled: %s', @job_name);
		RAISERROR(N'%s', 0, 1, @err_msg) WITH NOWAIT;
		RETURN
	END;
END;

Escaping input

Same point really – if you want to raise a warning or error with what is passed in, use something that escapes user input.

I tend to use FORMATMESSAGE for these bits.

EXECUTE

These might appear to be overkill but I have an cough contrived cough example; the code I took from Andy’s github and converted to Dynamic SQL!

Quick Test with Debug

If we pass in no job name and leave debug mode on; it splits out the code sans any WHERE clause before the ORDER BY to bring us back all jobs:

If we change up the job name to one that does not exist e.g. @job_name = N'No existy'; then we verify that the job doesn’t exist and error out:

Passing in a job that does exist, then adds that job name to the WHERE clause (parameterised, of course):

Let’s Run It!

Turn off debug mode and…

That’s throwing up an error message – but I’d be confident in saying that anyone glancing over the code in the gist would miss the three mistakes.

Yes, three of them – I wrote one on purpose but created two more by accident.

Luckily we can turn debug more back on, grab the output, and parse it in a new window:

The AND on the JOIN clause was intentional – the terminating semi-colon before the WHERE clause was not, neither was the mistake on the alias. The joys of re-writing code.

Fix these mistakes up in our Dynamic SQL, turn debug mode back off, and ready to re-run? Probably took around 5 minutes going slow, but now when we re-run the code:

Result Set

That’s one example of why I have believe Dynamic SQL should have proper formatting, and debugging, and some small others.

I had more examples but they normally come to me around 02:00 in the morning while I’m on swearing and sweating on an incident call.

I’d prefer to fix and forget them, after I make sure they don’t happen again.

Publishing PSTimeSheets To The PSGallery

Words: 567

Time to read: ~ 3 minutes

Skip This for Your Sanity

I have created a module
cause nobody wants to do timesheets no more;
they want PowerShell to do it for ya.
Well, if this is what you need,
then this is what I’ll give ya.
(Ahem, apologies about that, songs get stuck in my head sometimes).

I Confess

I’ve worked with PowerShell for years but have never published a module before. I’ve helped write changes to a few, e.g. dbatools, dbachecks, and a few internal ones.

But the actual creating and publishing one needs adding to my experience list.

There was a lot of gnashing of the teeth, wailing of the cries, and reading of the documentation.

There were a few things that I wanted to do before I published.

Creating tests against all the functions; done.
Creating documentation for all the functions; done.

These were the easy sections; publishing the module was where I encountered the speedbumps.

So here’s a quick list of the aspects that threw me for a loop.

.PSD1 vs .PSM1 Files

I’m aware that the auto-loading of PowerShell modules boils down to a combination of the PSModulePath environment variable ($ENV:PSModulePath) and the .psm1 file for the module. But is there a default way to auto-generate that file?

I thought it was using the New-Module Manifest command, but nope, that creates the .psd1 file. At least I don’t have to worry about that.

The best practice is not to auto-load everything into the .psm1 file. It’s supposed to be more performant to re-create the functions’ definitions there. That’s not what I did.

Publishing

First of all, yes. Anyone can publish to the PSGallery – you need an account.

Did I know that you needed an account? Hell no.
Did I find out? Hell yeah.

To be fair, they say as much when you try to publish the module, asking you for a NuGetApiKey. Pop open your profile in PSGallery, and you can generate it from there.

Missing Values in the .PSD1 File

Remember a few paragraphs ago when I said I didn’t have to worry about the .psd1 file? Yeah, I was wrong. The command New-ModuleManifest is excellent. But, a few key features get missed from the default options.

The Description field doesn’t have an entry, yet it’s a required key to publish a module. Simple enough to open a text editor and update the values there; simple, if annoying.

This next bit is on me: after you have filled out the description field and tried to publish the module, you will get the same error message. That’s because the description field, starting off empty, will also be a comment. Re-open the editor, remove the hash/pound/octothorp that makes the field a comment, save, and you should be good to go.

NodeJS, I Think?

There were other tangles with the Publish-Module command that pushes to the PSGallery. I’ve chalked them down to a sinister combination.

The Linux knowledge needed for troubleshooting vs the amount of Linux knowledge I had.

I switched out of my WSL and tried to publish from my Windows Desktop. It went as smooth as… a very smooth thing.

Return 0

Overall, it was a simple process made more difficult due to lack of experience. Easy enough for anyone to pick up, annoying but unmanageable. Would I do it again?

Well, I’ve got improvements to make to PSTimeSheets, so… yeah!

T-SQL Tuesday #153 – The Conference That Changed Everything

Words: 585

Time to read: ~ 3 minutes

Welcome to T-SQL Tuesday, the monthly blogging party created by Adam Machanic, maintained by Steve Jones (twitter | blog), and hosted this month by Kevin Kline (twitter | blog).


This month, the topic is:


Tell us the story of how attending an IT conference or event resulted in an amazing career or life opportunity.


I have gone to my fair share of conferences, especially since I still think I’ve only been in the community for a relatively short time.
Less than a decade so far.
This duration is a guess, though, because I don’t want to take a more extended look if that number is too low and I start having scary thoughts.

The idea of picking out a single IT conference is also a scary thought.
The number of standout events is more than any professional should be lucky enough to have.
In any event that I have gone to, the talks have been more than knowledgeable; they have been inspiring.
To leave a conference without a renewed energy and motivation to dive into the plugged tech is rare, and it only happened to me once when I went after flying & driving for more hours than I had slept in the previous days.

So it’s not the tech side of events that I want to extoll; it’s the “softer” side, the people & the memories.
I will be cheeky and say that my answer for this T-SQL Tuesday will be all of them, but for different reasons.


SQL Pass for meeting up with the dbatools team and meeting with Jess Pomfret (twitter | blog), Chrissy LeMaire (twitter | blog), Constantine Kokkinos (twitter | blog), Andy Levy (twitter | blog), and John G Hohengarten 👞 (twitter).


SQL Bits, as an attendee, for meeting up with Mötz Jensen (twitter), Dan Alexander (twitter) and Rob Sewell (twitter | blog).
SQL Bits, as a volunteer, for joking and laughing with Neil Holmes (twitter) outside the event to keep warm and realising that Erik Darling (twitter | blog) despairs my taste in whiskey.


Data Grillen, for meeting a great man in Reitse Eskens (twitter | blog), being dazzled by Ben Weissman (twitter | blog) & William Durkin (twitter), and finding out that Hamish Watson (twitter | blog) isn’t as cool as I thought; he’s cooler.


SQL Saturday Boston, for realising that Andy Mallon (twitter | blog) is a fantastic person to be able to organise, invite, welcome, and help host an all-day event with aplomb.


SQL Saturday Cork, for having non-tech chats on a cricket pitch with Kevin Kline (see starting paragraph) and Sander Stad (twitter | blog) on a rare sunny Irish day, talking about random topics we realised all interested us.


Data Scotland, for meeting up with people I had come to know online but not had a chance to meet face to face; Brett Miller (twitter | blog), Craig Porteous (twitter | blog), Paul Broadwith (twitter | blog), Johan Ludvig Brattås (twitter), Cathrine Wilhelmsen (twitter | blog), and John Armando McCormack (twitter | blog).


And for any PowerShell conference, for enlightening me to the genius of Mathias Jessen (twitter | blog), Adil Leghari (twitter), Chris Gardner (twitter | blog), and Adam Russell (twitter | blog).

I will have to stop before the memories stretch out, and I get caught up in them all evening. There are too many people to name in a short blog post, and the amount of memories makes me re-think the amount of time I’ve been in IT, leading to Scary Thoughts again.

The Conference that changed everything? All of them, and hopefully each one that will have me next.

Querying SQL Server with Golang

Words: 333

Time to read: ~ 2 minutes

I’ve been looking into Golang recently. Hey, everyone has to go with what interests them!

It’s all very well and good to open up Golang and write a FizzBuzz (note to self: write a FizzBuzz), but I still work with databases.

So before I do anything with Golang, I’d like to know: can it interact with databases. 

Granted, everything can, but how easy is it? And does trying to interact with databases kill any interest I have in the language.

So, let’s give it a go.

First, some caveats.

  1. You have to install Go (shocking, I’m aware).
  2. This post is effectively me reading the tutorials on the Golang website and converting it to work with SQL Server.
  3. You have to do some funky stuff to get the driver for SQL Server; granted, funky things basically mean run go get <Github link>. I’ve done funkier…
  4. There’s also some better way to do this, but who cares. This is exploratory.
  5. When setting up my SQL instances, I did some weird thing where I now have to specify a non-standard port when connecting. So I have to open up the error log in SQL Server, see what port it’s listening to, and put that into the code. Yes, I’m aware that I’m a DBA, and I should be able to fix this, but it’s my personal time & my personal test instance. I’ll fix it when I want to.

Anyway, here’s the code. 

Do with it what you will; I’m just happy it works, and I’m delighted that I’m excited about it!

package main
import (
"database/sql"
"fmt"
"log"
// this is the driver & yes, even though we're throwing
// it away to `_`, it's still needed
_ "github.com/denisenkom/go-mssqldb"
)
var (
db *sql.DB
server = "localhost"
// Yes, I hate this too…
port = 60515
)
func main() {
// Build connection string
connString := fmt.Sprintf(
"server=%s;port=%d;Trusted_Connection=True;AppName='Golang'",
server,
port,
)
// I want the line that the error occurs in my logs, please & thank you
log.SetFlags(log.Lshortfile)
// Create connection pool
var err error
db, err = sql.Open("sqlserver", connString)
if err != nil {
log.Fatalln("Creating connection pool failed:", err.Error())
}
stringDate, err := getDate()
if err != nil {
log.Fatalln("getDate() failed:", err.Error())
}
fmt.Println("Read:", stringDate)
fmt.Println("Read rows successfully!")
fmt.Println("Finished.")
}
func getDate() (string, error) {
var dte string
// We're "scanning" the values from the query into the `dte`
// variable using `&<var name>`. Different, but interesting…
if err := db.QueryRow("SELECT dt = SYSDATETIME();").Scan(&dte); err != nil {
return "false", fmt.Errorf("getDate() function failed! %s", err.Error())
}
return dte, nil
}
view raw db.go hosted with ❤ by GitHub

First of all, what happens when it works?

go run main.go

And what happens when it fails, say cause the default port actively refuses connections?!

// changing `port = 60515` -> `port = 60514`

Happy out! Now to see what else we can do. Thankfully, I’ve somehow managed to get some ideas marinating in creative juices. Let’s see if I have the time and the energy to see them through!

T-SQL Tuesday #152

Words: 375

Time to read: ~ 3 minutes

I don’t know if this post is a rage against “database-ubiquitous” SQL or Object-Relational Mapping tools (ORMs)

I’m going to say that it’s a rant against the first because if you know what you are doing, then ORMs won’t be a performance concern

But people use ORMs instead of Stored Procedures because
Them: “what happens if we need to change databases? We don’t have to worry about that now cause they’ll be baked in the application, and we won’t have to recreate the Stored Procedures in the new database cause ORMs just work!”

Everything just works until it doesn’t

I’ve worked with several companies and heard this argument many times from developers

Part of why I’m ranting about database-ubiquitousness SQL instead of ORMs is that I’ve also witnessed SQL code that has to work everywhere

I’m going to call this SQL “DUh SQL” from now on since I only have so many vowels on my keyboard and shouldn’t waste them re-writing the word “ubiquitousness”

Them: “Nope, we can’t use DATEFROMPARTS; it’s not available in other databases! Yeah, we use GETDATE() everywhere; what’s your point? WHAT!?!”

I’ve moved across different cloud providers more than I’ve moved databases, and guess what? Each cloud provider move, we’ve kept the same database

I know that knowledge sharing “in the wild” says that you should use ORMs or DUh SQL
The route to a 10x engineer is paved in DRY, KISS, and SWALK
Well, maybe not SWALK

Them: “But the leading minds out there say that you should keep it DUh since that means it’ll be a breeze if we have to move databases”

Sure, and I’ve been told that ice baths help in recovery
I’m not going to take them, though, since I know there’s a difference between me trying to do a 5k and dedicated athletes!

I love arguing with people in my blog posts; I rarely lose

So, don’t use DUh SQL cause of reasons that may not apply to you
Don’t refuse performance tuning efforts if they’ll add database-specific code
And, please, learn to use your tools

Oh, and apologies for the lack of full-stops, I can’t use them in case I have to turn this post into regex

T-SQL Tuesday #150 – Your First Technical Job

Words: 597

Time to read: ~ 3 minutes

Welcome to T-SQL Tuesday 150. This month, Kenneth Fisher (Blog | Twitter) asked us about our first technical job.

I’ve talked before about my first technical job for what feels like too many posts. So I will go back even further to my first job that required domain-specific knowledge.

Today, I will about being a DBA and being a beach lifeguard. Well, I’m going to attempt to anyway.

Are there any similarities between the two?

Pre-conceived notions

First up, we have the notion that comes with the jobs. 

Baywatch deeply colours the picture that springs to mind when thinking of lifeguards.

Athletic supermodels and super hunks, slow-motion springing across the beach, rescue buoy in hand, ready to save lives!

Have you ever tried to run across beaches in Ireland? 

90% of them are sharp stones! I’ve heard that the same percentage of statistics are made up, but I think you can get the picture. 

It’s not so much sprinting across the beach as speed limping while being pelted with rain.

Ideas of DBA-ness, while not as universal as lifeguarding, also had the same gallant imagery for me. 

Dreams of the heroic DBA, toiling at the keyboard, magically reviving downed servers, recovering data from corrupted DBs against all odds.
A sage wizard in the corner who can safely navigate the waters of performance traps.

Romantic ideas, eh? 

I’ve since re-defined that. Not so much a wizard as it is a cinema usher.

Rushing around, trying to have things in the proper place before the next deadline.
Cleaning up after people have thoroughly enjoyed themselves. 
Attempting to keep a low profile while keeping things moving along. 
Trying not to gorge on food while you work…no? Just me?

Oddly though, I enjoy(ed) both. 

Lifeguarding, even with its rocks and rain and day-long stretches of staring at an empty sea, and DBA work, even with its constant shift of intense workloads and tight deadlines.


What about the differences though?

Pathways

There is a defined method to becoming a lifeguard, and certifications are required.

There are practical tests, and there are renewals. There are exams on nearly everything! 
Actually not everything; they don’t test if you can run on stones.

These certs are required – “ no cert-y, no lifeguard-y “, a former teacher used to say. 

He was friendly, but I don’t think his poetry career ever really took off.

Once you were certified, though, that was it. All done until your next renewal or CPR techniques got updated.

DBAs, not so much. 

It’s all on you.

This job is the only one I know of where we have a phrase for someone who “fell” into the role. Either due to proximity to the server or from mistakenly looking up when a manager mentions the word “database”.

There are shops where you can get by just ensuring that backups get taken – maybe restoring the odd backup to get data from a deleted table.

There are also places where the constant thrum of projects and daily work and new technologies will have you sprinting to keep up.

Sure, there are certifications out there, but they change as much as the underlying technology changes. 

There is no ” definition of done ” despite people creating videos, lectures, and courses about it. You learn and learn and continue learning until people stop producing new technologies.

Take a guess what happens then? Here’s a hint, it rhymes with “a mother mew fecknowledgy”…

Sorry, he was an influential teacher….


So now for the million dollar question: which job do I like best?

It depends.

TSQL2sday #149: Advice you’d give your younger self

Words: 459

Time to read: ~ 3 mins

Welcome to TSQL Tuesday 149! The monthly blogging party where we are given a topic to write a post around.

This month Camila Henrique (blog) asked us about the advice we would give our younger selves.

It’s not that I’m smart; it’s just that I stay with problems longer…mainly cause I’ve caused them.

Albert Einstein, probably

Nobody likes a show-off

Really? There’s no need for a stored procedure because you can write out the syntax to update a business order by memory? Wow, that’s great. </sarcasm>

Are you expecting everyone new to learn the code as well?
How useful is that going to be when you leave?

Run this procedure and pass in the business order id” versus “So, what you gotta do here is join these tables based on these ids but only when the square root of -1 isn’t i.
Which do you think is the better option, Einstein?

Also, do you realise how much time you are wasting by manually typing out all the T-SQL code each and every time?!

Simple advice here; do not turn down a good idea because you think you don’t need it. You’re not half as smart as you think you are, and you’re twice as dumb as you feel.

Pride and Prejudice

If you want to go fast, go alone. If you want to go far, go together. Whatever you do, go away!

My sister, I guess

This is another instance of not knowing a good thing when it’s staring you in the face! If programming languages could stare, that is.

You’re soon going to be shown PowerShell as a way to automate some work. Yes, it’s version 2, so it will be rough around the edges. Hell, you’re rough around the edges! You’d like people to take a chance on you, so take a chance with it.

It, like you, will improve. However, it’ll improve at a rate and level that you can only hope to achieve.

Thanks to PowerShell, you will meet people that you never would otherwise. You will learn aspects that will improve every facet of your life. Prejudice ruins a lot of things, don’t let it ruin you!

There are two mistakes one can make along the road to truth… not going all the way, and not starting. No documentation is a close third though

Buddha, I’m assuming

To tie these up together and link back to a recent post by Ken Fisher (blog | Twitter). There is no such thing as a one-off request.

There is nearly always a request that starts with, “Hey, you know that query you ran for me the last day?”.

Learn to document, learn to automate, and learn to use Source Control, ya git.

Only One Join-Path Is Needed

Time to read: ~ 3 minutes

Words: 571

Update: Learning from my mistakes aka Failing Up

Update: Reliably informed that `-AdditionalChildPath` was added after 5.1

Join Me For a Moment

There’s a multitude of scripts out in the wild with a chain of Join-Path commands. Initially, when I wanted to create a path safely, a Join-Path cmdlets chain was also my go-to. However, after reading up on the documentation, I realised another way: I only need a singular instance of the Join-Path command.

Target Location

My PowerShell console is open in my home folder, and I’ve a test file: /home/soneill/PowerShell/pester-5-groupings/00-run-tests.ps1.

If I wanted to create a variable that goes to the location of that file, one of the safe ways of doing that is to use Join-Path.

Long Form

I mean, I could create the variable myself by concatenating strings, but then I’d have to take the path separator into account depending if I’m on Windows or not.

Apparently not…

$var = ".\PowerShell\pester-5-groupings\00-run-tests.ps1"

[PSCustomObject] @{
  Type      = 'Long Form'
  Separator = 'Manual entry: \'
  Variable  = $var
  Path      = try {Get-ChildItem -Path $var -ErrorAction Stop} catch {'Error!'}
}
Forward becomes back

I thought this wouldn’t work but, when running the code samples, it appears that PowerShell doesn’t mind me using a forward-slash (/) or a back-slash (\); it’ll take care of the proper separator for me.

UPDATE: This way works fine from a file but run the script from a PowerShell terminal and it’s a no-go.

No, you’re not the one for me

UPDATED UPDATE: Thanks for Cory Knox (twitter) and Steven Judd (twitter) for pointing out that this fails because it’s using /bin/ls instead of the Get-ChildItem alias:

Manual Creation

A more explicit, cross-platform method would be to use the [IO.Path]::DirectorySeparatorChar.

$sep = [IO.Path]::DirectorySeparatorChar
$var = ".${sep}PowerShell${sep}pester-5-groupings${sep}00-run-tests.ps1"

[PSCustomObject] @{
  Type      = 'Manual Creation'
  Separator = "[IO.Path]::DirectorySepartorChar: $sep"
  Variable  = $var
  Path      = try {Get-ChildItem -Path $var -ErrorAction Stop} catch {'Error!'}
}
The long way around

This method works fine but creating the path can get very long if I don’t use a variable. Even using a variable, I have to wrap the name in curly braces because of the string expansion method I used. That’s not something that I would expect someone picking up PowerShell for the first time to know.

-f Strings

In case you’re wondering, another string expansion method here would be to use -f strings.

$sep = [IO.Path]::DirectorySeparatorChar
$varf = '.{0}PowerShell{0}pester-5-groupings{0}00-run-tests.ps1' -f $sep

[PSCustomObject] @{
  Type = 'F String'
  Separator = "[IO.Path]::DirectorySepartorChar: $sep"
  Variable  = $varf
  Path      = try {Get-ChildItem -Path $varf -ErrorAction Stop} catch {'Error!'}
}
It’s hard to google for the F word

Many Join-Path Commands

Better yet would be if I didn’t have to account for the separator at all. Here’s where the multiple Join-Path cmdlets come into play.

$var2 = Join-Path -Path . -ChildPath PowerShell | Join-Path -ChildPath pester-5-groupings | Join-Path -ChildPath 00-run-tests.ps1
 
[PSCustomObject] @{
  Type      = 'Many join paths'
  Separator = 'Taken care of: Join-Path'
  Variable  = $var2
  Path      = try {Get-ChildItem -Path $var2 -ErrorAction Stop} catch {'Error!'}
}
One, Two, Many, Lots

Multiple Join-Path commands work fine. No real issue with people using this way, but there is another!

Only One Join-Path Needed

Join-Path has a parameter called -AdditionalChildPath that takes the remaining arguments from the command line and uses them in much the same way as a Join-Path command chain would.

$var3 = Join-Path -Path . -ChildPath PowerShell -AdditionalChildPath 'pester-5-groupings', '00-run-tests.ps1'

[PSCustomObject] @{
  Type = 'AdditionalChildPaths'
  Separator = 'Taken care of: Join-Path'
  Variable = $var3
  Path = try {Get-ChildItem -Path $var3 -ErrorAction Stop} catch {'Error!'}
}
One join to rule them all…

More Output than Put Out

So there you go—more than one way to join a path. Use whichever ones work for you. It’s good to know your options, though.

Table Column Differences Part 03 – Compare-SqlTableColumns

Words: 470

Time to read: ~ 2 minutes

Don’t talk to me about it!

Four years ago (I know, where did the time go?), I wrote about Table Column Differences with T-SQL and PowerShell.

A Michal commented on the post, asking how to get a specific output from his search.

Hi,

Thanks for your sharing. What if I also want to compare case sensitively columns and the order of them (syncwindows). How can I presented it on powershell.

I mean that in the final table I want to show also something like: column_a, column_A –> case sensitive

AND

column_a, column_a –> different order in the table

Thanks in advance

Michal

I confess that I never got around to answering Michal until a few weeks ago when I found myself with some rare free time.

Since then, I’ve written a script, slapped it into a function, and threw it up on Github.

Here’s hoping that it does what you want this time Michal, thanks for waiting.

Shall I Compare Thee to Another Table?

The first thing that we need to do is have a couple of SQL tables to compare.

So, I threw up a Docker container and created a couple of tables with nearly the same layout.

(Get-DbaDbTable -SqlInstance localhost -Table 'dbo.DifferenceTable01', 'dbo.DifferenceTable02').Columns |
        Select-Object -Property Parent, Name, ID, DataType |
        Format-Table -GroupBy Parent
I’m liking the new PowerShell formatting

You can see that there are around three differences here

  1. Column orders, e.g. col9 has id 6 in dbo.DifferenceTable01 but id 5 in dbo.DifferenceTable02.
  2. Column case sensitivity, e.g. col7 does not match COL7.
  3. Column presence, e.g. col3 doesn’t exist in dbo.DifferenceTable01 at all.

While Compare-Object has the -CaseSensitive switch, I don’t think that it would be helpful in all these cases. Or else I didn’t want to use that command this time around.

So, I wrote a function to get the output we wanted, and yes, I now include myself among that list of people wishing for that output.

I’m allowed to be biased towards the things that I write 🙂

Compare-SqlTableColumns

Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' |
        Format-Table

I’ve tried to include everything you could want in the function output, i.e. column names, column ids, and statuses.

Something I’ve started to do lately is wrapping a [Diagnostics.StopWatch] in my verbose statement to see where potential slow parts of the function are.

I’d like to think that 0.2 seconds for this example aren’t too bad.

$x = Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' -Verbose

$x | Format-Table

Thou hast less columns than thine brother…

Feel free to use and abuse this function to your hearts content. I know that there are a few things that I’d add to it. Comparing across different instances being an obvious one that I’d like to put in.

Hopefully though, someone out there will find it helpful.

Here’s looking at you, Michal.

T-SQL Tuesday #143 – Short code examples

Time to read: ~ 2 minutes

Words: 328

Welcome to T-SQL Tuesday, the monthly blog post invitational where we’re given a topic and asked to write about it.

This month we have John McCormack (Blog | Twitter) asking, “What are your go-to handy scripts“?

For this post, I’m going to break these down into different languages.

SQL

I once had the annoyingly complex T-SQL to change MS format time into a human-readable format memorised.

SELECT
    time_MS_format = [TimeMSFormat],
    converted_time = '2021-10-12 ' + 
    STUFF(
        STUFF(
            RIGHT('000000' + X.TimeMSFormat, 6), 3, 0, ':'
        ), 6, 0, ':'
    )
FROM (VALUES
     ('00000')
   , ('00500')
   , ('01000')
   , ('10000')
   , ('10500')
   , ('100000')
   , ('100500')
   , ('115500')
   , ('120000')
) X ([TimeMSFormat]);

Then I read a blog post from Kenneth Fisher (Blog | Twitter) about the in-built msdb database function dbo.agent_datetime.

SELECT
    time_MS_format = [TimeMSFormat],
    new_function = msdb.dbo.agent_datetime(20211012, X.TimeMSFormat)
FROM (VALUES
     ('00000')
   , ('00500')
   , ('01000')
   , ('10000')
   , ('10500')
   , ('100000')
   , ('100500')
   , ('115500')
   , ('120000')
) X ([TimeMSFormat]);

If I run sp_helptext on that function, it reminds me of that Andy Mallon (Blog | Twitter) post.

It would be more performant if I stripped that function and used the code directly but the code is too handy to use for the infrequent times I need it.

PowerShell

I’ve talked before about using ConvertTo-SqlSelect in a blog post before and I still use that function alot!

Another short piece of code that I use is more for formatting than anything else. You can populate a variable with an array of properties names. Select-Object can use this variable to return information.

 $Properties = 'SqlInstance', @{Name = 'DatabaseName'; Expression = {$_.Name}}, 'Status', 'RecoveryModel', 'Owner'

Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred | Select $Properties

A useful snipper for reporting is to use a combination of Sort-Object and the Format-* commands with the -GroupBy parameter.

Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred |
    Select $Properties |
    Sort-Object RecoveryModel |
    Format-Table -GroupBy RecoveryModel

Sin é

When I sit down and write this post, I realise that I don’t have a lot of handy scripts. Either I re-write things constantly (that’s likely), or I don’t know enough yet (also likely). I should fix that.