Press "Enter" to skip to content

Curated SQL Posts

Combining SendTo and Powershell

Published 2021-03-02 by Kevin Feasel

Mark Wilkinson shares a script with us:

If you are not familiar, SendTo options are those available when you right click on a file/folder in file explorer and select the Send To option in the menu. When you use this option, the currently selected files/folders are passed to the SendTo shortcut as a space delimited list of files and folders. This is important to know so you better understand what needs to be done to read that list.

I can confirm that this works well for deploying script out, especially when they need to go to multiple servers or multiple databases on servers. That functionality takes a bit more effort to write, but combine Mark’s code with Jess’s and you are well on your way.

Comments closed

Azure Charts

Published 2021-03-02 by Kevin Feasel

This is an interesting site I just learned about this morning:

Project mission: Communicating Azure’s current state, structure and updates in a compact digestible way.

Concept: Cloud Charts Manifesto

Data sources: Public updates, RSS channels and web pages are used as primary data sources.


While developed by a Microsoft employee, Azure Charts is not a Microsoft service or product. This is a personal project, there are no implicit or explicit obligations related to it. If you want to receive updates about Azure Charts, feel free to follow or connect on LinkedIn.

It’s out of the norm for me to talk about a whole site like this, but I think it’s a really useful way of showing off these services in a non-confusing manner. It also gives some en passant insight on the relative interest in specific services based on industry.

If you do think this is interesting, please fill out a small form to make sure it stays public.

Comments closed

Executing a Folder of SQL Scripts against SQL Server

Published 2021-03-02 by Kevin Feasel

Jess Pomfret has a quick Powershell snippet for us:

Another week and another useful dbatools snippet for you today.  Last week at work I was given a folder of 1,500 scripts – each containing a create table statement. Can you imagine having to open each file in Management Studio to be able to execute it? Thank goodness we have PowerShell and dbatools on our side.

Click through for the command, as well as Jess’s explanation of how it works.

1 Comment

Power BI Connector for Databricks

Published 2021-03-01 by Kevin Feasel

Stefania Leone, et al, announce general availability of the Power BI connector for Databricks:

We are excited to announce General Availability (GA) of the Microsoft Power BI connector for Databricks for Power BI Service and Power BI Desktop 2.85.681.0. Following the public preview, we have already seen strong customer adoption, so we are pleased to extend these capabilities to our entire customer base. The native Power BI connector for Databricks in combination with the recently launched SQL Analytics service provides Databricks customers with a first-class experience for performing BI workloads directly on their Delta Lake. SQL Analytics allows customers to operate a multi-cloud lakehouse architecture that provides data warehousing performance at data lake economics for up to 4x better price/performance than traditional cloud data warehouses.

This is easier to work with than the Apache Spark connector and it looks like it should be faster than that connector as well.

Comments closed

Eight Fallacies of Distributed Computing

Published 2021-03-01 by Kevin Feasel

Kevin Sookocheff dives into things people tend to forget when building distributed computing solutions:

If we continue to develop microservices using the same set of assumptions we used for a monolith, we are operating with a now false set of assumptions that can prevent us from being successful. Even in a small distributed system with just two microservices we need to deal with networked communication that can turn our usual mental model of application development on its head. A common set of rules that can help us update our mental model to be more accurate in a distributed environment is the Eight Fallacies of Distributed Computing commonly attributed to Peter Deutsch, an engineer at Sun microsystems who worked on early versions of Ghostscript, as well as interpreters for Smalltalk and Lisp.

Click through for more information on each of the eight fallacies and what you can do to avoid their resulting pitfalls.

Comments closed

Executing sp_help on Temp Tables

Published 2021-03-01 by Kevin Feasel

William Assaf shows how you can use sp_help to learn about temp tables:

For example, I was trying to convert a query from using a #temp table to a CTE instead, and wanted to see the column list and resulting data types of the #temp table. 

Sp_help is a helpful SQL Server system sproc to return schema of objects. It’s that magic that happens when you press Alt+F1 in SSMS. (Side note: showing someone the Alt+F1 shortcut in SSMS for the first time and seeing their life change for the better is really rewarding.) 

But Alt+F1 doesn’t work on #temp tables[…]

Read on for an alternative which does work.

Comments closed

TLS 1.2 Support in MDAC and SQLOLEDB

Published 2021-03-01 by Kevin Feasel

Ron the Polymath notes a change slipped into Windows:

The October 2020 Preview releases of Windows builds 1809 (KB4580390), 1903/1909 (KB4580386), and 2004/20H2 (KB4580364) include the following change:

– Adds support for the Transport Layer Security (TLS) 1.1 and 1.2 protocols when connecting to SQL Server using the data providers in Microsoft Data Access Components (MDAC).

Click through for Ron’s experience and another update of potential interest.

Comments closed

Week-Over-Week Comparisons with Power Query

Published 2021-03-01 by Kevin Feasel

Gilbert Quevauvilliers knows that time is a flat circle:

I have seen in the past Week-on-Week comparisons but one of the challenges is what happens when it overlaps years. Especially at the start of a year like it is now Feb 2021, the users want to compare week-on-week for the past 3 months.

My challenge was to find a way to have a continuous week number over multiple years.

Click through to learn how.

Comments closed

Common Admin Scripts for Power BI

Published 2021-03-01 by Kevin Feasel

Brent Powell has a new series for us:

Between the Power BI PowerShell modules and the Power BI REST APIs administrators have a rich set of tools to efficiently administer Power BI environments. Custom administrative and monitoring solutions based on these technologies have been featured on this blog before but today we will start the first of a two-part series highlighting simple script examples that Power BI administrators can use to address common scenarios.

The six PowerShell script files (.ps1) for today’s examples are available in my GitHub repo.

Check out those scripts, as well as Brent’s walkthrough of each.

Comments closed