Inspired by Jason Strate’s (Blog| Twitter) post “PowerShell: Download List of SQL Server MVPs”, which was inspired by John Samson’s (Blog| Twitter) post “The Best Database Administrators Automate Everything” – which also inspired me. I decided to create a Powershell script to grab my starred items in Google Reader and format them into an HTML Page with links for my Weekly Challenges/Good Reading Posts.
The Set Up
I had an HTML template created for these pages, so I split it into three parts:
- The portion above the Good Reading and Links – I saved this to a text file located at “c:\temp\TopTemplate.txt”
- The Good Reading and Links
- The portion below the Good reading and Links – I saved this to a text file located at “c:\temp\BotTemplate.txt”
I then looked at my public page for Starred Items on Google Reader. Note this script works for any public listing on Google Reader, so I can create a Public Page and change the URL in this script and it still works.
The Code
##------------------------##
##--Reader Starred Items--##
##------------------------##
$webclient = New-Object system.net.webclient
$SourceFile = "c:\temp\Reader.txt"
$HTMLPage = "C:\temp\GoodReading.txt"
$TopTemplate = "c:\temp\TopTemplate.txt"
$BotTemplate = "c:\temp\BotTemplate.txt"
$url="http://www.google.com/reader/shared/user/14135896925018987441/state/com.google/starred"
$webclient.DownloadFile($url,$SourceFile)
#Get the titles with links
$itemtitle = Select-String $SourceFile -pattern "h2 class=""item-title""" |
% {$_ -replace "<div id=""items""><div class=""item""><h2 class=""item-title""><div class="""">", ""} |
% {$_ -replace "</div></h2>", ""} |
% {$_ -replace "<div class=""clear""></div></div></div> <div class=""item""><h2 class=""item-title""><div class="""">", ""} |
% {$_ -replace "c:\\temp\\Reader\.txt:[0-9]*:", ""}
#Get the details with links
$iteminfo = Select-String $SourceFile -pattern "<div class=""item-info"">" |
% {$_ -replace "c:\\temp\\Reader\.txt:[0-9]*:<div class=""item-info"">", ""} |
% {$_ -replace "</div>", ""}
$i=0
$FinalContent = " "
foreach ($t in $itemtitle)
{$FinalContent= $FinalContent +"<li>"+ $itemtitle[$i] + " " + $iteminfo[$i]+"</li>" + "`r`n "
$i++}
$FinalContent = "<p><strong>Good Reading from the Week</strong>:</p>" + "`r`n" + "<ul>" + "`r`n" + $FinalContent + "`r`n" + "</ul>"
$TopTemp = Get-Content $TopTemplate
$BotTemp = Get-Content $BotTemplate
$FinalContent = $TopTemp + $FinalContent + $BotTemp
Set-Content $HTMLPage $FinalContent
invoke-item $HTMLPage
When the script completes, I simply update a couple of statuses and change the photo and it’s good to go!
- All database changes go through me.
- Do or Review, do or review.
These have been my catch phrases since taking on the lead SQL developer role at my company, on my team. There have many changes that I’ve had to review – many, many changes, most of them are fine, but occasionally I get a real head scratcher.
Things that I’ve caught reviewing code in the last three weeks (note these are also justifications I’m taking to my boss for more help):
- VARCHAR(MAX) column – we avoid these for online reindexing
- A cursor – we avoid these for performance sake – it also didn’t need it
- Missing comments and Headers in stored procedures – we’ve got a standard header we use
- An infinite loop – a testing procedure that uses service broker that would have been looping forever.
- A date that should be UTC Date, but Getdate() was used
Is this good for the company? This is the question I’m asking myself…all the time actually. Running the numbers, how much time and money has my reviews saved the company?
For the next six months a guess would be:
varchar(MAX) column
- 10 hours of operational DBA/contractor time to write exception statements and meet on how to handle the online re-indexing
- 10 hours development DBA time to migrate the data on the next release
- 5 hours testing time
The cursor
- 10 hours development DBA time to rewrite and test the change
- 5 hours testing time
Missing Comments and Headers in stored procedures
An infinite loop
- 1 hour to troubleshoot and fix on release night with 30 people waiting around – 30 work hours total
A Date that should be UCT instead of Server time
- 2 hours development DBA time to locate and fix
48 work hours total for about that the same amount of time reviewing code.
Note these are just estimates, so I may be way off, but the old saying, “Pay me now or pay me later” really comes into play here.
There are a quite a few intangible benefits as well:
- I know what’s going on with the changes and I’ve tried to communicate to the other DBAs the changes coming. That should save lots of time for others
- I have got to know a lot of developers who I normally wouldn’t talk to.
- I’ve taught a few developers a few things about SQL. Hopefully this will save tons of time down the road.
- I’ve showed this site off to a fairly new SQL guy and he’s used the generate random data script a lot and it’s saved him some testing time.
Here’s a confession: I consider myself a time
management guru. I think about time management and ways to improve efficiency and effectiveness all the time. It drives my wife crazy.
It all started when I read The Seven Habits of Highly Effective People about 10 years ago. Then one Saturday when I was single, I had a ton of stuff to do/wanted to do – so I made a list of everything. I then prioritized it, crossed some items off, added a couple of related items and to my shock, I got most of the list completed. I really couldn’t believe it, so that really got me thinking about how to do the mundane things quickly, so I can have time to do fun stuff.
Naturally working as a DBA for the last 13..14 years, I’ve been in many time crunches. I think to survive as a DBA, you need to have some strategies for dealing with time crunches.
Here’s a method I came up with a while back. I use it all the time, especially when I’ve got a bunch of work to do. This is more for the “let’s get stuff done because we’ve got a lot to do” time rather than the “let’s sit down and figure out what needs to be done” time. It’s called the Lohn BOER Time Management System and it’s a process that is done once per day for about 10 minutes, first thing in the AM.
B – Brainstorm
- No holds barred brain dump
- Look at Yesterday’s tasks to remember
- Look at meetings coming up
- Ask yourself, what do I really want to accomplish today?
O – Organize
- Move and delete brainstormed items to things that fit together and create your list for the day
- This is a combination of prioritizing and linking tasks
- Keep in mind what you really want to get done – those move to the top of the list
- Balance between big rocks (tasks you want to complete) and low hanging fruit
E – Execute
- Things are simple at this point – just follow the list
- Stuff will come up, try to stick with your list, but it is flexible, so you may have to go back a step to the organizing phase
R – Review
- Either at the end of the day or the beginning of the next day, take a couple of minutes to review your list.
- Some tasks won’t get done, others will need to be followed up on and others will also need follow through
So there it is. I actually presented this system in a class I took a couple of years ago. Some of the benefits of following this system are:
- Following through on tasks – some task are listed as complete, but need the follow through
- Marketing yourself by showing what you’ve done
- Do things more efficiently
- Do things more effectively
- Accomplish more
Give it a try – let me know if it works out for you.
