Press "Enter" to skip to content

Curated SQL Posts

Logging in Python

Published 2022-04-08 by Kevin Feasel

Daniel Chung shows off the logging module in Python:

Note that now all five messages were output, so the default level that the root logger logs is now “DEBUG.” The log record attributes (such as %(asctime)s) that can be used to format the output can be found in the logging documentation.

Although there is a default logger, we usually want to make and use other loggers that can be configured separately. This is because we may want a different severity level or format for different loggers. 

Next on the todo list is to implement the Reader monad to hide that logging deeper in your code base so that you a) don’t need to see logging code everywhere, and b) don’t forget to include logging in some function.

Comments closed

Accessing Twitter’s V2 API via R

Published 2022-04-08 by Kevin Feasel

Bryan Shalloway dives into the mess known as Twitter:

academictwitteR is probably the most established package that provides a quickstart entry point to the V2 API. However it requires creating an academic account in twitter, i.e. the user must be affiliated with a university. I also stumbled onto RTwitterV2 and voson.tcn which both also provide quickstarts on the V2 API, but did not explore these.

Instead I followed the tutorial Getting started with R and v2 of the Twitter API by Twitter Developer Advocate Jessica Garson that uses {httr} to interact more directly with the API. I highly recommend reading her tutorial. The code below is mostly just copied from there but changed to provide an example of getting the usernames of those that liked a tweet.

Read on for a how-to and some notes.

Comments closed

Tokenizing Results in SSIS

Published 2022-04-08 by Kevin Feasel

Peter Schott shows off a handy trick:

I’ve worked with SSIS for some time now, but a recent question and post pointed out an expression I’d never used before. We often have a need to split strings and pick out some portion of that string. Sometimes we have a need to pull in everything in the “nth” occurrence of a string. The TOKEN expression can be used to get that particular value. I tested this out by mocking up a really simple package.

Read on to see how.

Comments closed

Building a Gantt Chart with Power BI Paginated Reports

Published 2022-04-08 by Kevin Feasel

Paul Turley unlocks one of my guilty pleasures:

A Gantt chart is a running list of activities with the duration for each displayed as a horizontal bar depicting the beginning and ending day along a horizontal scale. The challenge is that this is not a standard chart type in either Power BI or SSRS/Paginated Reports. Furthermore, project planners may prefer to see activities as rows in the format of a printed page, as you can see in this example:

I like Gantt charts more than is probably healthy. Paul shows a method which isn’t exactly easy but it does the trick.

Comments closed

Reviewing SQL Server Logins

Published 2022-04-08 by Kevin Feasel

Lee Markum takes a look at logins:

You know you need to be thinking about SQL Server security, but maybe you’re not sure where to start. Topics like firewalls and ports and port scanners and such may be dancing your mind. Those are good things to think about, but they are not under your sphere of influence as a data professional in charge of SQL Server. So, what can you do?

Your first place to start is by looking at the Logins, which as I’ve explained in a previous  post, are at the level of the SQL Server instance level.

Read on for two approaches.

Comments closed

Skipping SQLPS

Published 2022-04-08 by Kevin Feasel

Ben Miller avoids SQLPS:

In researching some things for a presentation, I was to give at Intersections, Spring 2022, I came across this documentation page for SQL Server PowerShell that in the SQL Server Agent section it indicates that there is a way to get the Agent Job Step to skip loading the SQLPS module and you get to use PowerShell natively and can load whichever modules you would like.

What? Huge News!

That is good news. SQLPS is so outdated it’s not even funny, so this gives you a much better alternative.

Comments closed

Replacing PWDENCRYPT with HASHBYTES

Published 2022-04-08 by Kevin Feasel

Randolph West does a switcharoo:

Dave is referring to the Microsoft Docs page for PWDENCRYPT(), which has been deprecated for some time. Unfortunately, although the recommended replacement is HASHBYTES(), there isn’t an example on either page of how to replicate the functionality of PWDENCRYPT().

So, borrowing from Sebastian Meine who wrote an article titled Hash Algorithms – How does SQL Server store Passwords?, this is how you can replicate the functionality of PWDENCRYPT() to create a login, using the HASHBYTES() function instead:

If this is what you have, so be it…but an algorithm like bcrypt or scrypt would be so much better for this purpose than SHA2 or SHA3. That means using a third party library for it but there are plenty for React, .NET, Python, etc.

1 Comment

Printing ggplot2 Plots as Receipts

Published 2022-04-06 by Kevin Feasel

Bob Rudis has fun with a Point of Sale printer:

At the end of March, I caught a fleeting tweet that showcased an Epson thermal receipt printer generating a new “ticket” whenever a new GitHub issue was filed on a repository. @aschmelyun documents it well in this blog post. It’s a pretty cool hack, self-contained on a Pi Zero.

Andrew’s project birthed an idea: could I write an R package that will let me plot {ggplot2}/{grid} objects to it? The form factor of the receipt printer is tiny (~280 “pixels” wide), but the near infinite length of the paper means one can play with some data visualizations that cannot be done in other formats (and it would be cool to be able to play with other content to print to it in and outside of R).

Read on for a fun story which gets an entry in my most coveted category. H/T R-Bloggers

Comments closed

Fun with Powershell: A Roundup

Published 2022-04-06 by Kevin Feasel

Robert Cain has a bunch of posts:

Since September 2020 I’ve been posting extensively about PowerShell in a series I’ve titled Fun With PowerShell. In my next post I will begin a series on a new topic, but before I do I wanted to leave with a Fun With PowerShell Roundup” post.

Below you will find a listing of all posts in my Fun With PowerShell series. Note there are other posts I’ve done in this time frame, but since they did not focus on PowerShell I’ve omitted them from this listing.

Click through for a lengthy and good series.

Comments closed