Press "Enter" to skip to content

Curated SQL Posts

Detecting a Fraud: Power BI Edition

Published 2021-07-26 by Kevin Feasel

Brett Powell has an interesting post on determining a Power BI consultant’s chops:

This post discusses the forms, origins, and indications of fraud and misrepresentation in the context of Power BI consulting and contract engagements. The intent of this content is to help the reader determine whether a fraud or misrepresentation has occurred and identify characteristics that suggest the potential for a significant variance between expectations and deliverables.

Brett comes at this from the viewpoint of an expert sharing information on the types of things you should expect to see from a qualified consultant. Incidentally, this also works to give you an idea of the types of skills necessary to become a Power BI consultant so if that’s your goal, study this and you’ll be in a solid mindset—especially because this isn’t just a knowledge quiz.

1 Comment

Working with Complex Return Types in Power Query

Published 2021-07-26 by Kevin Feasel

Gerhard Brueckl works with some complex data:

When working with Power Query, you have probably already realized that every expression you write returns a value of a specific type. Usually this will be a primitive type like text, number, or date. (A full list of types available in Power Query can be found here: https://docs.microsoft.com/en-us/powerquery-m/m-spec-types). If for some reason the type of an expression cannot be defined, the special type *any* will be used. For sure you already encountered this when using Table.AddColumn which, by default, results in the new column being of type *any*.

Read on to learn more, including what you can do if you’re creating Power Query functions.

Comments closed

Setting Resource Constraints on Containers

Published 2021-07-26 by Kevin Feasel

Anthony Nocentino won’t let this container run amok:

Docker gives you the ability to control a container’s access to CPU, Memory, and network and disk IO using resource constraints, sometimes called Limits. You define limits as parameters when creating containers. In its default configuration, a container will have no resource constraints for accessing resources of the host operating system. This post will look at how to configure resource constraints in Docker and look at how SQL Server sees the resources when CPU and Memory resource constraints are in place.

In this post, we will focus on using CPU and Memory Limits using the parameters --cpus <value> and --memory <value>. These provide the essential resource controls for access to CPU and Memory available on the host. If you want to dive deep into the various types of resource constraints available in Docker, check out this page https://docs.docker.com/config/containers/resource_constraints/. The goal of this post is to introduce the SQL Server DBA into resource constraints in containers.

Read on to learn how you can use CPU and memory limits to control resource allocation for SQL Server containers—including at runtime.

Comments closed

Threat Intelligence and Kafka

Published 2021-07-23 by Kevin Feasel

Kai Waehner continues a series on using Apache Kafka as the foundation for a security solution:

Threat intelligence, or cyber threat intelligence, reduces harm by improving decision-making before, during, and after cybersecurity incidents reducing operational mean time to recovery, and reducing adversary dwell time for information technology environments.

Threat intelligence is evidence-based knowledge, including context, mechanisms, indicators, implications, and action-oriented advice about an existing or emerging menace or hazard to assets. This intelligence can be used to inform decisions regarding the subject’s response to that menace or hazard.

Threat intelligence solutions gather raw data about emerging or existing threat actors & threats from various sources. This data is then analyzed and filtered to produce threat intel feeds and management reports that contain information that automated security control solutions can use.

Threat intelligence keeps organizations informed of the risks of advanced persistent threats, zero-day threats and exploits, and how to protect against them.

Read the whole thing.

1 Comment

Generating Excel Reports with Power BI and Power Automate

Published 2021-07-23 by Kevin Feasel

Chris Webb mashes together a slew of technologies:

Now that Excel reports connected to Power BI datasets work in Excel Online it opens up a lot of new possibilities for doing cool things with Office Scripts and Power Automate. Here’s a simple example showing how all these technologies can be put together to automatically generate batches of Excel reports from a template.

Read on for the process.

Comments closed

Unlocking a Login the Bad Way

Published 2021-07-23 by Kevin Feasel

Kenneth Fisher needs bad things done fast:

We recently had an application login (SQL Server authenticated) in one of our training environments start locking out on a regular basis. I won’t go into why other than to say we did resolve it eventually. This was a major problem that escalated rather quickly up our management chain. We needed to solve it ASAP. And because of that we needed not only a long term solution but a short term one as well. The short term solution involved creating a script that unlocked the login (if it’s currently locked) and sticking it in a job that runs every 5 minutes.

Note: This is not something you should be doing in production! This creates a major security hole.

The process itself isn’t, strictly speaking, a bad idea—for example, maybe you’re testing out some integration and accidentally lock the account. The bad idea is more the script to keep doing this every few minutes.

Comments closed

Performance Issues with AT TIME ZONE

Published 2021-07-23 by Kevin Feasel

Jonathan Kehayias lays out a warning:

This is not the type of blog post that I enjoy writing. First let me start off by saying this is not a bash against Microsoft, the SQL Server team, or anything other than an informative post to let people know that there is a potential performance limitation in a very useful feature of SQL Server 2016 and higher. The AT TIME ZONE syntax was added in SQL Server 2016 to handle changing datetime values from one time zone to the offset of a different time zone using string names for the time zone. This is a great feature that simplifies converting datetime values but there is an unfortunate draw back to the implementation; it relies on the time zones that are stored in the Windows Registry and therefore has to make calls out to Windows which unfortunately occurs row-by-row for large result sets when used inside of a query.

Read on for more detail, as well as the way that Jonathan fixed the performance issue.

Comments closed

Throwing Exceptions in T-SQL

Published 2021-07-23 by Kevin Feasel

Chad Callihan plays hot potato:

When it comes to error handling or troubleshooting a long stored procedure, RAISERROR is an easy statement to use that gets the job done. Way back in SQL Server 2012, Microsoft wanted to replace RAISERROR with the new (arguably less convenient) THROW statement. I thought it would be worth looking at an example using THROW and what it takes to have custom messaging with a parameter.

THROW can be a little less convenient, but conceptually speaking, I do think it’s better than the alternative. The part which makes it tricky in practice is that so many types of T-SQL errors are non-catchable, so as a developer, you have to keep on your toes about it.

Comments closed

Remote Powershell Management via the ISE

Published 2021-07-23 by Kevin Feasel

Jeffrey Hicks has a use for the Powershell ISE:

Way back before the days of PowerShell Core, or even VS Code for that matter, the PowerShell ISE was the center of my PowerShell world. I spent a lot of time finding ways to make it easier for me to use and to push it to its limits. Naturally, the PowerShell ISE doesn’t play much of a role for me these days. But that may not be true for you. In fact, one area where the ISE can be useful is with remote sessions. 

Read on to see how.

Comments closed