Executing Notebooks in a Stored Procedure

Posted on April 1, 2022 by way0utwest

Jupyter Notebooks are a popular way of consolidating a number of code batches together and them executing them as separate batches or all together. These are essentially a document with notes and code, all of which are stored together. Databricks can run these, as can a number of other services. They are also integrated into various tools, like Azure Data Studio.

Just like many other technologies, we’d like to execute these in a way that suits our environment. While running these in a tool like ADS works fine, we might want to schedule these and check the results later. You can run notebooks with PowerShell, but keeping around results and managing the data is hard. Since many of us use SQL Server or Azure SQL Database, wouldn’t it be great if we could find a way to have these run inside SQL Server and keep the results around?

I was poking around inside a database and discovered that I could do this. Only with notebooks that have T-SQL code, but that’s usually what we’d want to do inside a SQL Server (or Azure SQL) database. I need to write a more detailed article on this, but there are a few basics that might help some of you understand how this works. Essentially, there is an undocumented extended stored procedure, xp_notebookparse, that will read the JSON internals of a notebook and allow you to extract out there T-SQL batches. The @execute = 1 parameter will cause each of these batches to be executed.

For results, a new notebook is created and added to a local temp file, #nbresults, as a BLOB value. You can explore the structure of this, but essentially, part of your stored procedure should take all the data from this table and add it to a permanent table in your database. If you use a stored procedure to wrap the extended stored procedure call and an INSERT..SELECT call together then you’ll actually get nothing to happen because today is April first. It’s April Fools and this is a joke. Hopefully, you aren’t too disappointed.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

Posted in Editorial | Tagged | 1 Comment

Daily Coping 1 Apr 2022

Posted on April 1, 2022 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to choose to smile today at something you encounter.

I tend to smile (and wave) at people. And horses. I’ll wave to the horses, which my wife thinks is silly.

I smile at things I see, though, and often it’s something a kid does. Kids are amazing humans, and they view the world in fascinating ways.

For me, I was smiling recently at SQL Bits, where I saw people hugging and shaking hands. It’s something I’ve missed at events, the closeness of friends. Most people asked others if it was OK, and for the most part it was.

Posted in Blog | Tagged , , | 2 Comments

The Summit is Back in Person

Posted on March 31, 2022 by way0utwest

Registration for the Data Community Summit opens today! You can sign up and come to Seattle in November with all the other data platform pros that you’ve missed seeing the last two years.

The event will be hybrid, but I’m going in person. I’ve missed seeing many of you, and I am looking forward to the chance to wave, shake hands, hug, and interact over coffee and cocktails in person. I’ve gone to a few conferences this year and have a few more scheduled. Each one of them is a little more normal, which I hope is a trend that continues.

If you aren’t comfortable coming in person, there is a still a hybrid option, with recordings of sessions for no-demand sessions available early. This will help with speaker Q&As, but there will also be a lot of sessions taking place live, with the ability to watch and ask questions, almost as if you were there. SQL Bits did a great job with hybrid, and I expect the Summit to be even better.

Breakfast is Back

Breakfast is back this year, which means you can wander into the convention center early, meet with friends, and make new contacts.

One of the things I’ve enjoyed is wandering around the hall and looking for friends to chat with. Or stopping at a table and introducing myself. Or just picking a spot and seeing who might come up. With a few meals a day, this is a great chance to expand your network and learn just how diverse our community can be.

Register Early

As with many events, prices rise over time. Now is the time when you can convince the boss to pay the least by registering now. The discount today is enough to cover quite a bit of the travel expenses, and every bit can help. You have until May 25 to get this opening pricing.

This is THE event for data professionals, especially those working with SQL Server, the Microsoft Data Platform, or using AWS. There are many blogs and posts across the years about the value, excitement, and knowledge gained at the event. Show a few to your boss and get registered today.

Seattle is a wonderful city, and there is a lot to do. If you want to combine a fun vacation with the trip, I’ve had some good times there with my kids before and after the event. If you’ve never spent a couple days there, this might be the time to think about it.

This is the homecoming for the data platform community and I hope that I see some of you there. If you see me, please stop my and say hi.

Posted in Blog | Tagged , , | 1 Comment

Daily Coping 31 Mar 2022

Posted on March 31, 2022 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to appreciate your hands and all the things they enable you to do.

My hands are critical. I learned to appreciate them quite a bit recently while trying to present at SQL Bits. I had injured my wrist and was wearing a brace to limit activity. When I went on stage and tried to use a mouse, I struggled because of the brace. I realized just how impactful an injury to my hands can be.

I use my hands to type for work, play guitar, coach kids, cook, and more. When I’ve been injured, I find my life impacted substantially.

I am grateful that my hands work well and have served me well across many years.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 31 Mar 2022