Press "Enter" to skip to content

Month: April 2024

Deploying SSIS Components using Custom Components

Published 2024-04-16 by Kevin Feasel

Andy Brownsword forgets something at home:

Within SSIS you can make use of custom components which aren’t present out of the box. An example of some would be the Azure Feature Pack if you’re working with cloud resources.

These will let us use features not available natively. They can also provide a challenge down the line when we come to deploy changes to the project.

Here we’ll look at an example of this challenge, how to troubleshoot, and ultimately resolve the issue.

Read on for the scenario and fix.

Comments closed

Regular Expressions in R

Published 2024-04-15 by Kevin Feasel

Steven Sanderson now has two problems:

Regular expressions, or regex, are incredibly powerful tools for pattern matching and extracting specific information from text data. Today, we’ll explore how to harness the might of regex in R with a practical example.

Let’s dive into a scenario where we have data that needs cleaning and extracting numerical values from strings. Our data, stored in a dataframe named df, consists of four columns (x1, x2, x3, x4) with strings containing numerical values along with percentage values enclosed in parentheses. Our goal is to extract these numerical values and compute a total for each row.

Click through for a worked-out example.

Comments closed

Updates to Open-Source Procedures sp_QuickieStore, etc.

Published 2024-04-15 by Kevin Feasel

Erik Darling has been busy:

Let’s start with giving some credit, here, since I hate an overdue bill.

ReeceGoding reported and very capably fixed an issue with sp_QuickieStore. The problem only occurred when you ran the procedure with @get_all_databases and the new @escape_brackets parameter set to true. Each loop would add an escape character to the search string, which could end up looking like this: %\\\\\[AnyStringStartingAndEndingWithSquareBrackets]\\\\\]%'

That’s a fix in place, and you can also see updates to sp_HealthParser and sp_PressureDetector.

Comments closed

Avoid ROLLBACK in Triggers

Published 2024-04-15 by Kevin Feasel

Thom Andrews shares a bit of advice:

A problem I’ve seen raised on numerous occasions is users that are getting a non-descript error when they are doing some kind of DML/DDL operation, and they don’t know why they are getting the error, and what is causing it. That error is:

Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Read on to learn more about good alternatives, remembering that you’re in the context of some other call when a trigger fires.

Comments closed

Elastic Jobs in Azure SQL DB Now GA

Published 2024-04-15 by Kevin Feasel

Srini Acharya makes an announcement:

Elastic Jobs is a fully integrated Azure SQL database service that allows you to automate and manage administrative tasks across multiple SQL databases in a secure, scalable way. It can run one or more T-SQL job scripts in parallel using Azure portal, PowerShell, REST, or T-SQL APIs. Jobs can be run on a schedule or on-demand, targeting any tier of Azure SQL Database. Job target can include all databases in a server, in an elastic pool, across multiple servers and even databases across different subscriptions and geo regions on Azure. Servers and pools are dynamically enumerated at runtime, so jobs run against all databases that exist in the target group at the time of execution.

If you’ve held off on Azure SQL DB because of a lack of the SQL Agent, take a look at this option.

Comments closed

Snake Draft Ordering in SQL Server

Published 2024-04-15 by Kevin Feasel

Aaron Bertrand demands order:

I play fantasy football. To start each season, there is a draft, where players are randomly assigned draft order. We’ve started some seasons with a “snake” draft, to make the selection of players most fair and balanced. A snake draft works like this (a simple example with four players):

  • First pick in the first round picks last in the second round.
  • Second pick in the first round picks third in the second round.
  • Third pick in the first round picks second in the second round.
  • Last pick in the first round picks first in the second round.
  • Then, in the third round, it reverts to the original order: The first pick from the first round picks first again (giving them two picks back-to-back).

Read on to see how to implement this in T-SQL. I’m happy to report that I anticipated the answer, though Aaron notes a couple of issues that I didn’t catch in my “compile the code in my head” approach.

Comments closed

SQL/JSON in Postgres

Published 2024-04-15 by Kevin Feasel

Hubert Lubaczewski makes note of some forthcoming functionality:

Back in March/April of 2022 Andres Dunstan committed a series of patches that added support for lots of really interesting features from SQL/JSON standard.

While I’m not avid user of json in database, I was very, very happy. Wrote couple of blogposts about it.

Then, around six month later they got reverted.

Lately, since last year, actually, these re-appeared again:

Read on for the list of commits and the current state of things with respect to implementing the SQL/JSON standard in Postgres.

Comments closed

A Scaffolding Design Pattern for Microsoft Fabric Pipelines

Published 2024-04-15 by Kevin Feasel

Andy Leonard shares some thoughts on design:

When assigned a project, it’s tempting – and dangerous – to Just Start Coding. If you suffer from the urge to develop first and design later, you are not alone (there’s at least one other developer like you and he’s typing this post). Do yourself a favor and…

Read on for more information on Andy’s design-first mentality and a sample of how you might lay out that initial design.

Comments closed

Selecting by Index in R

Published 2024-04-12 by Kevin Feasel

Steven Sanderson grabs some rows:

Imagine you want to analyze the fuel efficiency (miles per gallon) of a particular car. Here’s how to grab a single row by its index (row number):

Read on for that example, as well as examples covering two other cases: multiple rows based on index value, and ranges of indices.

Comments closed

Transactional Replication Slow: Check Missing Indexes

Published 2024-04-12 by Kevin Feasel

Lori Brown provides a good tip:

Have you ever had transactional replication mysteriously start showing significant latency at a subscriber server?  If so, check to see if the primary keys from the publisher database are missing on the subscriber database!

Replication was showing long latency while the publisher and subscriber servers were not heavily utilized.  Microsoft generated stored procedures that are used to send INSERT, UPDATE and DELETE operations from the publisher database to the subscriber database had no indexes to help query performance to the subscriber tables.  Since there were no indexes that matched the keys of the statements pushing through data changes, tables were being fully scanned and replication statements were piling up.  In other words, the subscriber was missing indexes. 

Read on to see how you can check this and also how you can quickly script those missing indexes.

Comments closed