Running processes including job step name

The normal output of sys.dm_exec_sessions (or sysprocesses) will tell you that a job is running, but it will hide the name of the job behind a heximal representation of the job_id, not the job_id as displayed in sysjobs. This script will match the job name and job stepwith the process.

Posted in T-SQL | 2 Comments

Get-AzDataFactoryPipeline: a misleading error

A few weeks back, I wanted to start an Azure Data Factory pipeline using PowerShell. It was the first time I tried that, and I thought it would be pretty simple: get the correct pipeline using Get-AzDataFactoryPipeline, supply all the parameters and pipe it to Invoke-AzDataFactoryPipeline. Something like:

That, however, did not work. I got an error message stating that the resource was not found (HTTP Status Code NotFound, Error Code ResourceNotFound):

After double checking the commands and all parameters and for syntax errors and typos, I turned to the internet for help, but searching online didn’t provide an answer either. However, it turned out that the answer was pretty easy: for ADF version 2, you need to use the AzDataFactory V2 commands:

Posted in Azure Data Factory, Powershell | Leave a comment

Code samples for my book on Querying data with Transact-SQL (MCSA 70-761), chapter 3

Posted in Code samples, MCSA SQL 2016 70-761 | Leave a comment

Slow delete with immediate output

Posted in T-SQL | Leave a comment

How I passed the exam AZ-900 Microsoft Azure Fundamentals

This week, I passed my first Azure exam: AZ-900 Microsoft Azure Fundamentals. As the name suggests, this is the most basic Azure exam, and therefore a nice place to start if you want to get certified in one of the hottest topics in IT at the moment. This exam covers the basics of cloud computing, such as what is different between the cloud and on-premises environments, and how to get started in the cloud. You can find a complete description of the exam topics on the Microsoft web site.

If you pass this exam, you will be awarded a certification, the Microsoft Certified Azure Fundamentals.

Microsoft is also kind enough to provide study material for free. On the Microsoft Learn portal, you can find a complete course for this exam (here). This course should take you a little over nine hours to complete. I had never tried Microsoft Learn before, but I found it quite enjoyable, and easy to follow along. It contains text, video, labs and practice questions. There are also some links to related material.

So probably, you are wondering if this free training is enough to pass the exam. In my experience, it is. Personally, I did not have a lot of hands-on experience working with Azure. I took an Azure boot camp course some six years ago, but in the cloud, things change pretty fast, so practically everything is different from six years ago. Since that boot camp, I’ve not worked with Azure directly, but some of my clients are moving (or planning to move) databases to Azure, so I have been watching these migrations from the sideline.

As far as the level of the course is concerned: it is not that difficult, exactly what you would expect for a fundamental course. If you are new to Azure, but not new to IT, you should be able to follow along quite nicely. If concepts like virtual machine, high availability and firewalls are completely new for you, you will probably require a bit more studying.

After following the course once, I scheduled the exam. I did plan to redo the course once more before actually taking the exam, and just focus on the topics that were new to me, and the topics that were more likely to be asked on the exam. If you’ve ever done a Microsoft exam, you will recognize those topics while studying. In most exams, there are bound to be questions that focus more on lists of options and factoids than on understanding of the material. However, life intervened, and I did not have time to completely go through the course a second time.

Regardless, I passed the exam easily. I will not give any details on the exam itself, because I don’t want to violate the non-disclosure agreement. But if you are interested in taking this exam, I can tell you that the Microsoft Learn course adequately prepares you for this exam. Good luck studying!

Posted in Uncategorized | Leave a comment

Dynamic PIVOT statement

My attempt to make a dynamic PIVOT statement. The table used is the same as the example in my 70-761 book.

First, the @list_of_columns parameter is filled to create a list of columns, using COALESCE. Next a common table expression is used. In this example. it is not useful, but as more complicated PIVOT statements will usually work with tables with more than the required 3 columns, I left this cte in there.

Add a second @list_of_columns parameter to replace the NULL values in the result set.





Posted in Uncategorized | Leave a comment

Convert MSDB job history to datetime

This script will get the job history for a particular job. The start time will be converted from integer to datetime. Works for job duration under 1000 hours.

Since I’ve still got clients on SQL 2008, I can’t use the DATETIMEFROMPARTS function, which makes the code a little longer than on newer SQL versions.



Posted in Uncategorized | Leave a comment

Bonus questions for MCSA 70-761

The practice questions have been moved to a free course on my Teachable site:

Posted in Books, MCSA SQL 2016 70-761 | Leave a comment

Code samples for my book on Querying data with Transact-SQL (MCSA 70-761), chapter 2