Press "Enter" to skip to content

Curated SQL Posts

Finding the xp_cmdshell Running User

Published 2020-04-15 by Kevin Feasel

Steve Stedman shows which user account actually runs commands when you use xp_cmdshell:

When you execute xp_cmdshell SQL Server runs a command at the operating system level similar to the old DOS prompt, or CMD shell. There are many security concerns and misconceptions about xp_cmdshell as documented in an earlier post.

The purpose of this post is not to debate whether xp_cmdshell is safe or not, but instead to show what user commands sent to xp_cmdshell are being run as.

Read on for a sample script.

Comments closed

Find and Replace Database Objects in SSMS

Published 2020-04-15 by Kevin Feasel

Daniel Hutmacher has a fun trick for us:

Here’s a quick tip that touches on one of the powerful SSMS tricks in my “Management Studio Level-Up” presentation. Say you have a potentially large number of database objects (procedures, functions, views, what have you), and you need to make a search-and-replace kind of change to all of those objects.

You could, of course, put the database in source control and use a proper IDE to replace everything, then check your code back into source control and commit it to the database. That’s obviously the grown-up solution. Thanks for reading this post.

But let’s say for the sake of argument that you haven’t put your database in version control. What’s the lazy option here?

Even if you do have everything in source control, the first part is still quite useful just in case there’s schema drift.

Comments closed

Using Trello as a Power BI Data Source

Published 2020-04-15 by Kevin Feasel

Gilbert Quevauvilliers shows how we can use Trello as an input for Power BI:

Where I am consulting, they use Trello boards which enables them to keep track of what tasks are being done, getting done and how things are progressing.

An interesting question came up asking how much work has been done. And I thought this could be done via Trello and looking at the number of tasks in the boards that have gone from To Do to Completed.

Below are the steps of how I completed this.

It’s a mashable world.

Comments closed

The Basics of tSQLt

Published 2020-04-15 by Kevin Feasel

Jess Pomfret walks us through the basics of tSQLt:

Getting started with tSQLt is really easy- you download a zip file, unzip the contents and then run the tSQLt.class.sql script against your development database.

There are a couple of requirements, including CLR integration must be enabled and your database must be set to trustworthy.  These could open up some security considerations, but for my development environment it’s no issue.

This is where I’d say putting the database in a container would be extremely helpful, as then you can destroy it afterward. I’m not sure if that’d work, as SQL Server on Linux doesn’t support unsafe or external access assemblies (and I’m not sure what tSQLt requires there).

Comments closed

Installing SSMS on Servers Running SQL Server?

Published 2020-04-15 by Kevin Feasel

Andy Mallon says yes, install SQL Server Management Studio on those servers running SQL Server instances:

“But wait, Andy. That’s not a best practice!” you say?

The pseudo best practice of “don’t install SSMS” is a misguided one–advice that I even fell into repeating in the past. However, that’s actually proposed solution to a best practice, rather than being itself a best practice.

I agree with Andy wholeheartedly on this.

Comments closed

The Importance of Unit Testing Database Code

Published 2020-04-15 by Kevin Feasel

Chris Johnson shares some thoughts on unit testing database code:

This is a topic that is quite close to me heart. I don’t come from a computing background before I started working with SQL Server, so I was quite ignorant when it came to a lot of best practices that other developers who have worked with other languages are aware of. Because of this, I had no idea about unit testing until I attended a talk at a SQL Saturday all about tSQLt. If anyone isn’t aware (as I wasn’t) tSQLt is a free open source unit testing framework for use in SQL Server databases. It is the basis of Redgate’s SQL Test software, and is the most used framework for writing unit tests in SQL Server.

Since then I’ve worked to try and get employers to adopt this as part of a standard development life cycle, with mixed success at best. My current employer is quite keen, but there are two major problems. First, we have a huge amount of legacy code that obviously has no unit tests in place; and second, the way people code is not conducive to unit testing.

Click through for additional thoughts on writing good tests and an example of modularizing code to make it more testable. I’m still in the camp of “test what you can, but you can’t test everything” with databases. There’s just too much state dependency.

Comments closed

Tidy Simulation of Stochastic Processes in R

Published 2020-04-14 by Kevin Feasel

David Robinson shows off my favorite distribution:

The Riddler puzzle describes a Poisson process, which is one of the most important stochastic processes. A Poisson process models the intuitive concept of “an event is equally likely to happen at any moment.” It’s named because the number of events occurring in a time interval of length is distributed according to , for some rate parameter (for this puzzle, the rate is described as one per day, ).

How can we simulate a Poisson process? This is an important connection between distributions. The waiting time for the next event in a Poisson process has an exponential distribution, which can be simulated with rexp().

Read on to learn about the Poisson distribution and Yule processes.

Comments closed

Changing the Graphics Device in RMarkdown Docs

Published 2020-04-14 by Kevin Feasel

Colin Gillespie shows us how to change PDF and PNG output settings within knitr:

In many workflows, function calls to graphic devices are not explicit. Instead, the call is made by another package, such as knitr.

When kniting an Rmarkdown document, the default graphics device when creating PDF documents is grDevices::pdf() and for HTML documents it’s grDevices::png(). As we demostrated, these are the worst possible choices!

Click through to see what you can do about it.

Comments closed

Clarifying Nomenclature around Azure Synapse Analytics

Published 2020-04-14 by Kevin Feasel

James Serra clears a few things up:

I see a lot of confusion among many people on what features are available today in Azure Synapse Analytics (formally called Azure SQL Data Warehouse) and what features are coming in the future. Below is a picture (click to zoom) that I describe below that hopefully clears things up:

I tend to just say “Azure Synapse Analytics SQL Pools” for the product formerly known as Azure SQL Data Warehouse and save “Azure Synapse Analytics” to include Spark + hyperscale (James’s v3).

Comments closed