T-SQL Tuesday #007 – Roundup

New hotness

First of all I apologize for this roundup taking more time than usual to appear. This last week was my last week of work at the hospital and trying to get everything turned over as well as prepping for my move to Jacksonville my time’s been short to say the least. Without further ado, here’s this month’s #TSQL2sDAY roundup:

Stacia Misner reports in on her love for all things Reporting Services.

Brad Schultz fills in the gaps for us with a brilliant (as always) Intellisense post.

Glenn Berry gives us some great insight on core database engine improvements. If you don’t think R2 brought anything to the core product stop and read this post.

Andy Lohn merges several answers together with IntelliSense, Resource Governor and the MERGE statement! Also Andy’s new on my radar and has a witty Twitter name so time to add another person to follow on Twitter/add to Reader!

Jason Brimhall packs a ton of great information in his post about compression.

Seth Phelabaum talks about the new T-SQL and query writing improvements. This is awesome as I wasn’t aware of some of these new additions! Developers, this is a MUST read! Once again, another great blog added to my reader!

Robert Davis, one of the newest folks to attain the prestigious SQL Server Master certification, brings to our attention something you wouldn’t really think about: sysprep support! SWEET! Great walkthrough example using a virtual machine on Windows Server 2008 R2. Definitely an administrator MUST read.

Wayne Berry takes us for a trip to the clouds with the SQL Azure team and SQL Server 2008 R2’s SQL Azure integration. If you’re even thinking about putting your database in the cloud check out this post and subscribe to the SQL Azure team’s blog.

Kendra Little has plays the dating game in this post where she narrows down three great features (PowerPivot, Data Compression and The Data Collector) and finally comes up with her winning date. Read this post to find out who wins!

Bill Fellows hasn’t been able to play with R2 yet but gives a great and detailed walkthrough of how and why to use User Defined Table Types as Table-Valued Parameters. VERY cool! Added to reader.

Steve Jones, of Voice of the DBA podcast fame, gives us some great insight on StreamInsight. Honestly this feature also excites me so this is a really nice intro to the feature.

Gethyn Ellis is a man after my own heart by talking about Policy-Based Management. Also always great to discover another great blog from across the pond.

Ted Krueger, who did a fantastic job last week with SQL University’s HA/DR week, waxes poetic about all of the new changes in SSIS Data Flow Engine. If you care at all about SSIS performance check this out!

John Racer, aka @speedracer, could’ve written a whole book on all the new BI features but instead focused on Reporting Services. Nice breakdown of new hotness in SSRS.

Mark Blakey talks about PowerPivot and comparing it to a certain scene in Austin Powers (no, not the hot tub scene with Alotta). Also make sure to give him a follow on Twitter as well.

Nicholas Cain, aka @anonythemouse, despite claiming to cheat by referencing one of his old SQLServerCentral posts covers a real nice example of how Data Compression helps you save space.

Pinal Dave does a fantastic job of covering MERGE operations as well as showing us precisely how it improves performance over traditional methods. Very cool! Developers, definitely check this out!

Stef Bauer walks us through the Resource Governor feature and how it works. Pretty cool feature to help cull down those resource-hungry queries from ad-hoc queries.

Rob Farley throws us for a loop as his favorite part about SQL 2008 R2 doesn’t even have to do with the features! Check it out.

Bob Pusateri is excited by what’s NOT there with his overview of filtered indexes in 2008 and how they helped him solve a specific issue on a homegrown solution.

PowerShell I Heart You

PowerShell = Giggity Giggity Goo

Ok so in the last 48 hours I think I’ve fallen madly in love with PowerShell. My time is limited so I’ll make this short. Last night I wrote a script to copy files from one file share to another and log the process. Luckily PowerShell community guru Laerte Junior (Blog | Twitter) and Ron Dameron (Blog | Twitter) were online and willing to help me out (as always). I aksed him some general questions but was determined to write script on my own and just have him review it and point me in right direction. So after much Boogling I got some code example and spat this PowerShell turd out: 

