Press "Enter" to skip to content

Month: August 2020

Creating Executables from Powershell Scripts

Published 2020-08-03 by Kevin Feasel

Patrick Gruenauer introduces us to an interesting tool:

Have you ever dreamed of creating exe files out of your PowerShell scripts? If yes, read on. In this post I will show you how you can create exe files with a small and simple program called ps2exe.

Read on to see how it works. Then give it a try and wallow in the fact that some Powershell-wielding production DBA who absolutely is not a developer and doesn’t write code like developers slowly realizes that the developer call was coming from inside the house.

Comments closed

Retrieving Secrets from Azure DevOps Pipelines

Published 2020-08-03 by Kevin Feasel

Gavin Campbell shows how you can pull secrets out of an Azure DevOps Pipeline:

For secrets created in the Azure DevOps UI, whether pipeline-scoped or in a variable group, it is not so simple to retrieve the variables after creation. This might be required for a number of reasons, most often troubleshooting. The need to do this is often an indicator that the project should have been using an Azure Key Vault in the first place.

Previously it was necessary to jump through some hoops to access secret variables, but it turns out this is no longer required. It also appears the recommended approach of mapping secrets to environment variables is currently not working for secret variables from variable groups.

I second the notion of using Key Vault for secrets management.

Comments closed

Wanted: Limiting Calculation Group Members in Power BI

Published 2020-08-03 by Kevin Feasel

Marco Russo shares an annoyance in how calculation groups are currently set up:

The consumption experience following the introduction of calculation groups needs to be improved in Power BI. For example, the immediate advantage of having a time intelligence calculation group is that it is no longer necessary to create multiple variations of the same measure to implement all the possible time intelligence calculations. However, what happens when you have a matrix with Sales Amount, Total Cost, Margin, but you want to show the current value for all the measures and the year-over-year difference only for the Margin measure? Today, if you add a year-over-year calculation item to the matrix, you get the year-over-year of all the measures.

Marco has a Power BI Idea to resolve this which sounds a lot like GROUPING SETS in T-SQL.

Comments closed

Issues when Using the Power BI REST API

Published 2020-08-03 by Kevin Feasel

Nicky van Vroenhoven walks us through a few issues discovered when trying to use the Power BI REST API:

Last month when I was trying to follow the excellent video on Building a Power BI Admin View by Parker Stevens ( b | @PowerBIElite | YouTube).

However, I ran into a few things that I’d like to dedicate this quick post on.

Read on to see where Nicky ran into issues and how you might be able to avoid them.

Comments closed

Recovering SSMS Scripts After a Crash

Published 2020-08-03 by Kevin Feasel

Jonathan Kehayias shows where those recovery scripts for SSMS are located:

It happens to the best of us and this post is more of a reminder for myself the next time it happens to me than anything else.  You are working in SQL Server Management Studio, you have a few tabs open (OK, it was 123 this time, but lets stay focused on the purpose of this blog post – You should see my Desktop and all the icons on top of other icons…), and then suddenly you get the dreaded SSMS has stopped responding/crashed window.  Sure you have been saving the important things along the way, but there are plenty of tabs that were just working queries for analysis that don’t really need to be saved but you still need them.  What do you do?

Even if you do use a tool like SSMS Tools Pack or DevArt’s SQLcomplete (which is what I use), it is still good to know where these scripts are just in case. I’ve also noticed that Azure Data Studio has been quite a bit better about maintaining scripts on close.

Comments closed

Enabling SQL Server Optimizer Hotfixes

Published 2020-08-03 by Kevin Feasel

John Morehouse takes us through the step-by-step for enabling optimizer hotfixes in SQL Server:

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance.  There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.

Let’s take a look at the three options.

Read on to learn more. There is some potential risk of regression with new optimizer updates, so standard rules around testing apply.

Comments closed