Press "Enter" to skip to content

Month: March 2019

Bad Idea Files: Cross-Server Temp Table Access

Published 2019-03-19 by Kevin Feasel

Kenneth Fisher explains how to shoot yourself in the foot:

So a few things here. I’m using a global temp table instead of a local one because it makes it easier to reference. Local temp tables aren’t listed in tempdb under their name while global ones are.

The first part of this message (the bit in black) is a warning basically telling us that if there is a temp table (a # at the front of the name) it’s going to ignore the multi part reference. In other words, you’re going to get this message any time you try to use a multi part name and a temp table. Linked server or not. The second part of the message (the bit in red) just tells us that there isn’t a temp table named ##DBList.

Kenneth finds a way, but I can’t think of a scenario where accessing a temp table on a different instance turned out to be a good idea.

Comments closed

Desired State Configuration: Managed Object Format Files

Published 2019-03-19 by Kevin Feasel

Jess Pomfret explains what Managed Object Format (MOF) files are and why they’re useful for Desired State Configuration:

When I run this script I see the output in the screenshot below, a MOF file has been created in my output folder. Managed Object Format (MOF) files are used to describe Common Information Model (CIM) classes, these are industry standards which gives us flexibility in working with DSC. In DSC this is important as the MOF file is the artefact that will actually be used to configure our nodes. This MOF will be delivered to our target node and enacted by the Local Configuration Manager (LCM).

The LCM will be covered in more detail in a later post, but for now know that it can be configured to be in either ‘Push’ mode or ‘Pull’ mode.  Pull mode is more complicated to set up but perhaps more appropriate for managing a large number of servers.  For now, we will look at the ‘Push’ mode where we will deliver the MOF manually to the target node for the LCM to enact.

There are a lot of TLAs to watch out for within Desired State Configuration.

Comments closed

Against Hard-Coded Database Names In Queries

Published 2019-03-19 by Kevin Feasel

Kendra Little explains why hard-coding database names in your stored procedures or views is a bad idea:

I’m terrible at naming things. I recently wrote some quick code to reproduce a design problem and demonstrate several options for solutions, and later realized that I’d named my objects dbo.Foo, dbo.FooFoo, and dbo.Wat.

But I feel strongly about a few important principles about referring to objects that are already named in a relational database, specifically SQL Server and Azure SQL Database.

Most of the time, you should use a two part-name for objects in the current database. It’s important to know your database context. When referencing an object in the current database, you should not specify the database name in the reference.

Read the comments as well. I’m not as hard-set against three-part naming for cross-database queries but can understand the sentiment.

Comments closed

Dependencies as Risks

Published 2019-03-18 by Kevin Feasel

John Mount makes the point that packages dependencies are innately a risk:

If your software or research depends on many complex and changing packages, you have no way to establish your work is correct. This is because to establish the correctness of your work, you would need to also establish the correctness of all of the dependencies. This is worse than having non-reproducible research, as your work may have in fact been wrong even the first time.

Low dependencies and low complexity dependencies can also be wrong, but in this case there at least exists the possibility of checking things or running down and fixing issues.

There are some insightful comments on this post as well, so check those out. This is definitely an area where there are trade-offs, so trying to reason through when to move in which direction is important.

Comments closed

Custom ggplot2 Fonts

Published 2019-03-18 by Kevin Feasel

Daniel Oehm shares two techniques for using custom fonts in your ggplot2 visuals:

ggplot – You can spot one from a mile away, which is great! And when you do it’s a silent fist bump. But sometimes you want more than the standard theme.

Fonts can breathe new life into your plots, helping to match the theme of your presentation, poster or report. This is always a second thought for me and need to work out how to do it again, hence the post.

There are two main packages for managing fonts – extrafont, and showtext.

Read on to see how to use each of these packages. H/T R-bloggers

Comments closed

Sending Highlighted Code From VS Code Via SSH

Published 2019-03-18 by Kevin Feasel

Anthony Nocentino shows how you can use Visual Studio Code to highlight and then send code via SSH to a remote machine:

You can create a custom keyboard shortcut in VS Code (And Azure Data Studio too) that gives you this functionality. Highlight code, press a button and execute that code in the active terminal, which just so happens to be SSH’d into a remote host.

Head over to Preferences->Keyboard Shortcuts (Picture 1) and in there you’ll find a shortcut called “Terminal: Run Selected Text In Active Terminal” (Picture 2). This is exactly what I want. Now, when I’m presenting…I can highlight the code…and what I highlighted gets copied into the terminal below and executed on whatever system is active in the terminal below. This could be either my local computer or a remote system over SSH.

Anthony’s use case is specifically around presentations but it could also be good for general use.

Comments closed

SSIS on Windows Containers

Published 2019-03-18 by Kevin Feasel

Andy Leonard is a man who doesn’t like to take “no” for an answer:

Seriously, since I hopped the fence from developer to data I’ve dreamed of the day when I could practice lifecycle management with data-stuff like I used to practice lifecycle management with software development.
I recognize the obstacles. The greatest obstacle (in my humble opinion) is software is mostly stateless these days (these days started with Object-Oriented Programming and include its descendants).

Stateless development solves lots of engineering problems in lifecycle management, and by “solves a lot of engineering problems” I mean some engineering problems simply don’t exist so lifecycle management for stateless stuff can simply ignore a “lot of engineering problems.”

A database, on the other hand, is all about that state. When it comes to managing lifecycle for a stateful platform – like a database – ACID gets dumped on many lifecycle management tools and solutions (see what I did there?).

Is it possible to manage a data-related lifecycle using stateless tools? Yes. But here there be obstacles. Let’s look at on use case:

Click through for more thoughts and setup for a new series.

Comments closed

Retaining a Few Tables From a Large Set

Published 2019-03-18 by Kevin Feasel

Jana Sattainathan has a Powershell-based solution to eliminate all but a few tables in a database:

Recently, I received a request to backup a dozen tables or so tables out of 12 thousand tables. I had to retain all the indexes, statistics etc. The goal was to hand this over to the vendor for analysis as a database backup.

I could have copied the selected tables over to a new database using the PowerShell function I had published earlier and backed that up but since the tables to backup were quite large, I skipped that route

Read on to see Jana’s solution.

Comments closed