Press "Enter" to skip to content

Curated SQL Posts

Enabling Modern Styles on Old Power BI Reports

Soheil Bakhshi saves us some time:

I see some developers really hate the old style of the visuals when they have to work on an old report and it makes them crazy to see that the visuals style remains the same even if they create a new report page and add new visuals to the page. So some developers go through a lot of pain to get the visuals to work in the modern style. I even know some developers copied all queries from Power Query from the old report and pasted them to a new Power BI file. Then they used Tabular Editor to copy the tabular objects from the old report to the new one. And some even started to build everything from scratch. If you are one of those developers, be aware that there is a simple setting that can help to quickly switch all the old visuals to their new modern style.

Read on to learn which setting that is.

Comments closed

Github Autopilot and Insecure Code Suggestions

Mayank Sharma reviews an article:

Academic researchers discover that nearly 40% of the code suggestions by GitHub’s Copilot tool are erroneous, from a security point of view.

Developed by GitHub in collaboration with OpenAI, and currently in private beta testing, Copilot leverages artificial intelligence (AI) to make relevant coding suggestions to programmers as they write code.

To help quantify the value-add of the system, the academic researchers created 89 different scenarios for Copilot to suggest code for, which produced over 1600 programs. Reviewing them, the researchers discovered that almost 40% were vulnerable in one way or another. 

Click through to learn more, as well as a link to the article itself. I would be interested in reading GitHub’s thoughts on this.

Comments closed

Using Index Column Order and Dynamic SQL to Fix Non-SARGable Queries

Erik Darling wraps up season 1 of Saving Sarge with a cliffhanger. First up, how setting up indexes to lead with SARGable columns makes sense:

Quite a bit, I find myself working with people who are able to change indexes, but unable to change queries.

Even making small, sane changes would nix their support, should they ask for it. I do sometimes have to laugh at the situation: if support were that great, they wouldn’t need me, and if we made the change, they probably wouldn’t need support.

Oh well, though. Keep me employed, ISVs.

Then, Erik takes a look at using dynamic SQL to solve one class of non-SARGable predicates:

The non-SARGABLE pattern that dynamic SQL helps you deal with is the the catch all query pattern, which can look like:

– col = @parameter or @parameter is null
– col = isnull(@parameter, col)
– col = coalesce(@parameter, col)

Or any similar variation of null/not null checking of a parameter (or variable) in the where/join clause at runtime.

Dynamic SQL allows you to build up the specific where clause that you need for the non-NULL set of search filters.

Stay tuned for the next thrilling episode of Saving Sarge. Same SARG-time, same SARG-channel.

Comments closed

E-Mailing a DBA upon Object Creation in System Databases

Thomas Williams has another script for us:

Next on the list to commission a SQL Server, is to create a trigger in the master system database to send an e-mail to the DBA when objects are created in the database.

In general I’d expect this never to happen – but if it did, I’d want to follow up. It’s possible to go even further and prevent creating objects in the master system database completely.

My experience is that about 5% of the time, people intentionally put objects into the master database, and 95% of the time, I…err, they…forgot to switch databases before running the script.

Comments closed

When APPEND_ONLY_STORAGE_INSERT_POINT is a Bottleneck

Paul Randal explains the APPEND_ONLY_STORAGE_INSERT_POINT latch and describes when and why this might be a problem:

Continuing my series of articles on latches, this time I’m going to discuss the APPEND_ONLY_STORAGE_INSERT_POINT latch and show how it can be a major bottleneck for heavy update workloads where either form of snapshot isolation is being used.

I strongly recommend you read the initial post in the series before this one, so you have all the general background knowledge about latches.

As always, read the whole thing.

Comments closed

Debugging Memory Access Violation Errors

Sean Gallardy explains access violations:

Access Violations (AV) are another common error that will cause SQL Server to take a memory dump. These can occur for a variety of reasons, and unlike the last one (Non-Yielding Scheduler) it’s quite the task, especially given public symbols don’t have structure data and offsets.

Read on to see what they are, what a dump file may include, and why they can be so difficult to debug.

Comments closed

Connecting to REST APIs via OAuth2 in Power BI

Chris Webb has an answer, but you may not like it:

There are a lot of articles and blog posts out there on how to handle OAuth2 authentication when connecting to REST APIs from Power Query in Power BI. However there is also a lot of confusion and contradictory information too so in this post I want to give you the definitive, Microsoft-endorsed answer to this question, which is:

If want to connect from Power BI to a REST API that uses OAuth2 authentication then you need to build a custom connector.

Read on for documentation showing how to implement and the big risk you’re taking if you don’t use a custom connector.

Comments closed

Estimating the Likelihood of an Underdog Winning at Soccer

Holger von Jouanne-Diedrich lays out the math for us:

The Bundesliga is Germany’s primary football league. It is one of the most important football leagues in the world, broadcast on television in over 200 countries.

If you want to get your hands on a tool to forecast the result of any game (and perform some more statistical analyses), read on!

What I would like is a tool which has SC Freiburg utterly dominating Bayern. Said tool may be more mythological than scientific (or at least a copy of Football Manager and a little bit of save scumming…), but I’ll take it.

Comments closed

Security Breach in Cosmos DB: ChaosDB

Nir Ohfeld and Sagi Tzadik discovered a flaw in Azure Cosmos DB:

Nearly everything we do online these days runs through applications and databases in the cloud. While leaky storage buckets get a lot of attention, database exposure is the bigger risk for most companies because each one can contain millions or even billions of sensitive records. Every CISO’s nightmare is someone getting their access keys and exfiltrating gigabytes of data in one fell swoop.

So you can imagine our surprise when we were able to gain complete unrestricted access to the accounts and databases of several thousand Microsoft Azure customers, including many Fortune 500 companies. Wiz’s security research team (that’s us) constantly looks for new attack surfaces in the cloud, and two weeks ago we discovered an unprecedented breach that affects Azure’s flagship database service, Cosmos DB.

Read on for details about the attack. Microsoft has already mitigated the issue by disabling the functionality necessary to pull off the attack. H/T Ben Stegink.

Comments closed

Multi-Cloud Pros and Cons

James Serra lays out some of the benefits and drawbacks of using multiple cloud providers:

A discussion I have seen many companies have is if they should be single-cloud (using only one cloud company) or multi-cloud (using more than one cloud company). The three major Cloud Service Providers (CSPs) that companies use for nearly all use cases are Microsoft Azure, Amazon Web Services (AWS), and Google Cloud Platform (GCP).

Without spoiling it too much, James is not really sold on the idea.

Comments closed