Press "Enter" to skip to content

Day: July 10, 2019

Amazon Elasticsearch Alerts

Published 2019-07-10 by Kevin Feasel

Jon Handler shows how to create alerts for Amazon Elasticsearch Service:

On April 8, Amazon ES launched support for event monitoring and alerting. To use this feature, you work with monitors—scheduled jobs—that have triggers, which are specific conditions that you set, telling the monitor when it should send an alert. An alert is a notification that the triggering condition occurred. When a trigger fires, the monitor takes action, sending a message to your destination.

This post uses a simulated IoT device farm to generate and send data to Amazon ES.

Click through for a demo.

Comments closed

R 3.6.1 Available

Published 2019-07-10 by Kevin Feasel

David Smith notes a new version of R is available:

On July 5, the R Core Group released the source code for the latest update to R, R 3.6.1, and binaries are now available to download for Windows, Linux and Mac from your local CRAN mirror.

R 3.6.1 is a minor update to R that fixes a few bugs. As usual with a minor release, this version is backwards-compatible with R 3.6.0 and remains compatible with your installed packages. 

Click through for the changes. There is one nice addition around writeClipboard but otherwise it’s a release where you probably update if you’re bothered by a bug it fixes and otherwise skip.

Comments closed

Order of Execution on SELECT Expressions

Published 2019-07-10 by Kevin Feasel

Bert Wagner digs into an interesting topic:

Success! But as I was celebrating my dynamic SQL victory, I realized I was making an assumption about SQL Server that I had never thought about before:

The above query only works because SQL Server is executing the variables in the SELECT list sequentially. I’m incrementing @CurrentRow only after processing my @RowQuery variable, and this logic only works correctly if SQL Server executes the variable expressions in the order they appear in the SELECT list. If SQL Server was executing items in the SELECT list in reverse or random order, @CurrentRow could potentially get set BEFORE @RowQuery was evaluated, causing the logic of adding “UNION ALL SELECT” in the right location to fail.

I have a vague recollection that you couldn’t always count on this, though I admit to never having seen a counter-example. I don’t believe expression execution order is defined in the ANSI SQL standard—the idea is that everything is typically resolved “at once.”

Comments closed

Nested Window Functions in ANSI SQL

Published 2019-07-10 by Kevin Feasel

Itzik Ben-Gan covers a hypothetical feature in the ANSI standard:

The standard nested window functions seem like a very powerful concept that enables a lot of flexibility in interacting with different points in windowing elements. I’m quite surprised that I cannot find any coverage of the concept other than in the standard itself, and that I don’t see many platforms implementing it. Hopefully this article will increase awareness for this feature. If you feel that it could be useful for you to have it available in T-SQL, make sure to cast your vote!

Check it out and upvote if you’re interested in seeing this functionality in SQL Server.

Comments closed

Scheduled Refresh on Power BI Custom Connectors

Published 2019-07-10 by Kevin Feasel

Kasper de Jonge shows how to enable scheduled refresh on custom connectors in Power BI:

Here is a post that is long overdue as the functionality has been available for almost a year now. Today we will enable scheduled refresh to the Strava custom connector I build before.

The trick to this is that we need to extend the code of the Strava connector with a “TestConnection” function as is described here. This will allow the Power BI gateway to know how to test the connection.

There’s just a little bit of code involved.

Comments closed

Stats Time Versus Plan Operator Time

Published 2019-07-10 by Kevin Feasel

Erik Darling explains why SET STATISTICS TIME ON can give you different timing results from what the execution plan states:

Here are the relevant details:

SQL Server Execution Times:
CPU time = 3516 ms,  elapsed time = 3273 ms.

What looks odd here is that CPU and elapsed time are near-equal, but the plan shows parallelism.

Thankfully, with operator times, the actual plan helps us out.

The query itself ran for <900ms.

The answer makes perfect sense.

Comments closed

Security Update for SQL Server

Published 2019-07-10 by Kevin Feasel

K. Brian Kelley notes a slew of patches for July:

CVE-2019-1068 | Microsoft SQL Server Remote Code Execution Vulnerability

It’s a remote code exploit, but the attacker has to be connected to SQL Server because the vulnerability can only be exploited using a specially crafted query. The code would execute in the context of the database engine service account (hopefully not configured to run with administrative rights on the server or elevated rights in Active Directory).

Check this out and get it patched.

Comments closed

dbatools and Linux

Published 2019-07-10 by Kevin Feasel

Chrissy LeMaire takes us through dbatools support on Linux:

As a long-time Linux user and open-source advocate, I was beyond excited when PowerShell and SQL Server came to Linux.

A few of the decisions I made about dbatools were actually inspired by Linux. For instance, when dbatools was initially released, it was GNU GPL licensed, which is the same license as the Linux kernel (we’ve since re-licensed under the more permissive MIT). In addition, dbatools’ all-lower-case naming convention was also inspired by Linux, as most commands executed within Linux are in lower-case and a number of projects use the lower-case naming convention as well.

Considering how many OS-specific operations there are, the percentage of Powershell commands in dbatools which work is excellent.

Comments closed