Press "Enter" to skip to content

Author: Kevin Feasel

Bubbling Up HTTP Status Errors In Power Query

Published 2019-01-18 by Kevin Feasel

Tony McGovern takes us through a method involving Power Query + M of giving end users useful information when a web request fails:

So how does this relate to error tables? Like most well-documented APIs, the U.S. Census Bureau API has a page devoted to listing and describing all the possible response codes that can be returned by their service. I take this information and build an internal table within the query that defines and describes these response codes in my own words. I’m now able to throw custom messages that make the difference between a 400 response code and a 404 response code more obvious.

For example, in the code below, I use the Error.Record function to create individual records that allow me to catch these unsuccessful requests and throw my own custom error messages to the user. I then create an extra field in each record called ‘Status’, which maps each HTTP response code returned by the API to a corresponding error message of my choosing:

There’s a bit of work, but the end result is a fairly simple explanation for end users.

Comments closed

Auditing SQL Agent Jobs

Published 2019-01-18 by Kevin Feasel

Jason Brimhall has some clever techniques for auditing SQL Agent Jobs with Extended Events:

Once upon a time, I was in the position of trying to figure out why a job failed. After a bunch of digging and troubleshooting, it was discovered that the job had changed but nobody knew when or why. Because of that, I was asked to provide a low cost audit solution to try and at least provide answers to the when and who of the change.

Tracking who made a change to an agent job should be a task added to each database professionals checklist / toolbox. Being caught off guard from a change to a system under your purview isn’t necessarily a fun conversation – nor is it pleasant to be the one to find that somebody changed your jobs without notice – two weeks after the fact! Usually, that means that there is little to no information about the change and you find yourself getting frustrated.

Click through to see how Jason does it.

Comments closed

When SLEEP_TASK Waits Are Important

Published 2019-01-18 by Kevin Feasel

Ginger Keys gives an example where the SLEEP_TASK wait indicates a performance problem:

Ordinarily SLEEP_TASK is a nonspecific wait type in SQL Server which occurs when a task sleeps while waiting for a generic event to occur, according to Microsoft documentation.  This wait type can usually be safely ignored, however on some occasions it can happen when a script does not execute completely or hangs up for long periods of time.

The SLEEP_TASK wait means that a thread is waiting on a resource or waiting for some event to occur, and could indicate background task scheduling, a query plan exchange operator that isn’t tracked by CXPACKET, or it could be a hashing operation that spills to tempdb.

Read the whole thing. For a bit more information, check out the SQLskills description of this wait type.

Comments closed

Testing Kafka Streams Applications

Published 2019-01-17 by Kevin Feasel

Yeva Byzek continues her series on testing Kafka-based streaming applications:

When you create a stream processing application with Kafka’s Streams API, you create a Topologyeither using the StreamsBuilder DSL or the low-level Processor API. Normally, the topology runs with the KafkaStreams class, which connects to a Kafka cluster and begins processing when you call start(). For testing though, connecting to a running Kafka cluster and making sure to clean up state between tests adds a lot of complexity and time.

Instead, developers can unit test their Kafka Streams applications with utilities provided by kafka-streams-test-utils. Introduced in KIP-247, this artifact was specifically created to help developers test their code, and it can be added into your continuous integration and continuous delivery (CI/CD) pipeline.

Streaming applications need tested just like any other.

Comments closed

Combining Plots In R With cowplot

Published 2019-01-17 by Kevin Feasel

Abdul Majed Raja shows how to use the cowplot library in R to merge together independent plots into a single image:

The way it works in cowplot is that, we have assign our individual ggplot-plots as an R object (which is by default of type ggplot). These objects are finally used by cowplot to produce a unified single plot.

In the below code, We will build three different histograms using the R’s in-built dataset iris and then assign one by one to an R object. Finally, we will use cowplot function plot_grid() to combine the two plots of our interest.

The only thing that disappointed me with cowplot is that its name has nothing to do with cattle.

Comments closed

Classifying Texts With Naive Bayes

Published 2019-01-17 by Kevin Feasel

I continue my series on Naive Bayes with another hand-calculation post:

Step two is, on the surface, pretty tough: how do we figure out if a set of words is a business phrase or a baseball phrase? We could try to think up a set of features. For example, how long is the phrase? How many unique words does it have? Is there a pile of sunflower seeds near the phrase? But there’s an easier way.

Remember the “naive” part of Naive Bayes: all features are independent. And in this case, we can use as features the individual words. Therefore, the probability of a word being a baseball-related word or a business-related word is what matters, and we cross-multiply those probabilities to determine if the overall phrase is a baseball phrase or a business phrase.

Click through for a sports-heavy example and a bonus Nate Barkerson reference.

Comments closed

Non-Administrative Powershell Remoting And January 2019 LCU

Published 2019-01-17 by Kevin Feasel

Emin Atac tests out a security change made in the January 2019 Latest Cumulative Update for Windows:

My first concern was: if it’s a security vulnerability, what’s its CVE? The blog post answer is: CVE-2019-0543 discovered by James Forshaw of Google Project Zero

My second concern was twofold. Is the chapter about A Least Privilege Model Implementation Using Windows PowerShell published in the PowerShell Conference Book impacted by this change? Should I stop deploying Windows 10 at work because the LCU of January 2019 breaks my loopback scenario?

The answer is no and explained by the blog post Windows Security change affecting PowerShell
you would not be affected by this change, unless you explicitly set up loopback endpoints on your machine to allow non-Administrator account access

Read on for some testing and digging into what works when and why.

Comments closed

Name Conflicts In DAX

Published 2019-01-17 by Kevin Feasel

Marco Russo takes us through an issue with naming in DAX:

You deploy this model and start creating reports using the Sales Returning Customers measure. So far, so good. One day, you need to extend the data model importing a new table that you decide to name ReturningCustomers. As soon as you import the new table named ReturningCustomers, your measure Sales Returning Customers stops working. The reason is that the ReturningCustomers variable generates a name conflict with the table that has the same name, as you can see from the error message.

‘ReturningCustomers’ is a table name and cannot be used to define a variable.

Marco has some advice if you’re in a situation where you are liable to see this pop up.

Comments closed

Java With Visual Studio Code

Published 2019-01-17 by Kevin Feasel

Niels Berglund learns about Visual Studio Code and writing Java in VS Code:

I mentioned above how Maven is a build automation tool for primarily Java projects. There are other build tools as well, but in this post, I use Maven as it is – which I mentioned above – the de-facto standard for Java-based projects.

Above we saw how we installed Maven as well as the VS Code Maven extension, however before we start to use it let us talk a little about Maven archetypes and naming conventions.

If I were in Java day in and day out, I’d probably go with IntelliJ IDEA but for occasional work, I can see VS Code doing well. That’s the interesting use case for Visual Studio Code: with all of the extensions, it’s a really good multi-purpose IDE while still being worse than the “natural” IDE for almost any single language.

Comments closed

SQL Undercover Inspector V1.3

Published 2019-01-17 by Kevin Feasel

Adrian Buckman announces a new version of the SQL Undercover team’s Inspector:

We know some of you really hate linked servers so we have been working on a powershell collection which will allow you to install the inspector without using linked servers to centrally log the information and instead the powershell function Invoke-SQLUndercoverInspector will do the rest for you (We will be writing a blog post about how you can use this soon) – this is currently a pre-release version so it’s a work in progress – I must say a massive thank you to Shane O’Neill (b | t) without his powershell skills this wouldn’t turned out as well as it has, thanks Shane!

If you’ve already downloaded this version, be aware that there is a hotfix.

Comments closed