Press "Enter" to skip to content

Curated SQL Posts

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

Thoughts on a Migration: Azure Analysis Services to Power BI Premium

Published 2022-12-06 by Kevin Feasel

Dan English shares some thoughts:

Over the past month couple of months I got the opportunity to test out the new migration experience that was just made available for Public Preview this past month during the PASS Data Community Summit and announced on the Power BI blog here Accelerate your migration experience from Azure Analysis Services to Power BI Premium with the automated migration tool. The blog post also shows a very quick animated gif walkthrough of the process and there is a thirteen minute video from the MS Build conference earlier this year where this was first demoed that you can check out here as well The Future of Enterprise Semantic Models.

Click through for a detailed analysis.

Comments closed

A New Sample Database

Published 2022-12-06 by Kevin Feasel

Daniel Hutmacher isn’t satisfied with AdventureWorks:

The database collation is Latin1_General_100_CI_AS_SC.

I’ve divided information into schemas based on their respective sources. The “ReferenceData” schema will have mixed sources, all of them publicly available.

Because the data is so geographically bound, many of the tables with have geo data as well, though I technically put it in a geometry type and not a geography type – just because it was easier. This can make for some cool map visuals in SSMS if you want.

Most columns and tables are annotated using the extended property MS_description, so if you view the extended properties in SSMS, or if you use my sp_ctrl3 utility, a brief description will show up for each object.

Read on for an overview of the database’s schema, as well as the link to download the DB. I’ll have to check it out.

Comments closed

The Lowdown on the Power BI Gateway

Published 2022-12-06 by Kevin Feasel

Reza Rad gives us a primer:

Power BI is a data analysis tool that connects to many data sources. Suppose the data source for Power BI is located in an on-premises location. In that case, the connection from the cloud-based Power BI Service to the on-premises located data source should be created with an application called Gateway. In this post, you will learn what the Gateway is, the types of the gateway, their differences, installing the gateway, and scheduling a data set with that gateway.

Click through for a video, as well as a lengthy article covering the topic.

Comments closed