Press "Enter" to skip to content

Month: March 2023

Changing the Browser SSMS Uses

Published 2023-03-29 by Kevin Feasel

Meagan Longoria doesn’t want the built-in browser:

Did you know that you can change the browser used by SQL Server Management Studio to authenticate using Azure Active Directory to a SQL database in Azure?

I had been experiencing serious delays with the window that pops up to accept my credentials taking 30 seconds or more to populate. I also once got a warning that the browser I was using was old.

Click through to see how to resolve annoyances around SSMS’s built-in browser and change to the default you use for everything else.

Comments closed

Choosing a SKU for Azure Data Explorer

Published 2023-03-29 by Kevin Feasel

Brian Bønk makes a choice:

When creating the clusters from the Azure portal, you are presented with 3 options when choosing the compute specification.

The compute specification is the method of setting up the clusters for the specific workload you are planning to put on the Kusto cluster.

The portal gives you these three options:

Read on for the options, as well as some recommendations on when you might choose each.

Comments closed

Restoring an Azure SQL Database

Published 2023-03-29 by Kevin Feasel

Andrea Allred recovers from a mistake:

Recently, the wrong table got dropped and we needed to bring it back. I had never done a restore in an Azure Managed Database before so I learned something really fast.

Click through for the process. And yeah, it is quite easy, though I’ve noticed that restore times are a bit slower than if you were using local hardware on-premises.

One neat trick with database restores in Azure SQL DB: you can’t restore over an existing database, something a client wanted me to do last week. What you can do, however, is restore the database under a new name, so we might have messedupdb and then messedupdb_restore. Well, in this case, messedupdb had no changes since “the incident,” so what we were able to do was rename messedupdb to messedupdb_dropme and rename messedupdb_restore to messedupdb. Azure SQL DB happily rolls on with this and after ensuring that the database was now in prime condition, we could drop the old version. It’s a little more complex than simply restoring over the existing database, but all the relevant metadata Azure SQL DB needs stayed in sync along the way, so the process was smooth.

Comments closed

Concatenation per Group in MySQL

Published 2023-03-29 by Kevin Feasel

Rahul Mehta asks the big questions:

In this article, we are going to discuss how to aggregate the article at a row level. MySQL provides a function called “group_concat” to perform row-level concatenation. Before we go ahead and learn how to do so, let us first understand:

  • Why do we need it?
  • Where do we need it?
  • When to use it?

Fortunately, Rahul then answers these questions; otherwise, it’d be a pretty short article. The T-SQL analog to this is STRING_AGG(), though the syntax and behavior is not precisely the same.

Comments closed

Building a Unit Chart

Published 2023-03-28 by Kevin Feasel

Elizabeth Ricks describes a type of visual:

Unlike other charts that use line length, position on an axis, or area to represent values, unit charts are made up of individual markers—geometric shapes, icons, images, etc—that encode quantities based on how many of that unit are shown. Examples of unit charts are ISOTYPE charts, square area charts (also known as waffle charts), symbol charts, and pictorial charts. They are commonly used in media, advertisements, and infographics. 

Click through for some good use cases for unit charts.

Comments closed

OneDrive and Disappearing Powershell Modules

Published 2023-03-28 by Kevin Feasel

Robert Cain diagnoses a weird issue:

I was having a weird problem with PowerShell, while working on my ArcaneBooks project. I would install a module, and it would work fine at first. I could see the module when I used Get-Module -ListAvailable, and use it.

The problem occurred when I closed the terminal (whether the Windows Terminal or in VSCode), then reopened it. The module had vanished! It was no longer there, I would get an error if I tried to import it, and Get-Module -ListAvailable would no longer show it. I could reinstall, it’d be there, but again when I closed the terminal and reopened it would be gone.

Read on to learn what OneDrive was doing and how Robert at least got it to stop doing that.

Comments closed

.NET Framework Versions and ADO Pipeline Builds

Published 2023-03-28 by Kevin Feasel

Olivier Van Steenlandt runs into a versioning issue:

The error message I received during the build process in my Azure DevOps YAML Pipeline was :

##[error]C:\Program Files\Microsoft Visual Studio\2022\Enterprise\MSBuild\Current\Bin\Microsoft.Common.CurrentVersion.targets(1229,5): Error MSB3644: The reference assemblies for .NETFramework,Version=v4.5 were not found. To resolve this, install the Developer Pack (SDK/Targeting Pack) for this framework version or retarget your application. You can download .NET Framework Developer Packs at https://aka.ms/msbuild/developerpacks

I wasn’t sure how to solve this issue, and when I was using my on-premise Agent Pool, the Database Project was able to build successfully.

Click through for the solution

Comments closed

The Problem with Stacked Bar Graphs

Published 2023-03-28 by Kevin Feasel

Rita Fainshtein looks at a stacked bar graph:

Let’s look at what motivated our choice and whether it is indeed a graph that will convey accurate messages to those who read it.

Here are some reasons why this type of visualization is preferred:

1. The graph on the right is one of the “recommended” graphs provided by tools (both Excel and Power bi).

2. It seems logical: there is a height comparison between case managers, clear separation between client types and the graph looks colorful and appealing.

But behind this, there are some major problems with the visual. Read on to learn what those are and for one alternative visual which can be better.

Comments closed

What-If Operations in Powershell

Published 2023-03-28 by Kevin Feasel

Chad Callihan looks at one of my favorite features in Powershell:

We’ve all had moments of instant regret. Maybe it was missing that WHERE clause when executing a SQL statement or seeing something like “500 rows affected” when expecting only 1. These are sinking feelings we never want to experience again.

To avoid that pain, SQL Server has BEGIN/ROLLBACK/COMMIT that you can use to help check your work. Did you know PowerShell also has a helpful switch called WhatIf to preview changes before they’re applied?

This does take a bit of effort to implement in your custom modules, but the fact that the language has this concept explicitly laid out is a smart idea.

Comments closed

Managed Identities and Invoking REST Endpoints from Azure SQL DB

Published 2023-03-28 by Kevin Feasel

Imke Feldmann executes a Power BI REST endpoint call from Azure SQL Database:

For Azure SQL Databases there is a very cool new preview feature: “sp_invoke_external_rest_endpoint “. This function allows you to call certain Microsoft API endpoints directly from within your Azure database and write that data back into a table for example.

With that, you can for example create a stored procedure that can be triggered from Power Automate. This is ideal for larger datasets that would require long and slow “apply-to-each” rounds or cumbersome bulk-upload-workarounds.

I was struggling with the authentication when using a system assigned managed identity (“service principal”). Thanks to Davide Mauri for telling me how to fill in the parameters for the DATABASE SCOPED CREDENTIALS to make this work for Power BI:

Click through to see that answer, as well as a demonstration of the entire process.

Comments closed