Press "Enter" to skip to content

Curated SQL Posts

Scripting Maintenance Mode Tasks

Jamie Wick shares some hard-earned knowledge regarding scripting out maintenance tasks using Powershell:

Given that we have several hundred servers (and growing), this process is taking an increasing amount of time each month. Over the years we’ve implemented various automated patching systems (WSUS, IBM BigFix, etc.) and they’ve worked reasonably well for managing the Download & Install step. The pain point lately has become the first two steps (snapshots and maintenance mode). Both processes are simple to complete using the VCenter web-based user interface and SCOM console. The problem is the volume of button clicks it takes to complete the process for ALL of the servers. Using the standard (web) user interfaces, over an hour of the monthly maintenance window can be lost to just getting the snapshots and maintenance mode tasks completed. Extrapolate that out over a year and we’re looking at over 1.5 DAYS of work-time lost to getting the servers ready to START applying updates. That’s not a statistic we want to publish to senior management. So, how to fix (or minimize) the problem? The answer to which is: Script It.

Let’s take a look at how to use PowerShell to automate the snapshot and maintenance mode tasks.

Read on for sample scripts.

Comments closed

Resetting SSMS Window Layout

Wayne Sheffield clues us in on a nice Management Studio feature:

SSMS is a wonderful tool. You can drag Windows around, grouped with others, split, docked, undocked, hidden… it seems endless what you can do with them. You can even change what columns you see. Invariably, with all of this customization, things go wonky. I’ve even seen windows opened up on invisible monitors. Sometimes you can’t find the windows that you need. Conversely, windows that you don’t need are open all over the place. Sometimes, you just need to reset everything and start over. Short of reinstalling SSMS, how do you do this? You just reset the window layout.

It’s one of those options which stares you in the face but you can easily miss.

Comments closed

Using FlashText Instead Of RegEx

Leona Zhang compares the FlashText Python library to using regular expressions:

If you have done any text/data analysis, you might already be familiar with Regular Expressions (RegEx). RegEx evolved as a necessary tool for text editing. If you are still using RegEx to deal with text processing, then you may have some problems to deal with. Why? When it comes to large-sized texts, the low efficiency of RegEx can make data analysis unacceptably slow.

In this article, we will discuss how you can use FlashText, a Python library that is 100 times faster than RegEx to perform data analysis.

Learn more on the GitHub repo.  I haven’t used this before but I could see it being handy.

Comments closed

Neural Networks Are Polynomial Regression

Norman Matloff announces a new paper:

A summary of the paper is:

  • We present a very simple, informal mathematical argument that neural networks (NNs) are in essence polynomial regression (PR). We refer to this as NNAEPR.

  • NNAEPR implies that we can use our knowledge of the “old-fashioned” method of PR to gain insight into how NNs — widely viewed somewhat warily as a “black box” — work inside.

  • One such insight is that the outputs of an NN layer will be prone to multicollinearity, with the problem becoming worse with each successive layer. This in turn may explain why convergence issues often develop in NNs. It also suggests that NN users tend to use overly large networks.

  • NNAEPR suggests that one may abandon using NNs altogether, and simply use PR instead.

  • We investigated this on a wide variety of datasets, and found that in every case PR did as well as, and often better than, NNs.

  • We have developed a feature-rich R package, polyreg, to facilitate using PR in multivariate settings.

The paper and presentation slides are ungated, so check it out.  H/T R-bloggers

Comments closed

Strings And Identifiers

Kenneth Fisher explains the difference between a string and an identifier:

A common mistake, and one I make frequently myself is to use a string in place of an identifier, or vise-versa. So to start, let’s have some definitions, shall we?

String

a linear sequence of characters, words, or other data.

Identifier

a sequence of characters used to identify or refer to a program or an element, such as a variable or a set of data, within it.

And because I always find examples fairly useful.

Click through for the example as well as additional explanation.

Comments closed

Power BI Helper Version 2.0 Released

Reza Rad has a new version of Power BI Helper:

I started Power BI Helper with the intention to help to find issues in Power BI reports faster and easier. This tool over time became better and better. I’m excited now to let you know that the version 2.0 of this product is now available for everyone to use and enjoy. This version comes with these features:

  • Connecting to more than one Power BI model. Selection option for the model.

  • Showing the connection mode of the Power BI file.

  • Showing list of tables that are NOT used in any visualization, and can be hidden from the report.

  • Modeling advises

    • List of both directional relationships
    • List of inactive relationships
  • Some minor bug fixes

It looks like quite the useful tool.

Comments closed

Replicating Extra-Long Strings

Monica Rathbun walks us through a replication error:

Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not.

Error Description: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit

We ran into an issue with a customer this week, this error was flooding the error log. After a little digging I found it had to do with transactional replication (also applies to Change Data Capture) they had setup which included LOB data.

Read on to see what you can do to resolve this error.  Also, check out the comments and be glad you’re not in that boat…unless you are, in which case…

Comments closed

DATENAME In SQL Server

Randolph West continues his dates and times series with a new function, DATENAME():

There are many similarities between DATEPART and DATENAME. Where DATEPART returns the date or time part as an integer, DATENAME returns the part as a character string.

This DATENAME function also takes two parameters: the date or time part we want back, and the input date. Just as we saw with DATEPART, the documentation indicates the input date parameter must be an “expression that can resolve to one of the following data types: datesmalldatetimedatetimedatetime2datetimeoffset, or time.”

Similarly, the date and time parts that can be returned look much like those in DATEPART, which gives us another opportunity for the reminder that we should avoid using the available abbreviations in order to help with writing clearly understandable code.

DATENAME is a useful function for displaying parts of dates & times, but Randolph does lay out the caveats.

Comments closed

SQL Operations Studio, June Release

Alan Yu announces the June release of SQL Operations Studio:

The June public preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top GitHub issues.

Highlights for this build include the following.

  • SQL Server Profiler for SQL Operations Studio Preview extension initial release

  • Azure SQL Data Warehouse extension

  • Edit Data Filtering and Sorting

  • SQL Server Agent for SQL Operations Studio Preview extension enhancements for Jobs and Job History views

  • Build your own SQL Ops Studio extension

  • Visual Studio Code Refresh

  • Fix GitHub Issues

I saw “SQL Server Profiler” and started wondering what was going on, until Alan explained that it’s actually the lightweight Extended Events profiler and not the heavyweight beast we know and love and/or hate.

Comments closed