Press "Enter" to skip to content

Month: June 2017

Finding Failed Queries

Published 2017-06-08 by Kevin Feasel

Andrew Pruski shows how to use extended events to find queries with errors:

What this is going to do is create an extended event that will automatically startup when the SQL instance starts and capture all errors recorded that have a severity level greater than 10.

Full documentation on severity levels can be found here but levels 1 through 10 are really just information and you don’t need to worry about them.

I’ve also added in some extra information in the ACTION section (for bits and bobs that aren’t automatically included) and have set the maximum number of files that can be generated to 10, each with a max size of 5MB.

Check it out.  At one point, I had created a small WPF application to show me errors that extended events caught.  It completely freaked out a developer when I IM’d him and told him how to fix the query he’d just run from the privacy of his cube, with me nowhere to be seen.

Comments closed

Creating Nicer Reports

Published 2017-06-08 by Kevin Feasel

Reid Havens has a few tips for making Power BI reports look nicer:

This is less of a single applied step as it is multiple formatting practices applied throughout the report. I’ve already hit on this subject a little bit in the two previous Power BI visual design practices in regards to using complimentary colors. The two key takeaways in this section are object formatting and color coordination.

Of all my best practices I’m showcasing here I’d say this one is the most subjective. However I think that maintaining complimentary colors goes a long ways to creating a professional looking report. I also have a strong dislike for the default title design for visualizations in Power BI. By default it is left aligned and a grey color (AGAIN…hard to read!). I center that sucker and color the background. An added benefit to coloring the title background is it actually forces me to make sure my objects are aligned, otherwise it is VERY noticeable now if they aren’t.

Definitely read the comments on this one, as some of these tips are subjective.

Comments closed

The REVERSE Function

Published 2017-06-08 by Kevin Feasel

John Morehouse explains what the REVERSE function is and why it can be useful:

Recently I had a conversation with a colleague that wasn’t aware of the REVERSE function in SQL Server.  I had sent them a code snippet and they inquired as to what the purpose of the function was.  Essentially, this function that reverses any string value.

For example, the phrase:

The brown dog jumped over the lazy fox

reversed looks like this

xof yzal eht revo depmuj god nworb ehT

Read on to see a good example of when you might use REVERSE.

Comments closed

Mapping File Shenanigans With The Import/Export Wizard

Published 2017-06-08 by Kevin Feasel

Angela Henry ran into problems copying a boatload of data from a mainframe-hosted DB2 server and has lived to tell the tale:

This post talks about the issue I ran into with SSIS Mapping Files.

We currently run DB2 on an IBM iSeries AS400 for our ERP system.  I was tasked with copying data from the AS400 to a SQL Server database for some consultants to use.  The C-Suite didn’t want to give the consultants access to our AS400, so this was the work around that was put forth and accepted (and no, no one asked me before I was “voluntold” for the task).  Since this would essentially be a “one-time” thing, I chose to use the Import Export Wizard, but I would save the package just in case they wanted this process repeated.

Sounds like it was a painful experience, but it does have a happy ending.

Comments closed

C Complier Errors With Memory-Optimized Objects

Published 2017-06-08 by Kevin Feasel

Arun Sirpal ran into an issue with memory-optimized tables:

Naturally (whether right or wrong) I thought that it could be corruption and I didn’t want to rule it out. I headed straight to the error log to see if it could guide me, all this did was confuse me. However after some research it led me to conclude that “Unable to call into the C compiler GetLastError = 2” for the specific error code shown below meant that a specific file(s) could not be found, I was thinking exactly what file(s)?

This is a snippet from the error log when the database went through recovery – Seems to even struggle during the analysis phase and the checkpoint failure was throwing me off slightly.

It’s worth reading in case you experience this error.

Comments closed

Riddler Nation: Game Theory In Action

Published 2017-06-07 by Kevin Feasel

Curtis Miller goes over a multi-phase distribution game with no known information:

The winning strategy of the last round, submitted by Vince Vatter, was (0, 1, 2, 16, 21, 3, 2, 1, 32, 22), with an official record1 of 751 wins, 175 losses, and 5 ties. Naturally, the top-performing strategies look similar. This should not be surprising; winning strategies exploit common vulnerabilities among submissions.

I’ve downloaded the submitted strategies for the second round (I already have the first round’s strategies). Lets load them in and start analyzing them.

This is a great blog post, which looks at using evolutionary algorithms to evolve a winning strategy.

Comments closed

Data Wrangling With Power Query

Published 2017-06-07 by Kevin Feasel

Eugene Meidinger parses a complex report using Power Query:

Hmm, so it looks like I made a mistake. I hope my honesty won’t lose me any izzat, or ability to command respect. I think it’s important to see how people really learn and really solve problems. So, I’m including my screw ups in this post.

Apparently, I created a linked table and I can’t see how to edit the the Power Query portion for that. A linked table is a nice way to pull raw data from the Excel workbook. It’s great for reference tables, but doesn’t solve our problem.

Come for the data analysis, stay for the spelling bee.  This is part one of a two-parter, focusing on techniques to get the data in a digestible format; part two will do interesting things with the data.

Comments closed

The Multifaceted Nature Of R

Published 2017-06-07 by Kevin Feasel

John Mount points out that there are many ways to skin a cat in R:

Python has a fairly famous design principle (from “PEP 20 — The Zen of Python”):

There should be one– and preferably only one –obvious way to do it.

Frankly in R (especially once you add many packages) there is usually more than one way. As an example we will talk about the common Rfunctions: str(), head(), and the tibble package‘s glimpse().

This is a small example of a large phenomenon.

Comments closed

Production-Quality Powershell Functions

Published 2017-06-07 by Kevin Feasel

Missy Januszko has some tips on turning those Powershell scripts into reusable functions:

Breaking down your code may mean chopping apart your lengthy script into smaller pieces. As a best practice, a function should do only one thing. A retrieval cmdlet retrieves information and sends that information to the pipeline. Conversely, a functional cmdlet performs an act but not a retrieval act. It may take input from another cmdlet and act upon that input. It may or may not send output information to the pipeline. Lastly, output cmdlets format output in a desired display. As a result, this will allow us to use the pipeline more effectively to pass parameters between functions. In the above example, most of the function is a retrieval function. The exception is that it formats the output into a table with the last line. I will remove that line and let the user of the function decide how they want it formatted.

As a friendly warning to operations folks who are using more and more Powershell, when you do it right, you end up being a developer.  But we can keep that a secret, just between you and me.

Comments closed

JSON Data Sources In SSIS

Published 2017-06-07 by Kevin Feasel

Chris Koester shows how to read JSON data sources in SQL Server Integration Services:

Once the Script Component has been defined as a source, the output columns can be defined. For this post, the same USGS Earthquake data that was used in the “Download JSON data with PowerShell” post will serve as an example. Be careful to choose the correct data types here. This can be tedious because you have to choose the correct data types in the C# code as well, and ensure that they correspond with the SSIS types. It’s helpful to bookmark a SSIS data type translation table for reference.

It does involve creating a script component, but aside from the tedium that Chris mentions, it’s not too bad.

1 Comment