Press "Enter" to skip to content

Curated SQL Posts

Creating Executables from Powershell Scripts

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.

Leave a Comment

Retrieving Secrets from Azure DevOps Pipelines

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.

Leave a Comment

Wanted: Limiting Calculation Group Members in Power BI

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 AmountTotal CostMargin, 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.

Leave a Comment

Issues when Using the Power BI REST API

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.

Leave a Comment

Recovering SSMS Scripts After a Crash

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.

Leave a Comment

Enabling SQL Server Optimizer Hotfixes

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.

Leave a Comment

Custom Windows in Apache Flink

Alexander Fedulov walks us through window options with Apache Flink:

In the previous articles of the series, we described how you can achieve flexible stream partitioning based on dynamically-updated configurations (a set of fraud-detection rules) and how you can utilize Flink’s Broadcast mechanism to distribute processing configuration at runtime among the relevant operators. 

Following up directly where we left the discussion of the end-to-end solution last time, in this article we will describe how you can use the “Swiss knife” of Flink – the Process Function to create an implementation that is tailor-made to match your streaming business logic requirements. Our discussion will continue in the context of the Fraud Detection engine. We will also demonstrate how you can implement your own custom replacement for time windows for cases where the out-of-the-box windowing available from the DataStream API does not satisfy your requirements. In particular, we will look at the trade-offs that you can make when designing a solution which requires low-latency reactions to individual events.

This article will describe some high-level concepts that can be applied independently, but it is recommended that you review the material in part one and part two of the series as well as checkout the code base in order to make it easier to follow along.

It’s worth giving this a careful read.

Leave a Comment

Finding Distance Between Cities using SQL Server

Hasan Savran wants to find geographic distances with SQL Server:

I wrote about finding distance between two location in my older post. I have been getting question about how to make the search by using data in SQL Server. In this post, I will try to answer all these questions. You do not need to have latest version of SQL Server to do any of these examples. SQL Server has been supporting Geospatial data since 2008.
     First, we need some data. Not just some data, some free spatial data. I want to show you how to find distance between cities in this post, so I need at least names of the cities and their latitude and longitude. I downloaded this data from SimpleMaps website in CSV Format.

Read on for the solution.

Leave a Comment

Sending Messages from SQL Agent to Microsoft Teams

Rob Sewell is waiting for a message and it comes in two parts. First up, sending SQL Agent results to a Teams channel:

Using dbatools we can create a simple script to gather the results of Agent Jobs form a list of instances. Maybe it would be good to be able to get the job runs results every 12 hours so that at 6am in the morning the early-bird DBA can quickly identify if there are any failures that need immediate action and at 6pm , the team can check that everything was ok before they clock off.

But that’s not enough for Rob:

Following on from yesterdays post about creating an overview of SQL Agent Job Results and sending it to a Teams channel, I was given another challenge

Can you write a job step that I can add to SQL Agent jobs that can send the result of that job to a Teams Channel

The use case was for some migration projects that had steps that were scheduled via SQL Agent Jobs and instead of the DBA having to estimate when they would finish and keep checking so that they could let the next team know that it was time for their part to start, they wanted it to notify a Teams channel. This turned out especially useful as the job finished earlier than expected at 3am and the off-shore team could begin their work immediately.

Read the whole thing, as Rob has some detailed code examples.

Leave a Comment