Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerShell: Download List of SQL Server MVPs

Turtle Shell?!

Turtle Shell?!

This weekend, I was working on something that I will blog about later this week.  As part of that, though, I needed to download a list of all of the SQL Server MVPs that Microsoft publishes.

A few times back, I just cut-n-pasted these down to get them all out quick and dirty.  After reading John Samson’s (Blog | @JohnSansom) The Best Database Administrators Automate Everything, I figured I would take it to heart.  This time I would automate it with a PowerShell script.

Below is the script I came up with.  It extracts the MVP profile ID and their name and places them in a file at c:\temp\mvp_import.txt.


<#Start up a web client and create file variables#>
$webclient = New-Object system.net.webclient
$file1="c:\temp\download_mvp_html.txt"
$file2="c:\temp\item_mvp_html.txt"
$file3="c:\temp\mvp_import.txt"
Clear-Content $file3
$i = 1
do {
$url="https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&amp;amp;competency=SQL+Server&amp;amp;page=" + $i
$webclient.DownloadFile($url,$file1)

<#Sift through the file for lines that include hyperlink to MVP profile ID #>
Select-String -path $file1 -Pattern "<a href=""/profile=........-....-....-....-............"">[A-Z].*?</a>" -AllMatches `
| % { $_.Matches } | % { $_.Value } | set-content $file2

<#Trim the HTML from the output #>
Get-Content $file2 | % {
$_.substring($_.IndexOf('profile=')+8, 36) + "|" + $_.substring($_.IndexOf('">')+2,$_.IndexOf(': SQL Server')-$_.IndexOf('">')-2) | add-content $file3
}
$i++
}
while ($i -le 29)

There is probably a quicker method to extract this data, but it works and got me what I needed.

Related posts:

  1. MCM Reading List – A New Years Resolution
  2. FileSystemHelper SQL Server CLR – Codeplex
  3. December PASSMN Meeting Today

Comments

Posted by Jason Brimhall on 8 February 2011

Now you need to post the process to import that flat file (maybe as day 32 of your ssis series ;) ) into a database. JK

Posted by Jason Strate on 8 February 2011

I'm actually working on that.  There are a couple changes I am planning to remove the hardcoded 29 loops.  Once I get some more of it together I'll be posting all of the pieces and a backup of the database itself.

Leave a Comment

Please register or log in to leave a comment.