Press "Enter" to skip to content

Curated SQL Posts

SQL Server and Daylight Savings Time

Published 2022-03-25 by Kevin Feasel

Joe Pollock has to turn the clocks forward:

At the start of Daylight Saving Time (DST), which is this weekend in the UK, the clocks will move forward by one hour at 1am. Apart from the fact that we all lose an hour of our night’s sleep, what effect will this have on your scheduled jobs in SQL Server?

As we saw in my last blog post, we know that the SQL Server engine always carries on regardless of when the clocks change, it knows that this has happened, but nothing unusual occurs in the engine itself. However, SQL Server Agent, which runs scheduled tasks, is not the same, as this directly impacts how it works. 

Read on to see what can happen. Also, this can be relevant when you have unique key constraints on datetime values.

Comments closed

Power Apps Building Blocks

Published 2022-03-24 by Kevin Feasel

Elayne Jones gives us an introduction to Power Apps:

The starting point for working with Power Apps is an environment. Environments house your business’ apps, data, chatbots, and flows. By building apps in a single environment, users can isolate content aimed at a specific use case or target their content towards a specific team or department. A common practice is to build separate environments for Development, Test, and Production stages. Power Apps Environments can even connect to GitHub, streamlining source control within an organization.

An Azure Active Directory tenant is required to create an environment, and only users within that tenant can access the content within the environment. After the environment is created, users deploy data sources to that environment. Thereafter, the content created can only connect to the data sources within the same environment. You can create a database in each environment, but there can only be one database in each environment.

Read on for a lot more.

Comments closed

Data Communication and Culture

Published 2022-03-24 by Kevin Feasel

Alex Velez notes one additional way that people may interpret your charts differently from your expectations:

Before I share some considerations for presenting data internationally, I want to acknowledge that I am not an expert on different cultures and audiences. In this post, I simply share some of my experiences with the hope that others will provide additional commentary for increased learning. If you have related thoughts, please share in the comments. 

Let’s consider five observations of regional differences I’ve encountered while communicating data.

Read on for those observations.

Comments closed

Fun with DAX in Paginated Reports

Published 2022-03-24 by Kevin Feasel

Adam Aspin looks at paginated reports:

To conclude this short set of articles on using Power BI datasets as the source of data for paginated reports, I want to outline a few classic solutions to common challenges in paginated report development with DAX.

Clearly, I cannot recount every paginated report challenge that I have ever met (or heard of) when creating reports from a Power BI dataset. However, as a report developer, it helps to be aware of some of the standard solutions to the challenges that many users encounter.

This article uses the accompanying sample data (CarsDataWarehouse.pbix) as the basis for the DAX that you will use to solve these problems.

Click through for several tips, as well as some tricks.

Comments closed

Power BI Dataflows and Storage Considerations

Published 2022-03-24 by Kevin Feasel

Teo Lachev has some things for us to consider:

Over the past few years, the BI industry has come up with new file formats, such as Parquet, ORC, and Avro, which are widely used today. To facilitate its vision for cross-industry data integration, Microsoft introduced a few years ago the Common Data Model (CDM) and CDM Folders. Power BI dataflows output CSV files to CDM folders and each table is saved in its own folder. You can bring your own data lake to directly access these files. If do so, you’ll find the following folder structure:

Although accessing the dataflow files might open all sorts of data integration scenarios, here are some things to watch for concerning the dataflow output:

Read on for five things.

Comments closed

Failure to Write to the Security Event Log

Published 2022-03-24 by Kevin Feasel

Sean Gallardy files events to the circular file:

Getting back on track instead of listening to me complain, many DBA’s and internal security folks like writing to the Windows Security Event Log because the word security is in the name and they have some tool like Splunk that automatically collects these details. I like automation, so that’s a nice win. However, you may be running into SQL Server error 33204 which is a failure to write an audit event to the security event log.

Read on to find one reason why this might happen.

Comments closed

Sending E-Mail via Azure Logic Apps

Published 2022-03-24 by Kevin Feasel

Maria Zakourdaev replaces Database Mail:

Azure SQL database is a fully managed relational database in the Microsoft cloud. It is a scalable, durable and performant database engine that allows developers to focus on the business logic without worrying about storage or server uptime. The main difference between SQL Server on premises and Azure SQL Database is that in Azure SQL Database only database level features are available. Features, like SQL Server Agent or SQL Server Mail are not supported by Azure SQL Database. However, in some situations we want to send an email from inside stored procedures. This post will show how to solve this challenge.

I’ve found Logic Apps to be surprisingly easy for this kind of thing, including connectors to outside mail services like Gmail. H/T Maria via Madeira Data’s blog.

Comments closed

So You Want to Migrate a SQL Server

Published 2022-03-24 by Kevin Feasel

Jon Biggs has a guide for us:

We are currently performing migrations with upgrade of multiple-instance SQL Servers to new servers. The migrations are going smoothly (knock on wood), and I wanted to relay some information about the migration process. There are four phases you need to perform when migrating a SQL instance to a new server. These phases are Review, Prepare, Test, and Migrate. It sounds simple enough but let’s take a deeper look into what goes into each phase.

Read on for Jon’s take on the migration process.

Comments closed

Azure Purview Workflows in Public Preview

Published 2022-03-24 by Kevin Feasel

Victoria Holt makes note of an improvement to Purview:

Azure Purview Workflows moved to Public Preview 10 March.  This functionality enables customers to orchestrate the create, update and delete operations of data entities, have validation, and approval of these data entities using repeatable business processes.

The benefit of using Purview workflows is higher quality data, policy compliance, user collaboration, and change tracking awareness across the organization.

Read on for more information about how workflows currently work.

Comments closed