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:
I started after nearly a week into the month had passed, and
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.
Recently the DBA Team Lead and I were reviewing some SQL code, and we came across some SQL that neither of us had frequently encountered before. This led to a brief watercooler moment where we shared some uncommon SQL that we had seen. Perfect blog post material, I think.
/* The 10 employees who have been the longest at the company */
SET NOCOUNT ON;
SELECT TOP (10) WITH TIES
HE.JobTitle,
HE.HireDate,
{d '2006-06-30'} AS start_of_company,
DATEDIFF(DAY, {d '2006-06-30'}, HE.HireDate) AS days_since_company_start
FROM HumanResources.Employee AS HE
ORDER BY days_since_company_start;
INSERT Alias
An unexpected item that we found recently was that INSERT INTO statements care about correct column names. That’s all though, nothing else seems to faze them. This means that you can add the most ridiculous aliases or part names to the column and SQL Server won’t care. As far as I can tell, it will just ignore them.
/* Prefixes get prefixed */
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Hires', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Hires;
END;
CREATE TABLE dbo.Hires (
hire_id int IDENTITY(1, 1) NOT NULL
CONSTRAINT [PK dbo.Hires hire_id] PRIMARY KEY CLUSTERED,
job_title varchar(50) NOT NULL,
hire_date datetime2(7) NOT NULL,
is_on_salary bit NULL
CONSTRAINT [DF dbo.Hires is_on_salary] DEFAULT (0)
);
TRUNCATE TABLE dbo.Hires;
WITH OldestHires AS (
SELECT TOP (10) WITH TIES
HE.JobTitle AS job_title,
HE.HireDate AS hire_date,
ROW_NUMBER() OVER (ORDER BY HE.HireDate) AS rn
FROM HumanResources.Employee AS HE
ORDER BY HE.HireDate
)
INSERT INTO dbo.Hires (
[0].[1].[2].[3].[4].[5].[6].[7].[8].[9].job_title,
a.b.c.d.e.f.g.h.i.j.k.l.m.n.o.p.q.r.s.t.u.v.w.x.y.z.hire_date,
[1/0].[%].[OUT_OF_BOUNDS].[ ].is_on_salary
)
SELECT OH.job_title,
OH.hire_date,
CASE
WHEN OH.rn % 3 = 0 THEN NULL
ELSE 1
END AS is_on_salary
FROM OldestHires AS OH;
SELECT *
FROM dbo.Hires;
GO
Default Option
Let’s contrive an example. Let us say that we have a table called dbo.Hires and we’ve added a column called is_on_salary. Since most of the hires are salaried, we have added a new default constraint setting the value to 0. Unfortunately, it looks like the default constraint hasn’t been applied yet…
/* Our dbo.Hires table */
SET NOCOUNT ON;
SELECT *
FROM dbo.Hires;
= DEFAULT
Recently, my DBA Team Lead pointed me to a piece of code where the syntax was: UPDATE T SET COLUMN = DEFAULT
Now, I had never seen this before, and I wasn’t quite sure that this method would work. I wasn’t wholly surprised, though when a quick test proved that it does.
/* UPDATE DEFAULT */
SET NOCOUNT ON;
UPDATE dbo.Hires
SET is_on_salary = DEFAULT
WHERE is_on_salary IS NULL;
SELECT *
FROM dbo.Hires;
What about with no default?
Okay, that seems to add the default constraint to a column. What about when there is no defined constraint on the column. Will it error out then?
/* Removing our default constraint */
ALTER TABLE dbo.Hires
DROP CONSTRAINT [DF dbo.Hires is_on_salary]
SELECT 'Pre update' AS [status],
*
FROM dbo.Hires;
UPDATE dbo.Hires
SET is_on_salary = DEFAULT
WHERE is_on_salary = 0;
SELECT 'Post update' AS [status],
*
FROM dbo.Hires;
Nope! As mentioned in the docs – if there is no default, and the column can become NULL, then NULL will be inserted.
CURRENT
Finally, we have CURRENT. While the vast majority of scripts manually define the database context for commands, such as ALTER DATABASE AdventureWorks, etc., you can tell SQL Server: Hey! Use the current database context!
/* CURRENT Database Context */
SET NOCOUNT ON;
ALTER DATABASE AdventureWorks2014 SET PAGE_VERIFY NONE;
SELECT 'Pre change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';
ALTER DATABASE CURRENT SET PAGE_VERIFY CHECKSUM;
SELECT 'Post change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';
And so forth
Thre’s probably a lot more but these are the ones that we talked about. If you have any uncommon SQL, let me know!
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.
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!
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.
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….
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.
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!
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.
Like most things in life, this piece of work came about while attempting to complete something else. It’s not a bad thing, I expect it at this stage.
Easy Like Sunday Morning
I find it easy to get the total of a row in SQL. Hell, when it is not particularly important, I’ll even go the easy route and use a calculated column in the table.
Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.
SELECT *,
SUM(p.total_pushups_per_day) OVER () AS total_so_far
FROM dbo.PushupsOctober AS p;
GO
Total total
Easy Like Monday Morning
PowerShell is a different beast. Please don’t get me wrong; I still love the language. I don’t find it easier to get a row total and then a grand total though.
It’s possible! I’m just hoping that there is a better way. Saying all that here is my attempt at a row total and grand total using PowerShell.
If you have a better way (you choose the conditions that satisfy “better”) please let me know.
Grabbing the Data
First, let’s grab the data from the table in our database.
Here’s where I remembered that I had a calculated column, realised that it would be cheating to use it and decided it needed to go. Thankfully, this also enabled me to get rid of those pesky columns that get returned from Invoke-DbaQuery when you forget the parameter -As PSObject!
We now have the row total in our total_per_day property. And, with our use of -outvariable data_3, we have the results saved into a variable called $data_3 .
Grand Total
Once we have a single column that we can sum up to give us our grand total, then PowerShell makes this operation trivial.
I do have to use Format-List here because Format-Table can’t fit all the properties in so our new property total_so_far won’t show up.
Welcome to T-SQL Tuesday! This month’s host is Rob Volk (blog|twitter) and the topic is Data Analogies.
Analogies
Come in! Come in, my boy! Now, your father sent you to me to explain what I used to do. Well, I was a DBA, my boy! What do you mean “what does that do”? I took care of the databases! I made sure that they were looked after, that they had vim and vigour, and that they didn’t go down.
What? No, I don’t mean “down-down”, it’s not a direction! I mean that they didn’t go offline. No, not “off-off”, well not quite… It was my duty to ensure that they were available. Got it? No?
Database Administration
Well, let’s take that whiskey cabinet over there Jas…Jaeysin. Let us say that the whiskey cabinet is our database, and it is our job to see that it is never empty. We do that by lots of ways; by checking on it and making sure that nothing is alarming, like empty bottles. We check that there is space if someone wants to add more whiskey and that anyone who wants something from it, can get it. Like me, hehe.
What? You don’t understand how that’s like being a DBA? Well think about it my boy, I would check on the databases, make sure nothing was alarming, and that the data was always available for whoever wanted it.
Security
What’s that? You want some? Ho ho, my boy, you are one for jests. I tell you what, try opening the cupboard door on the left. Yes, the one where you can see all the sticks and cherries through the glass. Not a problem for you, was it? Put back the cherry, please. And wipe your hands…NOT ON THE GLASS! Nevermind, my boy, nevermind, I shouldn’t have put the soda water in a dangerous place like that…inside a cupboard…away from the ledge. Try and open the right cupboard door now. Yes, the one with the fancy bottles and the shiny lights. Yes, I’m aware it’s locked. Now see, you cannot open that door but I can because I have permission to open that door. That was another part of my job, making sure that people had the right permission to get what they wanted, and that people without permission could not.
What’s that? Who decides on the permissions? Well, back then, it was a business decision. Where those higher up in standing would pick, and I would have to follow their instructions. Now, I’m in charge.
What do you mean that’s not what your father says? Right, well, I’ll be having a few words with him, and we’ll see more about this “under his partner’s thumb” business. No, I can’t open it either. Because I don’t have the key. Yeah well, I may be db_owner, but not sysadmin… Nevermind.
Performance
What else did I do? Well, I made sure those who wanted data from the database knew where they could get the data and could get the data promptly. Well, do you see the whiskey cabinet? Yes, the one on the right. Yes, the one you’re not allowed open. Yes, you’re allowed to look…oi! Quit your cheek, or I’ll add that to the list of things I’ll have to talk to your father about. Now, if someone were to go to that cabinet wanting a nice Scotch, they only have to reach into the middle shelf, and they would have a whole choice of Scotch to choose from. I know that because I know that my middle shelf is only for Scotch. Same with the databases; I helped people to create tables to hold their similar data the same way I choose that shelf to have my Scotch.
And see the way that the bottles get fancier as you scan your eyes from left to right. I ordered that shelf, so the most expensive bottles were all the way over there on the right. And, the least expensive bottles are all over to the left. Same with the databases; I would create indexes so that people could go to what they wanted, whether that be the “expensive bottles” or not. No more looking through the entire table for what they want, they knew exactly where to go and knew when they could stop looking as well.
What? Indexes, my boy, an index. No, it hasn’t nothing to do with your finger! Wait! You may be on to something there. Tell me, have you ever run your index finger down a phone book? A phone book. You’ve never heard of it? Hold on…this thing? Ever seen this? Big yellow book, lots of information? Yes, I know we have Google, nevermind.
Redundancy
Was that it? No lad, that was not it. The world of data is vast and open. So much more than what an analogy using a whiskey cabinet can provide. But I will leave you with one more analogy; Redundancy. It’s always essential to have a redundant copy of your data, a recovery plan if a disaster were ever to strike. Open that desk drawer for me, my boy. Yes, it’s another bottle of whiskey. A redundant backup, if you would. Now I believe I see your father pull into the drive, so it is time for you to leave. I shall go see if my backup can still be restored.
At this stage of my career with Transact-SQL, I would like to think that I have the language mostly mapped. If there is something that seems new or unusual, then it often comes down to the developers marking the code to make replacing it more effortless.
In case you don’t grasp what I’m on about, the below piece of code shows it pretty well.
SELECT N'##datetime##' AS search_and_replace;
<clickbait>I had never seen this before</clickbait>
Can you imagine my shock when I came across a piece of code that not only was not for finding and replacing but even though I did not think it would compile, it did!
If you can imagine my shock, then you’re going to need to increase it more when I tell you that there are a whole family of the same functions! Here is the code that threw me for a loop the first time I saw it.
SELECT {d '1970-01-01'};
Researching
There are more than just {d '<datetime>'} as well!
SELECT {d '2020-01-01'} AS sample_date,
{t '12:31:56'} AS sample_time,
{ts '2020-01-01 12:31:56'} AS sample_datetime;
It’s not just date and time functions but string function as well.
SELECT LEN(N'This string is 33 characters long') AS string_length,
{fn BIT_LENGTH(N'This string is 33 characters long')} AS string_bit_length,
{fn OCTET_LENGTH(N'This string is 33 characters long')} AS string_nvarchar_octet_length,
{fn OCTET_LENGTH('This string is 33 characters long')} AS string_varchar_octet_length,
DATALENGTH(N'This string is 33 characters long') AS string_nvarchar_datalength,
DATALENGTH('This string is 33 characters long') AS string_varchar_datalength;
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()
}
}
}
}
}
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 })
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.
Now, we can see all the users; the user itself, the system users, and the other user I created on the database.
Original Article
The Backstory
Work is in the process of automating tasks. Part of this automation includes verifying the automation that we’ve done.
Where am I going with this?
Well, when we’ve automated the creation of database users we also want to verify that we’ve created the users that we say we’ve created.
My fellow co-workers have, thankfully, seen the dbatools light and we use the command Get-DbaDbUser to get the users in a database and compare the list against the users we were supposed to create.
If there are any users that should have been created but don’t show up, well then we have a problem.
The Principle of Least Privilege
Works fine for me […] but it looks like […] can’t run it with her “public” access to the db server.
I’m not going to sugarcoat things – the person that sent me the request has more access than they rightly need. The “public” access worker did not need any of that access so I wasn’t going to just give her the same level.
Plus, we’re supposed to be a workforce that has embraced the DevOps spirit and DevOps is nothing if it doesn’t include Security in it.
So, if I could find a way to give the user enough permission to run the command and not a lot more, then the happier I would be.
But, I was surprised how difficult it was to find out what permissions were needed to run Get-DbaDbUser. Even more surprised when I failed and realised I’d have to find out myself.
If anyone else can Google/Bing it and get the answer, please let me know 😐
The Test
Let’s create a new user with no permissions in SQL Server.
USE [master];
GO
CREATE LOGIN LimitedPermissions WITH PASSWORD = N'MorePermissionsMoreProblems!';
GO
Now let’s test it out. I have a database in my instance called __DBA. Can we access the users in that database?
It doesn’t work. What’s even more surprising is that it silently doesn’t work. No warnings, no permissions errors, or nothing. And I included the -EnableException switch!
The Investigation
It’s good to know that you can check out the contents of the dbatools (and other) commands from PowerShell. No, I’m not talking about opening the .ps1 files. I’m talking about using the Function:\ psdrive.
See those $server.databases and $db.users? For me, that means that it’s using SMO (Server Management Objects). If there was any hope of me google/binging permissions before this, well it’s gone now.
The Will is going
To cut a rather long story short, eventually I came to the idea of thinking that maybe it only needs to connect to the database. So let’s try that.
USE __DBA;
GO
CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO
And now let’s try our Get-DbaDbUser command again.
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.
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.
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
The files get created.
Job should succeed.
The files don’t get created.
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”.