[sourcecode language=”powershell”]
try {
$a = (Get-Date -uformat "%Y%m%d").ToString()
 Get-ChildItem \servernameextracts_foldertestdata*.001 | ForEach-Object -Process{Move-Item -PassThru -Force $_.FullName -Destination \targetservernametest | Format-Table -AutoSize >> ("\servernameextracts_foldertestlogstest_extracts_copy" + $a + ".log")  }
 catch {
  "Error occurred on $_" >> \servernameextracts_foldertestlogstest_copyerrors.txt
try {
 Get-ChildItem \servernameextracts_foldertestdata*.IDX | ForEach-Object -Process{Move-Item -PassThru -Force $_.FullName -Destination \targetservernametest | Format-Table -AutoSize >> ("\servernameextracts_foldertestlogstest_extracts_copy" + $a + ".log")  }
 catch {
  "Error occurred on $_" >> \servernameextracts_foldertestlogstest_copyerrors.txt


Basically it moves the files from one server to another and writes basic information of each file moved to a log file. The log file it writes to is dynamically named based on the date the script is ran. If the script bombs then it writes the errors to another log file the administrator can refer to for troubleshooting. 

Next up this morning I saw an article on SQLServerCentral.com ‘s newsletter on how to Make a 100+ Server Inventory in 30 minutes. 30 minutes? Hell, with PowerShell I think I could knock that out even quicker! And so I have, try 5. First create a text file with a list of all your server names. I’m sure you could get that using PowerShell too but Boogle it. Next try this code out: 

[sourcecode language=”powershell”]

Get-Content ‘c:serverlist.txt’ | ForEach-Object {systeminfo.exe /s $_ } | Out-File c:testserver_reports.txt


ONE line of code and you have a full report of everything on your server. Giggity giggity goo! ALRIGHT!

Professional Development Event:Taking Control of Your Career

This is just a reminder that this month’s Professional Development virtual chapter meeting we have MVP, PASS Board member, and all-around badass Jeremiah Peschka (Blog | Twitter) presenting on Taking Control of Your Career. Here is the abstract of the presentation:

Raises, promotions, and job offers don’t happen by accident; you need a plan. Through careful planning you can create and reach impressive goals. But what’s the point of reaching your goals if nobody notices? “If you build it, they will come” doesn’t apply when you’re building your career.

In this session Jeremiah Peschka will show you:

  • How to set achievable goals
  • The importance of planning your career
  • Methods for recording and communicating your accomplishments

When: June 16th, 2010

Where: LiveMeeting Link

Time: 1:00 PM EST – 2:00 PM EST (UTC-4)   [Use this online time converter tool to find time in your area]

If you’re interested in presenting at a future meeting for the PASS Professional Development virtual chapter please feel free to email me and let me know!

T-SQL Tuesday #007: Summertime in the SQL

Invitation for T-SQL Tuesday #007

New hotness

Welcome back to the blog party known as T-SQL Tuesday. I’m honored to be hosting this month and since its Summer I’ve decided to talk about hotness…feature hotness. In the last few months we’ve had the release of SQL Server 2008 R2 which brought along with it a slew of new features. So this T-SQL Tuesday I ask: What’s your favorite hot new feature in the R2 (I’ll be nice and include 2008 in general) release? Got some code that takes advantage of a new feature? Post it! Got an example of how PowerPivot let you slice and dice data you simply couldn’t before due to contraints? Show us! Make sure to apply your SPF 1433 and get to writing.

Cabana Rules

This whole party started when Adam Machanic (Blog | Twitter) decided to start this awesome blogger party where one topic is chosen by the monthly host, everyone submits their posts, and those posts are then rounded up. Catch is these posts must all go up within the same 24 hour period. Here’s a list of the parties held so far:

  • #001 Adam Machanic asked for your favorite Date/Time tricks
  • #002 Adam followed up by asking for your favorite Puzzling Situations
  • #003 Rob Farley made the commitment with his topic: Relationships
  • #004 Mike Walsh kicked asked for your input on IO
  • #005 Aaron Nelson asked us to report on “Reporting
  • #006 Michael Coles wanted to know All About BLOB
  • So write up your post and make sure to link back to this post for your entry to count.

    To participate your post must go live between 00:00:00 GMT on Tuesday the 8th of June and 00:00:00 GMT on Wednesday the 9th.

    Don’t Run on the Pool Deck

    Here are the posted rules for this shindig:


    1. Your post must go live between 00:00:00 GMT on Tuesday the 8h of June and 00:00:00 GMT on Wednesday the 9th. If your post doesn’t go live in the time limits, it won’t be included in the round-up post.
    2. Your post must link back to this post (by trackback or comment).
    3. “T-SQL Tuesday #007” MUST be in the title of the post.
    4. The above rule has been CHANGED! The new rule is that the title of the participating post no longer has to reference T-SQL Tuesday (although it’s still recommended). The post still has to link back to the hosting blog, but the link needs to be anchored from the NEW LOGO (found above), which must appear at the top of the post.
    5. It is your responsibility to verify the trackback or comment appears here on this post. If you don’t see your trackback add your own comment with a link back to your T-SQL Tuesday post and it will be included in the roundup.
    6. Don’t feed Gremlins after midnight
    Lifeguard on Twitter Duty

    A lot of the T-SQL Tuesday bloggers and bloggees are on Twitter. Follow the hashtag #TSQL2sDay and when your post goes live, tweet a link to it with that tag.


    You too can host this event! Just have participated in at least two previous #TSQL2sDay and let Adam Machanic know you wish to host. That’s it!