Press "Enter" to skip to content

Month: August 2018

Confirmation In Powershell

Published 2018-08-29 by Kevin Feasel

Shane O’Neill shows off how easy it is to add confirmation checkpoints into Powershell code:

Finally it works… BUT…

  • It’s 26 lines long for this piece of code. If we have multiple then this is going to blow up size wise, and
  • There’s definitely more but I think I’ve gotten the point across (or I hope I have).

So let’s try and use what’s built in to PowerShell.

The built-in version is 3 lines of code and provides more functionality.  You probably want to use that version; click through to see this all in action.

Comments closed

Finding The First Non-NULL Value In A Window

Published 2018-08-29 by Kevin Feasel

Bert Wagner shows off the FIRST_VALUE window function and walks us through a case it struggles with:

The SQL Server FIRST_VALUE function makes it easy to return the “first value in an ordered set of values.”

The problem is that if that first value happens to be a NULL, there is no easy, built-in way to skip it.

While a UserVoice item exists to add the ability to ignore nulls (go vote!), today, we’re going accomplish that end result with some alternative queries.

Click through for the demo, as well as a video version of the post.

Comments closed

Writing C# Extension Methods In Biml

Published 2018-08-29 by Kevin Feasel

Cathrine Wilhelmsen shows us how to avoid repeating our C# code using extensions:

In a previous blog post, we looked at how to use C#/VB Code Files in Biml. There are several benefits to moving custom C# code into separate files. It allows you to reuse that code across multiple projects and solutions. You can maintain the code in your editor of choice, taking advantage of intellisense and syntax highlighting. And finally, my personal favorite: you can create custom extension methods.

In this post, we will look at how to simplify our Biml projects by creating and using C# extension methods. We will build on the examples from the previous C#/VB Code Files in Biml blog post.

*pushes up glasses* You know, this would be even easier in F# and wouldn’t need extension methods.

Joking-not-joking aside, read the whole thing.

Comments closed

Catalog Your SQL Servers: The SQL Undercover Catalogue

Published 2018-08-29 by Kevin Feasel

David Fowler announces the SQL Undercover Catalogue:

Where Can I Get the Catalogue From?

The Undercover Catalogue is available from our GitHub site.

What Does the Catalogue Store?

The Undercover Catalogue stores all manner of useful information on your SQL Servers,

  • Instances –
  • Databases
  • Logins
  • Users and permissions
  • Agent Jobs

with many more modules planned in future releases.

Check it out and I’m sure they’d love feedback.  Also, read on for where this toolkit is going.

Comments closed

The Gartner Hype Cycle, AI Edition

Published 2018-08-28 by Kevin Feasel

Alex Woodie reviews the latest Gartner Hype Cycle for Emerging Tech:

Democratized artificial intelligence is one of five trends driving Gartner’s latest Hype Cycle for Emerging Technologies, which it updates this month, as it traditionally does during the month of August.

The analyst group took a slightly different approach with this year’s Hype Cycle for Emerging Tech and grouped the 35 individual technologies into major groups, which includes digitalized ecosystems, do-it-yourself biohacking, transparently immersive experiences, and ubiquitous infrastructure, along with democratized AI.

The five trends will “blur the lines between human and machine,” Gartner says. “CIOs and technology leaders should always be scanning the market along with assessing and piloting emerging technologies to identify new business opportunities with high impact potential and strategic relevance for their business,” writes Mike J. Walker, research vice president at Gartner.

It’s an interesting exercise and parlor game.

Comments closed

Power BI Helper 4.0 Released

Published 2018-08-28 by Kevin Feasel

Reza Rad has introduced a new version of Power BI Helper:

Power BI Helper is getting new features every time, and this time, we got some exciting features; You can now get your M (Power Query script) code beautified and colorful with version 4.0 of Power BI Helper. We also get the row-level security information exposed through the Helper application. Both information above will be now available when you export the model information to a document. If you like to learn more about Power BI Helper, read this page.

Looks good.

Comments closed

GOTO And Labels In T-SQL

Published 2018-08-28 by Kevin Feasel

Ryan Desmond demonstrates the purpose of GOTO in T-SQL:

So I was playing around at work today and decided for whatever reason to see how I could get the code I was writing to fire off only in certain situations.

If it’s Sunday maybe, or if this is in a particular environment, or if a record in an admin table was something specific.  I’m not sure how I’ll use this but I stumbled on Labels and decided to play with them.

Ok, so how to get to know labels.  Well, in order to get them to work sometimes I have to create labels that are based on some criteria.

I do try to avoid these as much as possible, but they are valid syntax and I’ve seen a couple of cases where it makes sense to use GOTO.

1 Comment

Saving Newlines When Copying From SSMS Grids

Published 2018-08-28 by Kevin Feasel

Kenneth Fisher shows us how to retain CR/LF values when copying and pasting from SQL Server Management Studio grids:

Now, you do need to be aware that the settings Tools -> Options are just for new query windows. If you want to change the settings for an already existing window then either go to Query -> Query Options or right click in the window and go to Query Options from there.

Either way, once you are done and re-run your script you get this when you copy and paste:

Click through for the tip.

Comments closed

T-SQL Tuesday Roundup

Published 2018-08-28 by Kevin Feasel

Wayne Sheffield is back with a T-SQL Tuesday roundup for August:

I asked for the topic to be on:

Tell me about a time when you ran up against your own brick wall, and how you worked it out or dealt with it.

We had 17 folks take this topic and run with it. As a teaser, my favorite line in all of these is:

My feeling is that if you’re not hitting brick walls you’re probably not pushing yourself or SQL Server hard enough!

With 17 responses, there’s a good bit of reading to do.

Comments closed

More On Radix Sorting In R

Published 2018-08-27 by Kevin Feasel

Inaki Ucar explains some of the nuance behind sorting in R:

The latest R tip in Win-Vector Blog encourages you to Use Radix Sort based on a simple benchmark showing a x35 speedup compared to the default method, but with no further explanation. In my opinion, though, the complete tip would be, instead, use radix sort… if you know what you are doing, because a quick benchmark shouldn’t spare you the effort of actually reading the docs. And here is a spoiler: you are already using it.

One may wonder why R’s default sorting algorithm is so bad, and why was even chosen. The thing is that there is a trick here, and to understand it, first we must understand the benchmark’s data and then read the docs.

Read the whole thing.

Comments closed