Month: October 2022

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!