A Fine Slice Of SQL Server
Mikey Bronowski shows how to shuffle data around:
ImportExcel is a powerful tool to manage MS Excel, however, the user is not limited to data stored in worksheets. We can import from and export to SQL Excel data using a combination of ImportExcel and dbatools modules.
You can read how to start with dbatools in this blog series.
Click through for a demo.
Comments closedMikey Bronowski shows us a few techniques for protecting data in Excel files using Powershell:
Last month we have been hiding things in Excel, so this week we are going to make sure they are protected as well. Excel offers multiple levels of password protection and its options:
– locking file with a password, i.e. without key phrase opening file is not possible
– protecting workbook’s structure
– lastly, protecting individual worksheets from a handful of operations
Read on to see each of those in action.
Comments closedAaron Nelson has one method for creating a polling loop in Powershell:
Originally I had used the Start-Sleep command to wait 3 seconds ( Start-Sleep 3
). That worked fine on my machine, but when I deployed it to the server, I found I needed to bump it up to 6 seconds. At first that worked, but then a week later I needed to bump it up to 9 seconds. The problem here is obvious, if we force it to wait 9 seconds every time, even if the task was updated after 4 second, we’re wasting extra time. And those seconds are going to add up.
Read on for a smarter approach. Ideally we’d be able to use asynchronous event handling with awaits for all of this, but the real world is not always so nice.
Comments closedJess Pomfret takes us through some SPN pains:
But instead of getting a quick answer to my question, I just got the following error:
WARNING: [15:19:49][Get-DbaDatabase] Error occurred while establishing connection to dscsvr1 | The target principal name is incorrect. Cannot generate SSPI context.
Just reading the article brought back some bad troubleshooting memories for me… But as usual, I’m impressed that dbatools has a cmdlet or two to help with that troubleshooting.
Comments closedMikey Bronowski wants to change workbook properties:
The module’s capability at the moment does not allow to directly update the metadata of the workbook, so we are going to dig a little bit deeper. The properties are hiding behind Workbook.Properties class.
Click through for a demonstration.
Comments closedSince I’ve written a lot about PowerShell previously, I wanted to highlight some other tools that I depend on. I’ve always been a fan of Excel, my personal life is full of spreadsheets – most decisions end with a spreadsheet (lucky for me, my wife is also a big fan of Excel!). I often find myself copying data into Excel to keep track of work, or to quickly analyse data. It’s also a great way of sharing data with a clear structure. I’m also a big fan of shortcuts – so here’s a few I use often.
Jess also reminds me that it’s about time to tune up the bicycle…
Comments closedAaron Nelson has some cmdlets for us:
I finally did it. I created a function I’ve been wanting to be able to use for *years*. Test-RsRestItemDataSource is here.
I can’t tell you how many times I’ve started to work on a report I was told was working, only to find the connection info was invalid. This wastes valuable time, especially when you’ve already made changes to the report.
Other times, I’ve been asked to figure out why a bunch of subscriptions weren’t working, only to find out it was a simple connection issue. I’ve always wanted a simple PowerShell command to check the credentials of a bunch of reports before I touch anything.
Turns out, it wasn’t that hard to build at all. I only wish I had built it years ago.
Click through to see an example of this, as well as two more cmdlets.
Comments closedMikey Bronowski continues a series on using Powershell to modify Excel files:
In this part, we will work on an existing workbook that already has worksheets. If you want to learn how to add new worksheets using the Add-Worksheet have a look at this blog post.
In case you haven’t noticed in the script above we used -MoveToStart switch, that means all the new worksheets were added at the beginning.
Read on for examples around moving sheets to the front or end, moving sheets before or after other sheets, and changing the colors of different tabs.
Comments closedAnthony Nocentino takes the dbatools approach to a problem:
Recently I needed to take a look at all of the SQL Server Agent Jobs and their Jobs Steps for a customer. Specifically, I needed to review all of the Jobs and Job Steps for Ola Hallengren’s Maintenance Solution and look at the Backup, Index Maintenance and Integrity Jobs to ensure they’re configured properly and also account for any customizations and one-offs in the Job definitions. This customer has dozens of SQL Server instances and well, I wasn’t about to click through everything in SSMS…and writing this in TSQL would have been a good candidate for a Ph.D. dissertation. So let’s check out how I solved this problem using dbatools.
Click through for the script.
Comments closedGreg Moore gives us an overview of the Powershell IDE landscape:
Before I go too deep into this article, I want to distinguish between editing a file and running it. I’m going to focus on editors here, but most development environments include a way to execute a PowerShell script or PowerShell commands. However, do not confuse the editor with the execution environment.
I used the Powershell ISE for a long while, but eventually stopped because its settings were just different enough from the shell’s settings that things which would work just fine in the ISE would fail when I set them up as automated tasks. I don’t remember what those things were, though, so further research may be required. Nowadays, I’ll use VS Code when I need a proper editor and just wing it on the shell for one-off stuff.
Comments closed