Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

Create a List of Links from Google Reader Starred Items with Powershell

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:

  1. The portion above the Good Reading and Links – I saved this to a text file located at “c:\temp\TopTemplate.txt”
  2. The Good Reading and Links
  3. 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!

February 22nd, 2011 | Tags: , , , | Category: Powershell, Scripts, SQLServerPedia Syndication, Time Management | 2 comments

How To: Replace a string in all files in a directory with Powershell

Today I realized I made a fairly significant mistake.  I had misspelled a column name in a bunch of procedures and views that I had updated.  Not a big deal, except each procedure and view was a file and I had about 75 files.  Only a few of them contained this change, I don’t know which ones, but most of them had other changes.  What to do…

  • I could manually check all files with a few routine of Keystrokes – that might take an couple of hours.
  • I could check sysdepends for dependancies – nope none of the procedures have been executed.
  • I could check syscomments for the column name – that would kind of work, but I would still need to manually update it.
  • Powershell!!  I could find and replace in the files and execute them as well!

I used the following sites as they have good examples.

Here’s what I came up with:

##Finds all the occurrences in the Views folder##
foreach ($f in Get-ChildItem -path "C:\Temp\Views\" -Filter *.sql | sort-object)
{$Location = $f.fullname
Select-String -path $Location -pattern "Andee"| out-file -filePath "C:\Temp\ListoObjects.txt" -append}

##Replaces all the occurrences in the Views folder##
foreach ($f in Get-ChildItem -path "C:\Temp\Views\" -Filter *.sql | sort-object)
{(Get-Content $f.fullname) |
Foreach-Object {$_ -replace "Andee", "Andy"} |
Set-Content $f.fullname}
January 28th, 2011 | Tags: | Category: HowTo, Powershell, Scripts, SQLServerPedia Syndication | One comment

How To: Execute all SQL files in a Directory with Powershell

I’ve got a lot of changed altered procedures for a project I’m working on.  I’ve been altering a lot of data in my test database, so I needed to restore my database and apply the changes I’ve made.  The easy way to do would be a powershell script.  Here’s what I found.

From Martin Bell for the post, “Executing all .SQL files in a directory with Powershell”  Thank you Martin!

I also used twitter and asked #SQLHELP and SQLSoldier responded with this in about 3 minutes.  Very impressive!

Get-ChildItem -Path "C:\SQLScripts" -Filter "*.sql" | % {invoke-sqlcmd -InputFile $_.FullName}

Here’s what I altered Martin’s script to:

foreach ($f in Get-ChildItem -path "C:\SQLScripts\" -Filter *.sql | sort-object)
{
$out = "C:\SQLScripts\OUTPUT\" + $f.name.split(".")[0] + ".txt" ;
invoke-sqlcmd –ServerInstance <<ServerName>> -Database <<DBName>> -InputFile $f.fullname | format-table | out-file -filePath $out
}

Here’s all the info on the Invoke-SQLCMD from MSDN.

January 10th, 2011 | Tags: | Category: HowTo, Scripts, SQLServerPedia Syndication | 5 comments