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”.
I admire the array of learning materials that they have for different topics. I find it reassuring seeing the names of Pluralsight authors and recognising them independently as knowledge leaders in their field.
I even have a personal subscription since I was devastated when I moved jobs and was removed from the old jobs plan!
So, even though it’s been out for a while now, when I saw that they now have an extension for VS Code it was enough to make me install it on my machine.
I’m currently going through some C# Fundamentals by Scott Allen ( twitter | blog ) on Pluralsight with an aim to work my way into Entity Framework.
With the Pluralsight extension on VS Code, I can open up some sample code file and Pluralsight will recommend some clips based on the file
Clicking on one of those clips will open a new tab in VS Code like below:
Clicking the “Watch Clip” button will bring up a dialog from VS Code asking if you meant to go to an external website.
Opening or copying & going to the link will open up the Pluralsight video segment from the recommendations.
Insert Dad joke response here
Give it a go and see how it works for you. I use Azure Data Studio for my SQL code but I’m tempted to open up the .sql files in VS Code and see what Pluralsight recommends for them.
What do I think it will say? Who wrote this? Urgh! Kill it, kill it with fire!
At one stage or another, I’m sure that we’ve all been asked to performance tune database code. Sometimes this tuning can involve changing the code. How do we know that the changes that we are introducing isn’t going to introduce bugs? You got it – Unit tests!
Set up
Let’s set up a contrived example – Contrived of course coming from the words “con” as in fake and “Tried V” as in I tried 5 times before I just gave upand used what I had.
USE [tempdb];
GO
/*
Let's build this thang...
*/
CREATE TABLE [dbo].[Objects]
(
[id] int NOT NULL CONSTRAINT [PK dbo.Objects(id)] PRIMARY KEY,
[level] int NOT NULL,
[object_type] int NOT NULL,
[tag] nvarchar(256) NOT NULL,
[description] nvarchar(max) NOT NULL,
[parent_object_id] int NULL
);
GO
INSERT INTO [dbo].[Objects]
(
[id],
[level],
[object_type],
[tag],
[description],
[parent_object_id]
)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [id],
NTILE(10000) OVER (ORDER BY [S01].[alias]) AS [level],
[M01].[severity] AS [object_type],
[S01].[alias] AS [tag],
[M01].[text] AS [description],
NULLIF(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1, 0) AS [parent_object_id]
FROM [sys].[messages] AS [M01]
INNER JOIN [sys].[syslanguages] AS [S01] ON [M01].[language_id] = [S01].[lcid]
WHERE [S01].[alias] = N'English';
GO
SELECT *
FROM [dbo].[Objects];
GO
This will insert 11,466 records into the dbo.Objects table. The table has a column called parent_object_id that points to its … parent object id, wow you have been paying attention! Luckily, or unluckily, tuning the schema of the table is out of scope for this post
Next we have the piece of code that we’ve been asked to performance tune. It’s …uhm … well it works.
/*
RBAR SQL
*/
CREATE FUNCTION [dbo].[GetChildObjects]
(
@ObjectId int
)
RETURNS @ChildObjects TABLE
(
[object_id] int NOT NULL,
[level] int NOT NULL,
[object_type] int NOT NULL,
[tag] nvarchar(256) NOT NULL,
[description] nvarchar(max) NOT NULL
)
AS
BEGIN
--
-- Insert first object.
--
INSERT INTO @ChildObjects
SELECT
id,
level,
object_type,
tag,
description
FROM dbo.Objects
WHERE id = @ObjectId;
--
-- Stack
--
DECLARE @Stack TABLE
(
ObjectId int NOT NULL,
lvl int NOT NULL
);
--
-- Initialise level for stack
--
DECLARE @lvl int = 1,
@CurrentObjectId INT = NULL;
--
-- Insert current object
--
INSERT INTO @Stack
(
ObjectId,
lvl
)
VALUES
(@ObjectId, @lvl);
--
-- Loop through stack and get child objects
--
WHILE (@lvl > 0)
BEGIN
IF EXISTS (SELECT * FROM @Stack WHERE lvl = @lvl)
BEGIN
--
-- Get object
--
SELECT @CurrentObjectId = ObjectId
FROM @Stack
WHERE lvl = @lvl;
--
-- Insert child objects
--
INSERT INTO @ChildObjects
SELECT
id,
level,
object_type,
tag,
description
FROM dbo.Objects
WHERE parent_object_id = @CurrentObjectId;
--
-- Delete object from stack
--
DELETE FROM @Stack
WHERE lvl = @lvl
AND ObjectId = @CurrentObjectId;
--
-- Insert the childnodes of the current node into the stack
--
INSERT INTO @Stack
(
ObjectId,
lvl
)
SELECT
id,
@lvl + 1
FROM dbo.Objects
WHERE parent_object_id = @CurrentObjectId;
IF (@@ROWCOUNT > 0) -- If the previous statement added one or more nodes, go down for its first child.
SET @lvl = @lvl + 1; -- If no nodes are added, check its sibling nodes
END;
ELSE
SET @lvl = @lvl - 1; -- Back to the level immediately above
END;
RETURN;
END;
GO
Timing is important
Let’s get some times for this function. There’s nothing fancy here, we’re just going to call it for different values, returning more and more results and see how long it takes to finish.
/*
Simple tezt...
*/
SELECT * FROM [dbo].[GetChildObjects](11466) OPTION (RECOMPILE); -- 0 seconds.
SELECT * FROM [dbo].[GetChildObjects](11000) OPTION (RECOMPILE); -- 3 seconds.
SELECT * FROM [dbo].[GetChildObjects](10000) OPTION (RECOMPILE); -- 10 seconds.
SELECT * FROM [dbo].[GetChildObjects](9000) OPTION (RECOMPILE); -- 17 seconds.
SELECT * FROM [dbo].[GetChildObjects](8000) OPTION (RECOMPILE); -- 23 seconds.
SELECT * FROM [dbo].[GetChildObjects](7000) OPTION (RECOMPILE); -- 29 seconds.
SELECT * FROM [dbo].[GetChildObjects](6000) OPTION (RECOMPILE); -- 36 seconds.
SELECT * FROM [dbo].[GetChildObjects](5000) OPTION (RECOMPILE); -- 41 seconds.
SELECT * FROM [dbo].[GetChildObjects](4000) OPTION (RECOMPILE); -- 48 seconds.
SELECT * FROM [dbo].[GetChildObjects](3000) OPTION (RECOMPILE); -- 54 seconds.
SELECT * FROM [dbo].[GetChildObjects](2000) OPTION (RECOMPILE); -- 61 seconds.
SELECT * FROM [dbo].[GetChildObjects](1000) OPTION (RECOMPILE); -- 68 seconds.
SELECT * FROM [dbo].[GetChildObjects](10) OPTION (RECOMPILE); -- 76 seconds.
Taking from 0 seconds for 1 row to 76 seconds for 11,456 rows, we now our timings.
As you can see there has been some performance tuning efforts by the code creators already. So impressed was I by the use of OPTION (RECOMPILE) that I asked why they asked me to take a look.
The execution plan seems fine; doesn’t look like there’s anything we can do with it…
Well, let’s take a look at the execution plan, shall we?
Ah!
No surprises
Listen, we both know that this needs to be rewritten. Here is the crux of the post – how do we know that our re-write isn’t going to introduce unexpected side-effects? Little happy “unspecified-features“? You got it – Unit tests!
Creating Unit Tests
To keep this brief, I’ve installed tSQLt on my tempdb database. The documentation for installing it is very easy to follow on the tSQLt website so head there for instructions. We’re going to create some tests for this function.
/*
tSQLt time!
*/
EXECUTE [tSQLt].[NewTestClass] @ClassName = N'GetChildObjects';
GO
CREATE PROCEDURE [GetChildObjects].[test returns nothing for nothing]
AS
BEGIN
-- Arrange (new "fake" table).
EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';
-- Act
SELECT
*
INTO #Actual
FROM [dbo].[GetChildObjects](1);
-- Assert (empty is empty).
EXECUTE [tSQLt].[AssertEmptyTable] @TableName = N'#Actual'
END;
GO
CREATE PROCEDURE [GetChildObjects].[test returns 1 row if no child objects]
AS
BEGIN
-- Arrange (new "fake" table).
EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';
INSERT INTO [dbo].[Objects]
(
[id],
[level],
[object_type],
[tag],
[description],
[parent_object_id]
)
VALUES
(
1,
1,
1,
N'Don''t care',
N'Absolutely don''t care',
NULL
);
-- Act
SELECT
*
INTO #Actual
FROM [dbo].[GetChildObjects](1);
SELECT
1 AS [object_id],
1 AS [level],
1 AS [object_type],
N'Don''t care' AS [tag],
N'Absolutely don''t care' AS [description]
INTO #Expected
EXECUTE [tSQLt].[AssertEqualsTable] @Expected = N'#Expected', @Actual = N'#Actual';
END;
GO
CREATE PROCEDURE [GetChildObjects].[test returns multiple rows...say 4]
AS
BEGIN
-- Arrange (new "fake" table).
EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';
INSERT INTO [dbo].[Objects]
(
[id],
[level],
[object_type],
[tag],
[description],
[parent_object_id]
)
SELECT
[V].[number],
[V].[number],
[V].[number],
N'Don''t care '+ CONVERT(varchar(2), [V].[number]),
N'Absolutely don''t care '+ CONVERT(varchar(2), [V].[number]),
NULLIF([V].[number] -1, 0)
FROM [master].[dbo].[spt_values] AS [V]
WHERE [V].[type] = 'P'
AND [V].[number] > 0
AND [V].[number] <= 10
ORDER BY [V].[number]
-- Act
SELECT
*
INTO #Actual
FROM [dbo].[GetChildObjects](7);
SELECT
[id] AS [object_id],
[level],
[object_type],
[tag],
[description]
INTO #Expected
FROM [dbo].[Objects]
WHERE [id] >= 7
ORDER BY [id];
EXECUTE [tSQLt].[AssertEqualsTable] @Expected = N'#Expected', @Actual = N'#Actual';
END;
GO
Now we have tests to see what happens when the table is empty, when only 1 row should be returned, and when multiple rows are supposed to be returned. Hey! 4 rows still counts as multiple!
Running these tests couldn’t be simpler:
EXECUTE [tSQLt].[Run] @TestName = N'GetChildObjects';
GO
Re-Write time
The best piece of advice I heard for performance tuning, especially with a hectic schedule, is to
Have a “good enough” goal and have a “time limit”
If the code has to run under a minute and you get it to 51 seconds after 30 minutes, great! Stop – I’m sure you’ve a to-do list that is only getting bigger.
/*
All your bases are belonging to SET!!!
*/
DROP FUNCTION [dbo].[GetChildObjects]; --Don't worry, it's in source control.
GO
CREATE FUNCTION [dbo].[GetChildObjects]
(
@ObjectId int
)
RETURNS TABLE
AS
RETURN
(
WITH [Stack] AS
(
SELECT
[O].[id] AS [object_id],
[O].[parent_object_id]
FROM [dbo].[Objects] AS [O]
WHERE [O].[id] = @ObjectId
UNION ALL
SELECT
[Obj].[id],
[Obj].[parent_object_id]
FROM [Stack]
INNER JOIN [dbo].[Objects] AS [Obj] ON [Stack].[object_id] = [Obj].[parent_object_id]
)
SELECT
[Det].[id] AS [object_id],
[Det].[level],
[Det].[object_type],
[Det].[tag],
[Det].[description]
FROM [Stack] AS [S]
INNER JOIN [dbo].[Objects] AS [Det] ON [S].[object_id] = [Det].[id]
);
GO
Wait! How do you… oh yeah. Unit Tests.
EXECUTE [tSQLt].[Run] @TestName = N'GetChildObjects';
GO
Taking from 0 seconds for 1 row to 31 seconds for 11,456 rows, we now our new timings. And while I’m not a fan of the MAXRECURSION 0 I don’t have a major problem with adding it to an existing RECOMPILE statement.
A great side effect is that we have more information in the execution plan!
A wild index suggestion appeared
There we have it!
Thanks to our unit tests we have a more performant code to send back with confidence that it works the same as the old code…just faster.
Now if you’ll excuse me, my 30 minutes are up and my to-do list is growing.
Yes I am aware that these are normally posted directly after the podcast
I had the absolute pleasure of talking with Kevin Hill ( Blog | Twitter | Podcast ) on the first episode of his new podcast “Data Bits”.
You can check out Kevin’s blog post announcing it here and the actual podcast here.
It was an energising experience reliving past times, talking data tales, and cringing over shared scars received from working with data.
Next, Kevin had Andy Levy ( Blog | Twitter ) as his second guest on the show. It was uplifting hearing them talk shop and realising that maybe I should get more “aggressively friendly”.
I’m currently catching up with the next few episodes including Ginger Grant ( Blog | Twitter ), Jules Behrens ( Twitter ), and Louis Davidson ( Blog | Twitter ).
Thankfully, I’ve been able to scrounge some more time to listen to podcasts in this lock-down. There’s always an upside 😁!
I’ve been trying to improve my knowledge of Python for the past while. I’ve fallen into the same trap that I fell into when I started my DBA learnings though.
When I began my DBA learning, I was a Blog Post Warrior.
I read every blog post and DBA StackExchange post I could find. I had RSS feeds and alerts all set up and firing. I still have these RSS feeds and alerts though. There was nothing wrong with them, the problem was with the way I was using them. That was the extent of my learning. It was all theory and no practice!
Me: Oh, that’s how you do a page restore. Good to know. My Brain: Do you want to try that? Me: No, I read how to do it so I know it now.
Me and Me
I hope I don’t have to point out how sub-optimal this is. I changed how I learned about DBA topics. I built a lab and starting doing some practical work alongside the theory.
Yes, it may mean a slow down in learning throughput but it also means that I only need to learn a topic once. By embracing the practical side, I’ve moulded the information into my brain. I’ve also learned more if I hit errors that aren’t in the blog posts and have to fix them.
Same with Python
I’ve set up RSS feeds. I’ve signed up to newsletters. I’m checking out questions online. But I was not practising.
That has to change now.
I have a dependency on PowerShell. I like it. I find it easier to think that way. So that is where I have to change. Anytime I find myself reaching for PowerShell and I have time in my deadline, I’ll try to do Python as well. That’s where this blog post comes into play.
The Reason
We’re adding information to our internal wiki. We use TFS (Team Foundation Server) for this and TFS allows a variation of markdown. If you want to create a table in this markdown, then you follow the syntax
• :--- means left-align the value • ---: means right-align the value
The current work involves taking the contents of a file and outputting the content with | (pipes) between them, at the start of the line, and at the end of the line.
This is quite a manual process and one that I had done more than twice.
A perfect sign to start automating!
PowerShell
PowerShell came without any real effort or thought.
This seems pretty intuitive to me. Get the content and on each line split out the first part before the tab as the variable login_date and the second part as login_count. After that, it’s a case of adding those variables into a string wrapped with pipes.
Copy and Paste
Python
Python was a bit more difficult to put together and, in the end, I’m not sure I’ve done more than a literal translation of PowerShell to Python. But, hey, it works!
with open(r'C:\Users\shane.oneill\Documents\Versions.txt', 'r') as f:
for line in f:
lg_mth, db_cnt = line.split()
print(f"| {lg_mth} | {db_cnt} |")
Getting my Pyth-ON!
Now I can easily copy and paste this into TFS.
Hey, maybe even create a script as I’ll more than likely need to update this regularly going forward. The most important aspect I take from this is the fact that I can. I have the ability and the practical experience to try this and other examples like this again in the future.
Sin é
That’s it. It wasn’t so much an attempt at a revolutionary post as it was an attempt to get practical experience with Python. Also a chance to get stuck in to writing blog posts again.
Hopefully there’s more to come. Python, PowerShell, and all.
T-SQL Tuesday has come back around! Another month without me getting a separate blog post out. I’ll get a non-T-SQL Tuesday blog post out eventually! This month, Jess Pomfret ( twitter | blog ) asks us about our life hacks.
The Humble Tomato
There is not enough time in the day. Until somebody figures out how to squish a few more hours into a day, that’s something that we’re going to have to accept.
In an effort to have some sort of time management, I try the Pomodoro technique.
• Decide on the task to be done. • Set the pomodoro timer (traditionally to 25 minutes). • Work on the task. • End work when the timer rings and put a checkmark on a piece of paper. • If you have fewer than four checkmarks, take a short break (3–5 minutes), then go to step 2. • After four pomodoros, take a longer break (15–30 minutes), reset your checkmark count to zero, then go to step 1
Wikipedia
Two Problems
I have 2 problems with the above bullet points
It’s a very manual process, and
The first one says I have to decide on the task to be done first.
In the immortal words of Homer Simpson:
Can’t someone else do it!
Homer Simpson
PowerShell
So, in an effort to resolve my problems, I created a PowerShell script.
This script implements the Pomodoro technique for me, with blocks broken down into ~20/25 minutes of work and 5 minute breaks for reading blogs.
Knowing that there is so much topics to learn and stay on top of, every 3 work blocks are scheduled to learning.
After every 5 blocks, I get a longer break to grab a coffee, go for a walk, or interact with the Data/Automation family.
I even get a Toast notification when it’s time for me to change actions!
Here’s how it’s worked out for me today.
FYI – I missed starting this script when I came in thanks to back to back meetings from 08:00!
This solves the problems that I had with the Pomodoro technique for me.
It’s no longer manual, it’s giving me an alert each time for the time blocks
I no longer have to decide on what to do next.
The most I have to decide on is the -Random switch. Which effectively means do I want to try and get through all the items in my learning list or just pick a random one each time.
…with undoubtedly more coming, I should really make it -Random by default!
In case you’re wondering what the “Mark” column is for, I’m trying to improve the current documentation of the system. So every Work action with an entry in theMarkcolumn means get documenting!
Caveats
This is not a law.
If I’m working on something and I enter focus mode, I’ll ignore the suggested blocks and keep working. Same can be said for deadlines; I’ll take the 5-minute breaks and then get back to the task.
Also, I can’t think of anyone who’s at their desk all day (I’ve been double-booked for meetings today alone!). But it’s a nice way of coming back, glancing at the PowerShell console, and knowing I’ll be making the most of my time.
If there was ever a phrase that brings about the dreaded Writer’s Block it would be Imposter Syndrome. I’m going to fall back to what I know I can talk about while the back part of my brain ruminates on what to write.
T-SQL Tuesday
T-SQL Tuesday is the brainchild of Adam Machanic ( Twitter | Blog ). It is a monthly blogging call by the Data community. Each month a topic for the community is offered up and a blog post is written by all wishing to take part.
This month we have John Shaulis ( Twitter | Blog ) as the host and John has given the topic of Imposter Syndrome.
I want to read your stories about when you’ve experienced, seen, or overcome imposter syndrome!
I’ve gone through 2 cups of coffee staring at a blank page while waiting for my brain to dredge up some relatable Imposter Syndrome moment. It’s taken this long for me to realise that it’s a futile exercise. Imposter Syndrome is, by the definition that John has kindly given us, a personal experience.
One’s success […] result of one’s […] efforts or skills.
So, with a third cup of coffee in hand, and a deep refusal to believe that I have a caffeine dependency, allow me to tell you about the times that I feel the Imposter Syndrome the most.
Unsurprisingly
Normal working days are not the days that I feel Imposter Syndrome.
Fielding questions at work doesn’t bother me.
There’s a sense of investigation, an aim to teach, and a feeling of joy that people are taking an active interest in SQL Server.
Investigating issues and researching requests don’t trigger it either.
There are set goals here; either fix the issue or can the request be done? Plus, there is an amazing community out there that can be called upon on Twitter, Slack, etc. for answers or guidance.
Unsurprisingly the days when I am the sole person interested in SQL Server are not the days when I feel Imposter Syndrome.
Surrounded but Isolated
Imposter Syndrome hits like a brick when I’m not alone. Conferences, User Groups, and meetups are the main culprits.
Probably stemming from being a Junior DBA going to my first User Group and realising the extent of the topics I didn’t even know that I didn’t know!
Trying to learn the basics of administrating databases and going to a meeting where people are arguing about single-thread CPU performance and NUMA nodes…
Talking to people who are dealing with columnstore partitioning problems and massive memory grants because they’re bulk-loading 3 trillion rows at once.
All the while I’m there thinking my main problem was telling developers “set-based = good, row-by-row = bad”.
The same is true for conferences.
Can’t Stop, Won’t Stop
Please don’t get me wrong. I love going to conferences, I do!
There are few other places where you can get curated knowledge to that extent! Where you can talk to people who get it when you say “Can you believe that they wanted sysadmin privileges just to take backups!?!”.
I love seeing what people are doing, what new technologies are coming up, and catching up with people who experience the same problems that I do.
And there’s no way I’m going to stop, the “fors” don’t just outweigh the “againsts”, they completely smother them!
But it fires off my Imposter Syndrome like nothing else does!
