A Fine Slice Of SQL Server
Max Trinidad can keep a secret:
Finally, I came up with a practical example using the Powershell Secret Management module for storing SQL credentials. This is an excellent way of keeping your SQL connection strings information out of your scripting code. This way we just have it stored in our Vault.
Read on for a walkthrough and demonstration.
Comments closedKevin Wilkie combines Excel and dbatools like a mad scientist:
Those ways work great if you’re inserting one tab of an Excel spreadsheet. I don’t know about y’all, but I don’t want to have to do all of this work for each tab – especially if I have 30+ tabs to import into SQL Server (and yes, I’ve been asked to do that quite a few times.)
One of the easiest ways I’ve seen to insert a lot of data (once you’ve made sure that the data is how you want it) is to use the power of PowerShell.
Read on to see how.
Comments closedShane O’Neill has fun with Pester:
I’ve been working with Pester v5 lately.
Pester v5 with PowerShell v5 at work & Pester v5 with PowerShell Core outside of work.
There are quite a few changes from Pester version 3, so it’s almost like learning a new language… except it’s based on slang. I think that I’m speaking eloquently, and then I’ve suddenly insulted someone and Pester no longer wants to play nice with me.
Read on to see how to make those Pester outputs look a lot nicer.
Comments closedRon the Polymath has a framework:
PowerShell job steps offer a lot of advantages, but when things don’t work as expected, it can frustrating to understand why. Things like when a non-zero exit code reports the step as successful. Some important points I found with PowerShell steps (especially the first item):
Read on for those interesting points, for a block of Powershell code you can use to track errors, and a SQL Agent job template to boot.
Comments closedKenneth Fisher stands on one foot:
Commenting your code, still super important. That piece of code that looks a bit strange because you couldn’t find another way to make it work? Better put in a note why so the next person doesn’t have to spend hours figuring out what you did and why. That block of code that pulls a list of zip files and unzips them? Explain what you are doing. The next person to look at this (who just may be you) could use a hint as to what you were thinking. Weird variable name? Heck, not so weird variable name. It couldn’t hurt to explain the purpose. Did I ever tell you I got a job because I did such a good job commenting my code during a technical test?
Read the whole thing.
Comments closedJeffrey Hicks proffers some advice:
The other day someone who is learning PowerShell reached out to me with a problem. He couldn’t understand why the relatively simple PowerShell expression to pull information from the System event log wasn’t working. He wasn’t seeing errors, but he also wasn’t seeing the events he was expecting. Searching event logs with PowerShell is a common task. But as you’ll see, you may need to update your approach to mining event logs with PowerShell. Things change in the PowerShell world, and sometimes in subtle ways that you may not notice. Although to be fair, some of these changes my arise from new versions of the .NET Framework and/or Windows 10. Here’s what we encountered.
Read on to learn a more efficient technique for this process.
Comments closedJeffrey Hicks has a bit of advice:
Gladys’ big challenge, probably like many of you, is trying to break the habit of launching the PowerShell ISE. It doesn’t matter if you are running the ise alias at a PowerShell prompt or using a Start Menu shortcut. My idea is that if you can’t stop yourself from doing this, then why not have the ISE turn around and launch VS Code? I gave Gladys a few lines of code to put in her PowerShell ISE profile script that launched VS Code and then killed the ISE. I’ve since, refined that code and I thought I’d share it with everyone else.
There are a few assumptions in the process. First, is that when you installed VS Code, you included the option to include the application in your %PATH%. You can test this. At a PowerShell prompt run code. If VS Code launches, you are ready to continue.
Click through to see how.
Comments closedJeffrey Hicks announces a new tool:
Over the last year, I’ve been working on a solution. I’ve been using it and finding it helpful. My friend Gladys Kravitz was also bemoaning the lack of tools for managing stand-alone functions. And while she had her own approach, I thought my solution might offer more. So I polished it up, setup a Github repository, and published a preview release to the PowerShell Gallery. The module is called PSFunctionInfo. You can find the repository on Github. Because it is a pre-release, you might need to install the newest version of the PowerShellGet module so you have the prerelease parameters.
Click through for more detail on how to use it.
Comments closedJoey D’Antoni needs a good IP address:
I had a client ask me to write a PowerShell script (for an Azure Automation runbook) to automate the creation of firewall rules for an Azure SQL Database. As part of this process, I was planning on having to validate the IP addresses (a valid IP address is of the format x.x.x.x where x is an integer between 0 and 255) by having to write some code to split the IP address string into individual octets and figuring out if each value was a valid number. However, in doing research (searching Stack Overflow), I discovered PowerShell has a built-in IP address data type, which made my life way easier.
Read on to see how you can use this to your advantage.
Comments closedKenneth Fisher avoids overloading:
In SQL Server both the set and equality functions are handled by the equals sign (=).
[…]
However, in some other languages that’s not how it works. In PowerShell for example the equals sign is always a set operation.
Click through to learn the difference, as well as what Powershell uses for equality operations. I think my favorite language for this is Scala, where I jokingly say = means “is equal to,” == means “is really equal to,” and === means “is equal to and I totally mean it.”