Press "Enter" to skip to content

Author: Kevin Feasel

Dropping Database Objects with Aplomb

Published 2019-04-23 by Kevin Feasel

Pamela Mooney has a two-part series on dropping database objects. Part one includes a big setup script:

Some months ago, a fellow DBA came to me and expressed her concern over the normal housecleaning process that occurred at her company.  Developers or product owners would submit changes for objects which were no longer used, only to find that sometimes, they were.  No damage had been done, but the possibility of creating an emergent situation was there.

I could well understand her concern.  Most of us who have been DBAs for any length of time have probably come across a similar scenario.  I thought it would be fun to write a process that reduced the risk of dropping database objects and made rollbacks a snap.

Part 2 handles the actual drops:

Now, the objects in the table will be dropped after 120 days.  But what if you need them to be dropped before (or after)?  Both options work, but I’ll show you the before, and also what happens if there is a problem with the drop.

Check it out and drop with impunity. Or at least the bare minimum of punity.

Comments closed

Importing a Private Key From VARBINARY

Published 2019-04-23 by Kevin Feasel

Solomon Rutzky tries out various methods of loading certificates and private keys in SQL Server:

These results confirm that:
1. You can import a certificate from a VARBINARY literal
2. You can import a private key when creating a certificate from a VARBINARY literal
3. You cannot import a private key when creating a certificate from an assembly
4. Except when creating a certificate from an assembly, any combination of sources for the certificate (i.e. public key and meta-data) and the private key should be valid

It’s a long post with a lot of detail and quite a few tests, so check it out.

Comments closed

SARGability and Date Functions

Published 2019-04-23 by Kevin Feasel

Erik Darling shows why you don’t want to use YEAR() or MONTH() in the WHERE clause when querying a large table:

If you’ve been query tuning for a while, you probably know about SARGability, and that wrapping columns in functions is generally a bad idea.

But just like there are slightly different rules for CAST and CONVERT with dates, the repercussions of the function also vary.

The examples I’m going to look at are for YEAR() and MONTH().

Read the whole thing. Maybe “go to brunch” in the middle of it for maximum effect.

Comments closed

Data Breach Causes

Published 2019-04-23 by Kevin Feasel

Grant Fritchey takes us through some of the immediate causes of data breaches:

Fine, let’s talk about a business then. How about 24 million loan records, including bank account information, email, phones, social security numbers and all the rest. Yeah, that was sitting on an Elasticsearch database with no password of any kind. Oh, and the S3 storage was completely open too. Security? Is that still a thing?

How about exposing your entire client list because you left the password off the database (Elasticsearch again, is it hard to add a password to Elasticsearch). How about stacks of resumes (ElasticSearch, again, and MongoDB).

Those are just breaches from this year. If we go back, we can find more and more. Please, put a password on your systems. 

The OWASP Top 10 application security risks is out there and provides a lot of useful information on how to prevent the problems Grant mentions.

Comments closed

Modifying HTML Rendering in Shiny

Published 2019-04-22 by Kevin Feasel

Senthil Thyagarajan gives us an example of uisng the htmltools package to change the way tables render in Shiny:

In order to build the html table I have used a function table_frame which can be used as a container in DT::renderdatatable. This function basically uses htmltools. For more references on the basics of html tables please refer here

In addition to changing the colors, Senthil also shows how to add a couple of buttons which call Javascript functions. H/T R-bloggers

Comments closed

SSMS Grid Options

Published 2019-04-22 by Kevin Feasel

Michelle Haarhues provides details on the different options for sending results to a grid in SQL Server Management Studio:

What is Results to Grid and what can it do for you?  Results to Grid are Query Results options in SQL Server Management Studio (SSMS) that can help users customize their query results in a variety of ways that can help make users more efficient.  Some of these might be little changes, but when used often throughout the day, they can make a big difference.  Once you change the setting, you will need to open a new query window for the change to go into effect

The two I wish were on by default are column headers when copying or saving results, and retaining CR/LF on copy or save.

Comments closed

Power BI Report Builder Map Gallery Customization

Published 2019-04-22 by Kevin Feasel

David Eldersveld shows off how you can customize maps in the Power BI Report Builder Map Gallery:

One of the defining features of Power BI’s [paginated] Report Builder vs the current geospatial offerings in Power BI Desktop is the native support for ESRI Shapefiles. If you have worked with maps in Report Builder over the years, you may have used the Map Gallery. The Map Gallery offers a collection of built-in geographies, but you can also customize and enhance what’s available out of the box.

David takes us through an example of simplifying one map, but the same technique can help in other ways.

Comments closed

Multi-Pattern Replacement with SQL Server

Published 2019-04-22 by Kevin Feasel

Hugo Kornelis has a pattern matching problem to solve:

The actual use case and the list of patterns that I had to remove are considered a confidential competitive advantage by my client, so I will just make up a list here to illustrate the problem. In this fake requirement, the following patterns must all be removed if anywhere in the string: “-@@%”, “@@%”, “-@%”, “@%”, “No.X#X”, and “^^^”. Also, “@X” needs to be removed when at the end of the string. In these patterns, @ is a placeholder for a numeric symbol, X for an alphabetic symbol, and # for an alphabetic or numeric symbol. All other symbols are to be taken literally. (So “%” is the percent mark, not the LIKE pattern for any character!).

This is a problem for regular expressions, but without built-in regular expressions (and I’d guess no desire to use the CLR), Hugo gives us a workable solution.

Comments closed

Connecting with Read Intent

Published 2019-04-22 by Kevin Feasel

John McCormack shows two ways to connect to an Availablity Group listener with read-only intent:

SQLCMD
The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.

There’s some good information in here for sqlcmd and for SQL Server Management Studio.

Comments closed

Query Editing with Azure SQL Database

Published 2019-04-22 by Kevin Feasel

Dave Bland shows off the Azure SQL Database query editor built into the Azure portal:

We have all been using SQL Server Management Studio to query and manipulate data, even for an Azure SQL database.  There is also an option to do this same thing built into the SQL Azure database interface in the Azure portal.  Although there have been a number of posts related to this topic dating back a few years, this feature is still marked as “preview” in the Azure portal.

Click through to see how it works, what you can do with it, and some of its limitations.

Comments closed