Press "Enter" to skip to content

Month: December 2022

InvalidAbfsRestOperationException in Synapse Managed VNet

Published 2022-12-07 by Kevin Feasel

Kamil Nowinski goes down a rabbit hole:

This happens on the customer’s Synapse workspace where we have a public network disabled, so only private endpoint and managed VNET are available. Additionally, you probably spotted, that it took over 3 minutes to actually get this message. Hence, as a next step, in order to minimize the potential causes I simplified the query to make sure I have access to the Storage, by listing the files:

Click through for a story of pain, followed by glorious resolution.

Comments closed

Semi-Colons in Snowflake

Published 2022-12-07 by Kevin Feasel

Kevin Wilkie punctuates the statement:

With our last blog post, we started discussing Snowflake and the SELECT statement. Now, if you remember, there is this great thing called a semi-colon.

The main reason you should use the semicolon is to terminate all of your queries. Snowflake does this great thing by default, letting you run one query at a time.

I remember back when Microsoft deprecated T-SQL statements which did not end with semi-colons. It was fun speculating for about 5 minutes regarding the carnage which would happen if they carried out the deprecation notice, not least of which we’d find in Microsoft-developed code.

Comments closed

Tracking Database Errors with Extended Events

Published 2022-12-07 by Kevin Feasel

Eitan Blumin is watching you:

But interestingly enough – we would be getting an added benefit here. Even if there is no SQL injection attack, it’s still possible that such errors would be raised by the application – simply due to bugs.

Furthermore, these errors in the database may be happening without anyone even noticing! How could that be, you ask? Well, it could be due to bad error handling that “swallows” the error entirely, or because the errors are logged but no one is bothering to look at the logs, or maybe because the errors are caught but an undetailed error message is logged/displayed to the user (I can’t even count how many times I encountered “general database error” messages in applications), or because the developers simply decided to mark this as a “known issue” that they didn’t bother to fix and they didn’t think to ask their DBA about it… The reasons are numerous and varying.

Click through for the scripts. I had built something similar about a decade ago, a simple WPF app which watched for errors. I messaged him with something like “You missed a comma in that IN clause” and I saw him pop up from his cubicle and look around, trying to figure out how I could peek over his shoulder and see the query.

1 Comment

In Praise of Stored Procedures

Published 2022-12-07 by Kevin Feasel

Jay Robinson defends the highly defensible:

The point I’m trying to make is that the database code is treated as part of the solution. It is in the repos just like the C# code. It is deployed just like the C# code. I even include unit testing, just like the C# code.

I also keep all T-SQL in the database. There is no T-SQL code inside the C# code. Stored procedures are used for all functionality, even basic CRUD functions. In fact, probably 80–90% of the stored procedures in my systems are essentially CRUD functions. The only operation that doesn’t go through stored procedures is bulk insertion, and I use SqlBulkCopy for that.

Jay makes what I often consider the most important point from the standpoint of developers: that stored procedures act as an interface.

Comments closed

Connecting to SQL Server from R

Published 2022-12-06 by Kevin Feasel

Thomas Williams makes a database call:

R code (including in R Markdown and Shiny) can connect to databases, for both reading and writing. This opens up a whole world of powerful analysis – however, it can sometimes be tricky to deal with drivers, error handling and closing connections. In this post, I’ll demonstrate how to connect to a SQL Server database and run a query; in part 2, I’ll cover some of the things you might want to do with the query results.

Click through for two common libraries and a few tips.

Comments closed

Data and Compute in Azure ML

Published 2022-12-06 by Kevin Feasel

Tomaz Kastrun continues an advent series on Azure ML. Day 4 covers data sources:

Yesterday, we have learned the general outlook of the Studio and in this blog post, we will be focusing primarily on getting data to the workspace and reading data from other data sources.

Day 5 has you provision some compute:

With a basic understanding of data assets, let’s create compute instances. Under “Manage” in the navigation bar, select “Compute” (denoted as 1), select “Compute instances” (d. 2) and click on “+ New”.

Comments closed

AI: Lying at Card Games via Probabilistic Modeling

Published 2022-12-06 by Kevin Feasel

Matt Eland is on a mission:

I taught an AI to lie at my favorite card game.

In this article, I’ll explore how I did that and what considerations I had to make while designing an artificial intelligence to play a social deduction-based card game. I’ll also discuss where the project is headed and the potential approaches that you might consider building game systems as an AI developer.

The game I chose to model is One Night Ultimate Werewolf by Bezier Games. This is a social deduction game modelled on the popular party games of Werewolf and Mafia. I’ll give you a quick overview of the rules in the next section, for those unfamiliar with it.

This is an interesting overview of the card game, as well as describing the project itself.

Comments closed

The Basics of dbt in Spark

Published 2022-12-06 by Kevin Feasel

Ust Oldfield provides an introduction to dbt:

dbt is an abbreviation for data build tools. It is primarily a SQL based transformation workflow, supported by yaml, to allow teams to collaborate on analytics code whilst implementing software engineering best practices like modularity, portability, CI/CD, testing, and documentation.

dbt is available using a CLI in the form of dbt core, or as a paid-for SaaS product in the form of dbt cloud.

Click through to see how the product works, including an example.

Comments closed

What’s New in SSAS 2022

Published 2022-12-06 by Kevin Feasel

Chris Webb goes digging:

There was a time when a new release of SQL Server – and therefore a new release of SQL Server Analysis Services – was the most exciting thing in the world for me. New functionality! New things to blog about! Not so now that my focus, and Microsoft’s, is on Power BI and we get cool new functionality there every month. All the same there are still a lot of people running SSAS on-premises and SQL Server 2022 has just been released, so what’s new and is it worth upgrading?

Read on for Chris’s thoughts. MDX even makes an appearance.

Comments closed