SQL New Blogger Digest – Week 2

I didn’t intend for last week’s digest to also be my post for week two of the challenge, but life got in the way and I wasn’t able to complete the post that I really wanted in time. So, that post will be written much earlier in week three and completed well ahead of the deadline.

Here are the posts collected from week two of the SQL New Blogger Challenge. It’s been compiled the same way last week’s was.

Author Post
@AaronBertrand #SQLNewBlogger Roundup – SQL Sentry Team Blog
@MtnDBA #SQLNewBlogger Week 2 – Teach Something New | DBA With Altitude
@ceedubvee A Insider’s View of the Autism Spectrum: Autism and Information Technology: Back on the Job Hunt
@DwainCSQL An Easter SQL | dwaincsql
@DwainCSQL An Even Faster Method of Calculating the Median on a Partitioned Heap | dwaincsql
@arrowdrive Anders On SQL: Stupid stuff I have done. 1/? Or, How I learned to stop GUIing and love the script
@MattBatalon Another TRUNCATE vs. DELETE tidbit… | Matt Batalon
@way0utwest April Blogging Challenge 2 – Primary Key in CREATE TABLE | Voice of the DBA
@GuruArthur Arthur BaanSQL Server error 17310 – Arthur Baan
@Pittfurg Blog Series: SQL Server Backup and Restores with PowerShell – Port 1433
@fade2blackuk Checking SQL Server User Role Membership with PowerShell « SQL DBA with A Beard
@gorandalf Database Compatibility Level 101 | Gorandalf’s SQL Blog
@nocentino Designing for offloaded log backups in AlwaysOn Availability Groups – Monitoring – Centino Systems Blog
@SqlrUs Detaching a Database – File Security Gotcha | John Morehouse | sqlrus.com
@MartynJones76 Devon DBA: Check Database Integrity Task Failed … Oh Dear Master Luke!
@toddkleinhans How Do You Visualize Abstractions? | toddkleinhans.com
@AalamRangi How to Have Standard Logging in SSIS and Avoid Traps | SQL Erudition
@gorandalf How to Test Existing T-SQL Code Before Changing the Compatibility Level | Gorandalf’s SQL Blog
@EdDebug HOWTO-Get-T-SQL-Into-SSDT | the.agilesql.club
@DBAFromTheCold In-Memory OLTP: Part 2 – Indexes | The DBA Who Came In From The Cold
@nicharsh It’s a Harsh Reality – SQL Sentry Team Blog
@SQLBek Learn Something New – SSMS Tips & Tricks « Every Byte Counts
@cjsommer Modify SQL Agent Jobs using PowerShell and SMO | cjsommer.comcjsommer.com
@MyHumbleSQLTips My Humble SQL Tips: Full List of SQL Server 2014 DMVs
@MyHumbleSQLTips My Humble SQL Tips: Running DBCC CHECKDB on TEMPDB
@way0utwest New Blogger Challenge 1 – Adding a Primary Key | Voice of the DBA
@uMa_Shankar075 Querying Microsoft SQL Server: In Memory Optimized Table in SQL Server 2014
@Jorriss Random Thoughts of Jorriss
@pmpjr Sidenote, the 4200 databases are a different story for another week… | I have no idea what I’m doing
@ALevyInROC SQL New Blogger Challenge Weekly Digest | The Rest is Just Code
@jh_randall SQL Server Monitoring – Getting it Right – SQL Sentry
@cathrinew Table Partitioning in SQL Server – The Basics – Cathrine Wilhelmsen
@eleightondick Teach Something New: PowerShell Providers [T-SQL Tuesday #065] | The Data Files
@rabryst The Art of Improvisation – Born SQL
@DBAFromTheCold The DBA Who Came In From The Cold | Advice on working as a SQL Server DBA
@Lance_LT The estimated query plan and the plan cache (Part 2) | Lance Tidwell the Silent DBA
@SQLJudo TSQL Tue 65: Memory Optimized Hash Indexes | Russ Thomas – SQL Judo
@sqlsanctum T-SQL Tuesday #065 – Teach Something New – APPLY | SQL Sanctum
@_KenWilson T-SQL Tuesday #65: FMT_ONLY Replacements | Ken Wilson
@m82labs Untangling Dynamic SQL · m82labs
@cathrinew Using a Numbers Table in SQL Server to insert test data – Cathrine Wilhelmsen
@tpet1433 Why yes I can do table level restores – Tim Peters
@Jorriss Why You Absolutely Need Alternate Keys: A Unique Constraint Story

SQL New Blogger Challenge Weekly Digest

Watching all of the tweets as people posted their first entries in the SQL New Blogger Challenge earlier this week, I quickly realized that keeping up was going to be a challenge of its own. Fortunately, there are ways to reign it in.

My first stop was IFTTT (If This Then That). IFTTT allows you to create simple “recipes” to watch for specific events/conditions, then perform an action. They have over 175 “channels” to choose from, each of which has one or more triggers (events) and actions. I have IFTTT linked to both my Google and Twitter accounts, which allowed me to create a recipe which watches Twitter for the #sqlnewblogger hashtag, and writes any tweets that match it to a spreadsheet on my Google Drive account (I’ll make the spreadsheet public for now, why not?).

The next step is to export the spreadsheet to CSV. I don’t have this automated, and may not be able to (I may have to find another workaround). Once it’s a CSV, I can go to PowerShell to parse my data. I want the end result to be an HTML table showing each post’s author (with a link to their Twitter stream) and a link to the post (using the title of the post itself).

Once I import the CSV file into an object in my PowerShell script, I need to do some filtering. I don’t want to be collecting all the retweets (posts starting with RT), and I should probably exclude any post that doesn’t contain a URL (looking for the string HTTP).

To extract the post URLs, I ran a regular expression against each tweet. Twitter uses their own URL shortener (of course), which makes this pretty easy – I know the hostname is t.co, and after the slash is an alphanumeric string. The regex to match this is fairly simple: [http|https]+://t.co/[a-zA-Z0-9]+

Then, for each URL found in the tweet, I use Invoke-WebRequest to fetch the page. This cmdlet automatically follows any HTTP redirects (I was afraid I’d have to do this myself), so the object returned is the real destination page. Invoke-WebRequest also returns the parsed HTML of the page (assuming you use the right HTTP method), so I can extract the title easily instead of having to parse the content myself. It’ll also give me the “final” URL (the destination reached after all the redirects). Easy!

My full script:

#requires -version 3
[cmdletbinding()]
param ()
set-strictmode -Version latest;
Add-Type -AssemblyName System.Web;
$AllTweets = import-csv -path 'C:\Dropbox\MiscScripts\Sqlnewblogger tweets - Sheet1.csv' | where-object {$_.text -notlike "RT *" -and $_.text -like "*http*"} | select-object -property "Tweet By",Text,Created | Sort-Object -property created -Unique;
$TweetLinks = @();
foreach ($Tweet in $AllTweets) {
    $Tweet.text -match '([http|https]+://t.co/[a-zA-Z0-9]+)' | out-null;
    foreach ($URL in $Matches) {
        $MyURL = $URL.Item(0);
# Invoke-WebRequest automatically follows HTTP Redirects. We can override this with -MaxRedirection 0 but in this case, we want it!
        $URLCheck = Invoke-WebRequest -Method Get -Uri $MyUrl;
        $OrigUrl = $URLCheck.BaseResponse.ResponseUri;
        write-debug $Tweet.'Tweet By';
        Write-debug $URLCheck.ParsedHtml.title;
        write-debug $URLCheck.BaseResponse.ResponseUri;
        $TweetLinks += new-object -TypeName PSObject -Property @{"Author"=$Tweet.'Tweet By';"Title"=$URLCheck.ParsedHtml.title;"URL"=$URLCheck.BaseResponse.ResponseUri;};
    }
}
Write-debug $TweetLinks;
$TableOutput = "<table><thead><tr><td>Author</td><td>Post</td></tr></thead><tbody>";
foreach ($TweetLink in $TweetLinks) {
$TableOutput += "<tr><td><a href=""https://twitter.com/$($TweetLink.Author.replace('@',''))"">$($TweetLink.Author)</a></td><td><a href=""$($TweetLink.URL)"">$([System.Web.HttpUtility]::HtmlEncode($TweetLink.Title))</a></td></tr>";
}
$TableOutput += "</tbody></table>";
$TableOutput;

And now, my digest of the first week of the SQL New Blogger Challenge. This is not a complete listing because I didn’t think to set up the IFTTT recipe until after things started. I also gave people the benefit of the doubt on the timing (accounting for timezones, etc.) and included a few posted in the early hours of April 8th. For week 2, it will be more complete.

Author Post
@eleightondick Kevin Kline on Twitter: “Advice to New Bloggers http://t.co/o1jfLOR4QI

@BarbiducH
Safe exit from WHILE loop using ##global temp tables | One developer’s SQL blog
@eleightondick Mike Donnelly on Twitter: “T-SQL Tuesday #065 – Teach Something New http://t.co/LoyFbhVOpw #tsql2sday”
@GuruArthur Arthur BaanTrace flags in SQL Server – Arthur Baan
@cjsommer Blogging and Intellectual Property Law | legalzoom.com
@ceedubvee A Insider’s View of the Autism Spectrum: Autism and Information Technology: Answering a Blog Challenge (Plus, Why I Like Data)
@arrowdrive Anders On SQL: A bit about me continued. Anders meets SQL
@SQLJudo Experience Is Overated | Russ Thomas – SQL Judo
@SQLBek T-SQL Tuesday #065 – Teach Something New | Mike Donnelly, SQLMD
@MtnDBA #SQLNewBlogger Week 1 “Eye of the Tiger” | DBA With Altitude
@Lance_LT The estimated query plan and the plan cache (Part 1) | Lance Tidwell the Silent DBA
@AalamRangi How to Use Temp Table in SSIS | SQL Erudition
@DwainCSQL An Easter SQL

Limitations

There are a couple limitations and gotchas with this process:

  • The IFTTT recipe only runs every 15 minutes (all IFTTT triggers run on 15 minute intervals) and only fetches 15 tweets each time it runs (again, IFTTT’s configuration). So if there’s a flood of tweets, they won’t all be captured.
  • I don’t really check the final destination of a link. For example, one of the first tweets captured contained a link to another tweet, which then linked to a blog post. Could I detect this & resolve the true final destination? Probably. But it’d involve a lot more logic, and it’s getting late.
  • I also don’t pick up every duplicate link/post. Again, I can probably get by this with some extra logic, but I don’t think it’s necessary right now.
  • It doesn’t post automatically to my blog, or anywhere else. I have to manually paste the HTML into my blog post(s).
  • I had to manually remove one link as it didn’t actually point to a post written for the challenge; it was tweeted with the hashtag, so my IFTTT recipe caught it.
  • I started collecting these tweets mid-day April 7th. If you posted before that, I’ve likely missed your post. You will be picked up for Week Two!