The Bit Bucket

I was working with a client who has having trouble debugging an ADF pipeline, related to an ODBC linked service not working as expected.

The user had configured the connection string property of an ODBC connection this way:

•  Set a parameter to the linked service as ServiceDSN
•  Configured the connection string as @concat(‘DSN=’,linkedService().ServiceDSN)

The Test Connection for that worked fine, but when you click Apply, it fails with an Internal Server Error. Specifically, the error was:

2023-04-24

I was able to record another new Cosmos Down Under podcast today. My guest was Microsoft Senior Program Manager Rodrigo Souza.

In the show, we discussed the Change Data Capture feed for the Analytical store in Azure Cosmos DB. This is a powerful new capability and worth learning about.

I hope you enjoy it. You’ll find it, along with all Cosmos Down Under podcasts at:

Cosmos Down Under Podcast

2023-04-12

Over the last year, I’ve come to know Alex Kolokolov more, through involvement with his data visualization challenges. I was really pleased to see he’d written his first book Make Your Data Speak (Creating Actionable Data through Excel For Non-Technical Professionals).

Things I Liked

I really liked the conversational style of the book. It’s all structured around  an approach of “Let’s see how this happens by example”. The tone was really refreshing and should be good to hold people’s interest.

2023-03-12

In the part 1 of this series, I discussed the positioning of Change Data Capture. In part 2, I want to cover how it works.

Log Reading

There are many ways that you can output details of changes that occur in data within SQL Server. Many of those methods require actions to occur at the time the data change is made. This can be problematic.

The first problem with this, is the performance impact on the application that’s making the change. If I update a row in a table and there is part of the process that writes details of that change to some type of audit or tracking log, I’ve now increased the work that needs to happen in the context of the application that’s making the change. Generally what this means, is that I’ve slowed the application down by at least doubling the work that needs to be performed. That might not be well-received.

2023-03-06

I’ve worked in this industry a long time now.

From 1981 to 1986, I worked as an engineer for Hewlett-Packard. Yep, I’m “have actually met both Bill Hewlett and Dave Packard” old. They’d retired when I met them and John Young was in charge, but they came to Palo Alto to a shareholders’ meeting and I was there at the time.

Most of my work was on HP3000 commercial mini-computers. The level of engineering on those systems was unlike anything I’d ever seen either before or since. They were really quite superb.

2023-03-05

I often need to capture the changes from one database into another. The most common reason is that I’m wanting to bring changes from a transactional system across into a data warehouse that’s part of a BI setup.

So which technology is best to use for this?

That’s not a trivial question to answer but here are some thoughts:

Replication?

Unfortunately, this one’s not available for Azure SQL DB as yet. Azure SQL DB can be a subscriber in Transactional Replication. We often use it this way. If we have an on-premises SQL Server, one of our favourite ways to get data into the cloud is by using Transactional Replication. (If you need to get your head around Replication with SQL Server, just head to our course here).

2023-02-26

I published a new Cosmos Down Under podcast today.

In the podcast, I’ve discussed the new integration between Azure Cosmos DB and Azure Data Explorer with the program manager for the feature: Vincent-Philippe Lauzon.

It’s a great new capability and worth half an hour of your time to find out about it.

I hope you enjoy it. You’ll find it, along with all Cosmos Down Under podcasts at:

Cosmos Down Under Podcast

2023-02-17

Just a heads-up that v23 of SDU Tools went out to SDU Insiders yesterday.

You can find details about SDU Tools here: https://sdutools.sqldownunder.com

In v23, we’ve added or enhanced the following:

• ExcelSerialToDateTime - there’s so much corporate data stored in Excel, and if you need to import it to SQL Server, you quickly find that dates and times are a challenge. I have a friend who says that the only data types in Excel are numbers and strings, and that everything else is an illusion. I think that’s pretty accurate. This function takes one of the funky Excel decimal numbers (called a Serial) that represents a date and time, and converts it to a SQL Server datetime value.
• DateTimeToExcelSerial - no prizes for guessing what this one does. It’s the opposite of the above.
• We’ve done a lot of updates to internal data within the tools, like all up to date SQL Server build numbers, etc.
• DateDimensionColumns now has StartOfWeek and EndOfWeek
• DateDimensionPeriodColumns now has IsSameWeek, DayOffset, MonthOffset, and WeekOffset

You can see a short video on the new Excel conversion functions here:

2023-01-19

A while back, I reviewed a book called Atomic Habits by James Clear. I mentioned at the time that I nearly didn’t get past the first chapter as I thought it was going to be another pretty cheesy self-help book. But I ended up loving the book. James really made me think about habits in a way that I hadn’t done so before.

And so I was interested when Luke and Phil from Mandarin Blueprint released a new online course called the Habit-Building Bootcamp.

2023-01-18

I recorded the first of a series of podcasts for the SQL Server 2022 timeframe recently. Once again, it was great to chat with another old friend Davide Mauri. Davide is a Principal Product Manager for Azure SQL Database.

Note that the topic I chose for today was the new  sp_invoke_external_rest_endpoint system stored procedure, and it’s currently only available for Azure SQL Database but I expect it will get ported to other flavours of SQL Server over time. Davide has been deeply involved with the development of this procedure.

2022-12-16