SQL Saturday #111 Pre-Conference Sessions

SQL Saturday is coming back to Atlanta on April 14, and once again, we’ve managed to schedule 3 awesome pre-con sessions for the Friday before (April 13). They will cover a broad spectrum of topics: Performance Tuning, SSIS, and SSRS. Registration is limited, so go take a look and see if you or someone from your company can benefit from this great training opportunity.

Also don’t forget that we will have an absolutely amazing lineup of presenters for Saturday! We’ll be announcing the final schedule for SQL Saturday #111 by Monday, February 13th. Registration is open, and we’re already halfway to capacity, so head out to SQLSaturday.com to reserve your seat.

_____________________________________________________________

Andy Leonard and Matt Masson– A Day of SSIS – http://dayofssis.eventbrite.com/

A Day of SSIS was developed by Andy Leonard to train technology professionals in the fine art of using SQL Server Integration Services (SSIS) to build data integration and Extract-Transform-Load (ETL) solutions. The training is focused around lectures and emphasizes a practical approach. The target audience for this training is database professionals, application developers, and business intelligence practitioners interested in acquiring or expanding their existing SSIS skill set. No experience with SQL Server or SQL Server Integration Services is required before attending this training. It is helpful (but not required) that students possess some knowledge of and experience with relational databases. SQL Server knowledge / experience will be more helpful than experience and knowledge with other technologies.

_____________________________________________________________

Adam Machanic – No More Guessing! An Enlightened Approach to Performance Troubleshooting – http://nomoreguessing.eventbrite.com/

Scratching your head, you stare at the screen. Should you rebuild an index? Create a new one? Reboot the server? Why is this query so slow?!? Figuring out performance problems can sometimes feel like fumbling your way through a dark room. Maybe you’ll get lucky and find the right solution―or maybe you’ll stub your toe. Either way, it’s a slow, potentially painful process. Yet finding the root cause of most performance issues is a simple exercise, once you understand where to look and when. In this full day seminar, you will learn a proven methodology that can be used to approach virtually any performance problem. Created and refined by Adam Machanic over several years, this strategy leverages core SQL Server performance tools (including dynamic management views, Extended Events, and WMI counters), applying them to various performance troubleshooting techniques (such as waits and queues analysis, baselining, and real-time activity monitoring). Each of these tools and techniques has a unique role, and you will learn to use them cooperatively to quickly and adaptively find the actual cause of performance issues. All of this will be illustrated through complete demonstrations that will teach you how to drill from high-level problem detection all the way to specific spot in a query plan or deeper―pinpointing the exact problem and helping you to quickly solve it. Attend this seminar to take full control of your databases—and never again stumble blindly through the dimly lit world of performance troubleshooting.

_____________________________________________________________

Jessica Moss – Learn SSRS in a Day – http://ssrsinaday.eventbrite.com/

SQL Server MVP, Jessica M. Moss, presents an exciting, introductory, full day training session on SQL Server Reporting Services 2008 R2. In the three-part class, Jessica will teach you how to build reports from the ground up. In Part 1, learn the basics of report development, including picking a report development tool and creating your first report. Part 2 delves into visualizations, groupings, and drill-down functionality. Finally, Part 3 highlights core administration tasks in Reporting Services. In addition, Jessica will point out industry-wide best practices for report development and show numerous live demos using a variety of data sources.

Friday, April 13, 2012

Georgia State University – Alpharetta
3775 Brookside Pkwy
Alpharetta, GA

Register for pre-conference sessions by March 15 for $99!
($109 after March 15)

For registration to the Saturday Event and more information visit http://www.sqlsaturday.com/111/eventhome.aspx.

I received multiple requests to post the scripts I used before I even had a chance to sit down so here they are.

If you were in the class and would like to use these and don’t even know where to start with PowerShell have a look at my “Resources for my SQL Server PowerShell Extensions webcast” post.

If you ever get a chance to attend one of their classes and present in front of Paul and Kim TAKE IT!   Even if it’s just about your SSMS tips or how to work around a problem you encountered.  The feedback you get is *more* than worth it!

Building a PowerShell function is almost as easy easier than building a stored procedure around a select statement in SQL.  The reason PowerShell is easier than SQL is that when you have a parameter that you are passing in, you can give it a data type, but you don’t have to.

For the function below I gave it a default value of the local machine but you can pass in a machine name that you are trying to get to.

Just copy the code below into an ISE window and hit F5.

function Get-MachineInfo($ServerName="localhost")
{
get-wmiobject win32_computersystem -ComputerName $ServerName |
select DNSHostName, Manufacturer, Model, SystemType ,
        @{Name="TotalPhysicalMemoryInMB";Expression={"{0:n2}" -f($_.TotalPhysicalMemory/1mb)}},
        NumberOfLogicalProcessors, NumberOfProcessors, CurrentTimeZone, DaylightInEffect
}# End Get-MachineInfo

After you’ve done that, to call the stored proc function simply type in the name ( Get-MachineInfo ) to the prompt at the bottom and hit enter

It came about because some of the SQL MVPs were trying to figure out the best way to download a VM that had been split up into 36 different equal-sized files.  I’m sure there’s a better way than this but at the same time it took me about 4 minutes to write.  Write is the wrong term, more like copy/paste/change a few things.

Give it a whirl and let me know what you think! 

1. Table Size
1. Store Results
2. Compress Tables
1. Compress Indexes
2. Store Results
3. Process Cube
4. Backup Database –PassThru
5. Download Denali CTP
6. Grab Configuration Info
7. New Get-DisksSpace try to pass from Reg servers w/ Group
8. Email Space Report
9. CSV with Bulk Insert
10. Import CSV with Out-DataTable
11. Allen Kinsel – Logs
12. SQL Saturday – Navigate Event Schedule XML

I showed how you could pull size and free space information from all of your data files, then used the same technique to pull size information from all of your tables across every database on an instance.  Later we created a database and some tables, and then showed how to do some basic login comparisons, all with SQLPSX.

In addition, we stored the results of the frees pace and table size information in a database using the Out-DataTable and Write-DataTable functions.  Finally we wrapped up with a quick demo on how the new SQL Server cmdlets inside of the SQLPS Module in Denali combined with some new functionality in PowerShell v3 CTP1 can really make backing up a database with PowerShell a lot easier.

Here’s what you’ll need to recreate today’s demos;

• Download and install SQLPSX
• The Out-DataTable function from the TechNet script repository
• The Write-DataTable function from the TechNet script repository
• and these scripts
• Oh, and here are the handful of slides that I had

I hope I see you next week at the PASS Summit or next month during another webcast, this time on customizing your own functions to work with the SMO.

In the meantime I want to give a quick mention about SQL Saturday #89 happening here in Atlanta in just 12 days. We’ve hit the maximum number of registrations we feel comfortable handling. If you’re on the wait list, we’ll send out an email later this week to people that were able to successfully register but now can’t make it, asking them to unregister. From the looks of the registration counts this might end up being a little under twice the size of last year’s SQL Saturday.

More blogs coming soon!

I woke up at 5 am this morning to kick off my download.  I’ve finished the install and I’m happy that I can finally report that we have new cmdlets for SQL Server. 

First off, after running Get-Module -ListAvailable I saw:

Manifest   SQLASCMDLETS
Manifest   SQLPS

This means that SQLPS inside of SQL Agent should finally allow you to import a module during your job step. 

There is now a Function called SQLServer that, from what I can tell, is the SQLServer Provider.  When under PS SQLSERVER:\> if you do a dir | Select pschildname you will find the following list:

PSChildName
———–
SQL
SQLPolicy
SQLRegistration
DataCollection
XEvent
Utility
DAC
IntegrationServices
SQLAS

From my notes it looks like XEvent, IntegrationServices, and SQLAS are the directories that have been added here.  Can’t wait to try out the IntegrationServices portion!  [Update]: Apparently I may need a defaul instance install for this to work.

I also noticed that Tab-Completion appears to be fixed!!    Also, SQL Registrations > ‘Database Engine Server Group’ are split up into instances like CMS was.  Not sure what that’s all about just yet.  In fact when I try to use it, it doesn’t seem to work.  Maybe I should read the help files.  But I think they may have just gotten CMS & RS reversed.  CMS isn’t working for me either yet.

When you drill down under XEvents and go to your Denali instaince you will see: Packages, & Sessions.  I guess this means that I finally need to read Jonathat’s blog series on X-Events.

When I imported the SQLPS module I received a warning about unapproved verbs.  Oh well, hopefully they’ll work that out soon.  Next I ran Get-Command -Module “SQLPS” and found the following list of cmdlets.

Analysis Services got some love too in the form of 11 cmdlets!  After inporting the SQLASCMDLETS module I ran this Get-Command -Module “SQLASCMDLETS” and found the following.

More news to follow soon!

PowerShell has taken the Pole for the DBA division at SQLRally!  Come see why the organizers have chosen this session to lead the pack to the Green Flag.

We will be covering new ground, not rehashing last year’s PASS Summit presentation.  I have developed several new examples for leveraging PowerShell in your everyday DBA role since then.  I am promising 6 new scripts for everyone but if I get on a role that number might be closer to 10 when they drop the green flag on this year’s event. 

If you’ve never used PowerShell before don’t worry. We will spend a few minutes on a ‘get up to speed lap’ so everyone is going at the same pace when we step on the gas with all this new material.  All scripts will be made available for you to download during the session and access later on from pit-row.

After my session here’s what I’ll be checking out:

What sessions I'll be checking out during SQLRally

Deploying SQL code with PowerShell can be very easy but why would you want to do that instead of just open a script in SQL Management Studio and run it?  The number 1 reason that I can think of is when you need to deploy to multiple destinations.  Let’s get started with something simple, we’ll deploy a table that I use in the post on collecting database sizes.

Invoke-Sqlcmd -ServerInstance Win7Netbook -Database CentralInfo -InputFile C:\temp\dbo.DatabaseFileSizes.SQL

That’s a pretty simple example and one that doesn’t offer too much any benefit over just opening up SSMS and running the script.   Let’s take it a little further with the loop construct and this time we’ll deploy Adam Machanic’s ( blog | twitter ) sp_WhoIsActive to a bunch of machines

$DeployFile = C:\temp\who_is_active_v11_00.sql
<# Loops through Registered SQL Severs and applys WhoIsActive #>
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Development\ | where {$_.Mode -ne "d"} )
{
"Deploying to "+ $RegisteredSQLs.ServerName;
Invoke-Sqlcmd -InputFile $DeployFile -ServerInstance $RegisteredSQLs.ServerName -database master}

So now we can see how we can deploy a SQL Script to multiple machines with just 4 lines of PowerShell.  The script obviously doesn’t have to be deploying code.  It could inserting/updating data or even verifying permissions.

Marco provides an overview of using sysprep for Windows Server 2008R2, and SQL Server 2008R2, which also now supports this feature. This is especially useful when wanting to get up a new (lab or even production environment) quickly. He will be doing this using only Microsoft Hyper-V & Windows PowerShell without any other external tools.

Marco Shaw
Marco ( blog | twitter ) is a consultant with CGI and has over 12 years of IT experience. He has been awarded a Microsoft MVP award for the last 4 years in the Windows PowerShell category.

LiveMeeting: Registration Link
_____________________________________________________________
Designing Cubes for Performance
April 20, 2011, 12 PM EDT (GMT -4)
Stacia Misner

Building a cube is simple. After all, you have a lot of wizards at your disposal to do the development work. But building a cube that delivers data quickly requires you to make additional changes to the database design. In this session, you’ll learn what steps you need to take in the development process to ensure the cube is designed for optimal performance, whether you’re using Analysis Services 2005 or Analysis Services 2008. You learn not only how to properly design dimensions, aggregates, and partitions, but why these design principles improve performance.

Bio:
Stacia Misner is a consultant, educator, mentor, and author specializing in Business Intelligence solutions since 1999. With more than 25 years of experience in IT, she is the author or co-author of ten books about BI. Her most recent books include Business Intelligence in Microsoft SharePoint 2010 and Building Integrated Business Intelligence Solutions with SQL Server 2008 R2 and Office 2010. Stacia provides consulting and custom education services through Data Inspirations, writes about her experiences with BI at blog.datainspirations.com, and tweets as @StaciaMisner.

LiveMeeting: Link
_____________________________________________________________
Database Standards SOP
April 21, 2011, 1 PM EDT (GMT -4)
Thomas LeBlanc

Join Normalization nut Thomas LeBlanc for a review of a standard operating procedure used among DBAs at an employer. See the changes he made after joining the BI group at this employer. The session will go through naming conventions, check list for creating a table, formatting in stored procedures, and more. A brief preview of the SQLRally talk 3rd Normal Form: That’s Crazy Talk!!! Will be given about Lookup tables. This discussion comes from 21+ years of developing databases for application developers. The use of identity columns for primary keys, and the need for a unique constraint on transaction tables that do resort to ID columns will be covered.

Thomas LeBlanc
Thomas is a semi-retired Senior DBA turned ETL Lead Architect @ Amedisys in Baton Rouge, LA. Worked in the IT field for 21 years experience w/ COBOL dBase, FoxPro, Visual FoxPro, VB 3 thru 6 and .Net(C#). Designing and developing Normalized & Dimensional database has become his passion. Full-time DBA work started about 9 years ago. he has been blessed with speaking at SQLRally this year. Free time is spent helping other & improving his relationship with family & God.

LiveMeeting: Link

Building Applications That A DBA Will Love
April 12, 2011, 12 PM Eastern
Presenter: Andy Warren
Attendee URL: LiveMeeting

It’s natural for the focus to be on the end user when we build applications, but it’s important and worthwhile to build into our design some things that make it easier for the DBA to provision, tune, and support the product we deliver. So what does a DBA care about? Performance of course, but also security, scalability, space usage, and yes, even documentation. This presentation will cover tips in a number of areas that address the concerns of the DBA, and we’ll talk about why you should care about making the DBA happy too!

Andy Warren

Andy Warren is a SQL Server consultant and trainer who occasionally writes some code too. He’s on the PASS Board of Directors, was a founding partner in SQLServerCentral.com, started the SQLSaturday event format, and is currently a SQL Server MVP. Andy blogs at www.sqlandy.com, and can be reached via Twitter as @sqlandy and on LinkedIn at www.linkedin.com/in/sqlandy.

How do I attend?
Attendee URL: LiveMeeting
_____________________________________________________________

Efficient and effective processing of OLAP CUBES with AMO and XMLA
Fri April 15th 12PM EST (US)
Speaker: Steve Simon

Topic Description:

Within the financial industry, rapid and effective decision making is critical to our client relationships. Analytical tools such as OLAP cubes help make this a reality. In many 24 X 7 enterprises, data capture and cube updates occur on an ongoing basis throughout the business day. We are able to achieve this through the innovative utilization of SQL Server Integration Services (SSIS). This intermediate level hands-on presentation will show the attendee how we have utilized Analysis Management Objects (AMO) commands and SSIS to ‘fire’ our OLAP CUBE updating processes. The presentation will discuss the high level concepts behind Analysis Management Objects (and the XMLA that is generated from the commands) and the scenarios under which we can benefit from their usage. Topics covered will be the updating of dimensions, measures, partitions, the cube and more.

Bio:

Steve Simon is an Assistant Vice President with State Street Corporation in Boston. He has been involved with database design and analysis for over 26 years. Steve has presented papers at eight North American PASS Summits (in Orlando, Seattle WA (4), Denver CO (2) and San Francisco CA), two at PASS Europe 2009 and one at PASS Europe 2010. He has just recently presented his PASS 2008 Summit presentation in Johannesburg South Africa. Steve is actively involved with the SQL Server community within the Boston area and is a regular presenter at the New England Code Camps and SQL Saturday events and participates actively on many of the Microsoft User Forums. He is also the Virtual Chapter leader of the Professional Association for SQL Server (PASS) Oracle – SQL Server Virtual Chapter and is a PASS regional mentor. He will also be presenting a data mining paper at SQL Teach in Montreal between May 31^st , 2011 and June 3^rd 2011.

URL: https://www.livemeeting.com/cc/usergroups/join?id=RPN4J9&role=attend&pw=J%25%7E*7r%229d

Last year I kept hearing about all these people using something called GTD (Getting Things Done).  They didn’t talk about it much.  But occasionally one of them would send out an excited tweet mentioning GTD.  I got tired of hearing all these excited tweets and finally decided to dive in.  I had actually purchased the audiobook of GTD years ago but never got around to listening to it.  I was only a few pages into the book when I had the urge to make a big list of everything I have to finish.  I started out putting everything into a spreadsheet.  Everything from “Script new Mount Point Daily Check Job” to “Remember to pickup dry cleaning” was going into this list.  I would frequently realize that I was behind on a certain task; so I’d stop and take care of that item. 

After 6 hours I was in love with this technique.  After 18 hours I was slightly overwhelmed.  And after  48 hours I was ready to quit.  I had a list of over 80 items, and adding to that list did help me finish any of those items.  In fact the list left me feeling that life was more out of control than before my to-do items were written on paper.  I couldn’t understand how anyone –let alone all these people I had heard of – could get anything done using method.  Like my first attempts with PowerShell, I closed it down and decided it wasn’t very useful.

At the same time I had just started a new job much closer to home.  I had an extra hour available for every workday, but I was accomplishing less than ever.  It felt weird.  Almost everything was in place – so much so that I felt anyone should be able to succeed given these conditions – and yet I wasn’t!  I wasn’t getting to the gym more, I wasn’t spending significantly more time with my daughter and I wasn’t all that much less stressed.

I took a while, but after seeing that list and realizing how much was there I recognized that, short of having two clones, I was never going to be done with my to-do list.  What to do?  Simple.  That list must be shorter. With the writing on the wall I decided to categorize the things on my now out of date GTD list and see what I could scrap.  To say it was a punch in the face would like saying Southerners like Barbeque.  “It ain’t dinner unless a pig had to die.”  Right now my daughter is 13 and still listens to me at least pretends to listen when I speak.  I’m not sure how much longer that will last.  Her school is 2.2 miles away from both our home and my office  (For those playing at home, that is a 12 minute Atlanta commute. ). At the end of the day life is about priorities and I’ve finally figured mine out. So until my daughter stops letting me drop her off at school, play taxi-cab for her and her friends, and generally allows me to be seen with her in public… well, I’m not going to have a lot of free time to do much else

So what does that mean?  Am I going to stop blogging, writing, speaking, everything?  I don’t know, quite possibly.  Maybe for a while, maybe for a good while.  I have two items in my Professional Development category that I’ve committed to get done (and thankfully they both won’t take up very much time at all).   I’m still going to finish those two and they might result in some interesting content.  But for everything else?  Well following the GTD method those are all marked as “Maybe-Some Day”.

– Thanks for the idea Tom

This one would look great in your email signature!

This one would look great in the tight-hand column of your blog

Or this one:

If you’re using the PrimePress theme like me you’ll want to put this under /wp-content/themes/primepress/headers

Paul’s query is pretty simple:

SELECT os.Cores, df.Files
FROM
(SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
(SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;
GO

I simply took that query and wrapped it with almost the same code that I used at SQL University.  The difference allows me to show off two new tricks. 

• First, I separated out the part that grabs the list of servers from your Registered Servers to happen before the foreach loop.  I had received feedback from a reader that A) Having it there made it hard to read on my website, and B) it simplified the logic flow.  It also has the added benefits of performing faster when you’re running against hundreds of instances like me, and making it a little more clear how to swap it out when you want to use something besides Registered Servers to list your instances.  For more information on the different data sources that you can use for reading in a list of servers have a look here at Post 5.
• Second I added this $S++; it is a little piece of code to enumerate the instances for you.  Last time I had written the results to a table and then read them out using DENSE_RANK but since we don’t have multiple results this time [and because I love showing off cool PowerShell code] I used this instead.

$ServerList = dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | where {$_.Mode -ne "d"}            

foreach ($RegisteredSQLs in $ServerList )
{
$S++;
$dt+=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -Query "
    (SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
    (SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df" -As 'DataTable'
}            

$MultipleResults = $dt | ConvertTo-Html -Property Instance, Cores, Files | Out-String;            

Send-MailMessage -To paul@SQLskills.com `
 -Subject "tempdb Survey Results" `
 –From YourEmailAddress@GoesHere.com `
 -SmtpServer YourSMTPServerGoesHere `
 -Body $MultipleResults -BodyAsHtml

Hopefully this will help you email query results to your heart’s content   Big thanks to TJay Belt ( blog | @TJayBelt ) for pointing out this request on the #SQLHelp hotline to me.  I’ve done this tons of times myself and I LOVE that anyone can walk up with a question and when people don’t have the answer they still do what they can to help them out right away.  I less than three community!!

Please Note:

DO NOT SPAM Paul Randal! You should probably try emailing this to yourself a time or two before you decide to send it to him.

I’m using Chad Miller’s (Blog|Twitter) invoke-sqlcmd2 to output the results as a data table (you’ll need that).

Finally a friendly reminder that unlike SQL Server, PowerShell persists variables so if you run this multiple times you’ll want to clear out the $dt variable like this: $dt=$null.

I have to tell you, this doesn’t get old:

I love that somebody on the other side of the planet is telling someone else that I ‘probably have a PowerShell script to solve their problem’. The funny thing is that a lot of the time I don’t have a script for the problem they’re trying to solve *yet*. I love it when someone gets referred to me and I don’t have a script because it gives me a chance to go explore the language and figure out something I don’t know. PowerShell makes this quest pretty fast.

Ironically Paul White ( blog | twitter ) didn’t know I had a script for that, he was just making a joke. Well, actually, I didn’t have a script for that. I did however know right where to find the person that did; and that is even better than if I had a script of my own. Why is it better that I had to point someone some where else? Could I not have figured this script out own my own? No that’s not it at all.

PowerShell is NOT a hammer it’s an Automation Language. Better yet PowerShell is a Toolset, and it happens to come with some pretty freaking awesome hammers built in. We all understand that SQL Server isn’t just a hammer, that it’s an entire toolset. Inside of that toolset is: T-SQL, SQL Agent, Profiler, SSIS, the SMO, SSRS, SSAS etc… just to name a few.

The cool thing about PowerShell is it’s actually built to work with other hammers. So the reason that you hear me talking about PowerShell all the time is not because I’m replacing my T-SQL Hammer, I’m just automating it! Like this, here’s a quick script to figure out how long your SQL 2008+ instances have been running:

<# Number of Days since the Instance has been restarted #>
$InstanceList = "WIN7NetBook", "WIN7NetBook\R2", "WIN7NetBook\SQLExpress"
foreach($Instance in $InstanceList)
{
Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS 'ServerName'
, DATEDIFF(D, Sqlserver_start_time, SYSDATETIME()) AS 'NumberOfDays'
  FROM sys.dm_os_sys_info" -ServerInstance $Instance -Database master
}

The automation doesn’t stop with SQL Server. You can find plenty of posts form people in our SQL Community on working with things like Red-Gate’s SQL Compare, Outlook, Active Directory, and Subversion just to name a few. The key here is that all of these people are automating something adjacent to their SQL world and they’re using PowerShell to make it happen.

Heck just last week I wanted to know how many sessions had been submitted to SQL Saturday #67 in Chicago. Do you think I went to the schedule page, copied the list of sessions into Excel and then checked to see what line number the last session was on to get my count?? Heck NO!! Last year I wrote a script for Allen Kinsel ( blog | twitter ) to count how many people at the PASS Summit had registered their twitter handle when the signed up. I took that script, changed the link, changed the term I was looking for and presto! I now know that 105 sessions have been submitted to SQL Saturday #67.

That has nothing to do with SQL Server right?!… Except for that whole bit about it being a SQL Saturday that I was interested in.

<#Start up a web client and download the web page into a text file#>
$url="http://www.sqlsaturday.com/67/schedule.aspx"
$file="c:\temp\DownloadSession$(((Get-Date).ToString("yyyyMMddHHmm"))).txt"
$webclient = New-Object system.net.webclient
$webclient.DownloadFile($url,$file)            

<#Sift through the file for lines that include "viewsession" #>
(Select-String -path $file -Pattern "viewsession").count

OK, so what’s your point?

My point is that I wouldn’t have even had the twitter script that I started with if Allen Kinsel hadn’t taken a chance and sent me an email at noon the Friday before the PASS Summit and asked me if I could use PowerShell to count the number of attendees with twitter handles.

Again, what’s your point?

My point is Use PowerShell to make one tool talk to another.

And…

And tell people faced with an annoying problem that you think I’ve got a script for that because whose knows, maybe I do!

When I do my presentations I have a set of scripts that have the name of the computer and instance I am working with.  I’m not a fan of using localhost or anything like that; I also like to use instance names that let people know what version of SQL that I’m using.  When I switch computers I spend less than a minute changing all of the names.  At the PASS Summit I used a laptop that I had just purchased; here’s the script I used to rename everything.

foreach ($SC in dir "$home\Documents\PoSh\" -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
(Get-Content $SC) | Foreach-Object { $_ -replace 'KILIMANJARO', 'R2' } | Set-Content $SC
}

To make this work for you just change out the highlighted parts above to whatever you need.  The part that says “$home\Documents\PoSh\” will go to the PoSh folder under your “My Documents” directory (If you don’t have one, now’s a good time ).  You can also use a path like C:\SQL\Databases\AdventureQuirks\ here.  The -replace ‘KILIMANJARO’, ‘R2′ portion seems pretty self explanatory; same goes for -recurse.

If you just wanted to search for all the files with a certain table name, column name, stored proc name, etc… and return a report (not to modify) you can use something like this:

#Make sure to navigate to the directory that you want to start looking in:
## 
cd c:\temp            

foreach ($SC in dir -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}

This piece of code is setup slightly different solely to demonstrate another way you can use this functionality.  Make sure that you navigate to directory that you want to search in first for this script.  When you get the results you may end up seeing the same filename listed more that once because the “pattern” appears in the file multiple times.  To make the results show each filename just once simple add -List.

One final option that I want to call out is -Filter.  If you want to search for only .SQL files in a directory (or .PS1, .txt, whatever) just add this: -Filter *.SQL

So you might end up using something like this:

foreach ($SC in dir C:\SQL\Databases\AdventureQuirks\ -recurse -Filter *.SQL | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}

Alright I better stop here before I start showing off some other features that I just learned.
Hope that helps!

To answer Paul Randal’s question of why I should get to win a week of training from SQLskills this is what I have to say:

Plain and Simple.  I’m a Second Generation DBA with over a decade of experience with SQL Server.  I want some deep level ideas for some new PowerShell scripts to simply tasks for DBAs everywhere.  I’ll also spend countless hours telling all my fellow DBAs here in Atlanta what all I learned until they can take it no more and demand this level of top-notch training for themselves! 

I’ll get started with the PowerShell scripts right now even.  This little script will search twitter for a giver hashtag, in this case: #SQLskills.  It will then save everything it finds off to an XML file and then query it:

$SearchHashtag = "sqlskills"            

$wc = new-object net.webclient
$url = "http://search.twitter.com/search.atom?q=%23$SearchHashtag"
$OutFile = "c:\temp\Download_$($SearchHashtag)_$([DateTime]::Now.ToString('yyyyMMddHHmmss')).xml"
$wc.DownloadString($url) | add-content $OutFile;            

[xml]$tweetxml = get-content $OutFile            

$tweetxml.feed.entry | Format-List            

($tweetxml.feed.entry).count            

Today we’re going to talk about formatting and storing our results.  Our storage mechanisms are going to be a simple flat file, CSV, and the obvious one, a table.  This post is a little long but it walks you through how to overcome some of the frustrations when I started working with PowerShell over a year ago.  Enough preamble, let’s get started!

Output on the screen:

Start from the beginning by running this little command:

Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBookNow depending on how wide your PowerShell window is open to right now PowerShell will format the data to spool out in the results pain the way it thinks is best.  It may have come out ‘one row at a time’:

Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBook | Format-ListOr it may have come out like this:

Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBook | Format-Table” so you’ll probably want to get into the habit of putting a “ | Format-Table”  after the end of you queries.  Or if you’re lazy like me:  | FT.

When we want to run this cmd (or whatever query you really want to run) against multiple databases we’re going to want to know what database that the data came out of.  We might write something like this

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{
Invoke-Sqlcmd -Query ' SELECT * FROM sysfiles'  -ServerInstance  Win7NetBook -Database $db.name | Format-Table
}

But the problem with that is We don’t know for sure which database each file came out of.  I mean if we have logical naming we can guess but that’s all it is, a guess.  What we can do is add our little “$db.name” that we’re using to pass in the name of our database.

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-Sqlcmd -Query ' SELECT * FROM sysfiles'  -ServerInstance  Win7NetBook -Database $db.name | Format-Table
}

Object Sidebar: Probably the most important thing for SQL people to know about this language!

Believer it or not we’re using object oriented code right now.  In this example we’re pumping a list of databases as objects into a variable and then iterating over that list.  If we examine our object by piping it over to Get-Member “$db | Get-Member” we’ll see that it only has one property but if we had selected more columns it would have had more properties.  Typically objects have more than one property so that’s why we need to add the .name to our $db variable so that we only get the name property.

Outputting to a text file:

In the PowerShell language to output to a text file we can just use “>” and then give it a filename.  Since we’re inside of a loop we would just keep overwriting that file until we got down to the last database and all that would be in the file would be the info from that last database.  We can also use “>>” which allows us to append to a file like this:  >> C:\temp\MyDatabaseFiles_20110120.txt

Side Note: In addition to the “>>” we could have also used Out-File –FilePath C:\temp\MyDatabaseFiles_20110120.txt –Append with much the same results.  To make a long story short, use Out-File instead of >> whenever possible.

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-Sqlcmd -ServerInstance  Win7NetBook -Database $db.name -Query ' SELECT * FROM sysfiles' | Format-Table >> C:\temp\MyDatabaseFiles_20110120.txt
}

That’s not what we expected was it?

There are two main problems with the output when we look at  MyDatabaseFiles_20110120.txt.  First, those database names are missing.  They’re missing because they weren’t in the pipeline in the first place.  Each time that we looped we were executing two different statements not just one; and the database name was only present in the first.  We could fix that by making the first line inside of the loop look like this $db.name >> C:\temp\MyDatabaseFiles_20110120.txt; but we won’t, at least not this time.

The second problem is that unless we have the editor open on a really, really wide monitor the output was likely truncated with some “…” at the end.  Now if we changed the output mode Format-List we might avoid that truncation problem but as database professionals “might” rarely cuts it.

Using Export-CSV to save off our results:

Funny story about Export-CSV later…

We’ll fire up a new variable called $MyResults and push our results into it each time that we pass through the loop (sorta~like a temp table but don’t make too strong of a connection to that).  After we complete the loop we will take the results and pass them down the pipeline to the Export-CSV cmdlet.  When we open that file we’ll see nice clean data output that we can save.

foreach ($db in Invoke-Sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
$MyResults += Invoke-Sqlcmd -Query " SELECT DB_NAME(db_id()) AS 'DatabaseName', * FROM sysfiles"  -ServerInstance  Win7NetBook -Database $db.name
}            

$MyResults | Export-CSV -Path C:\temp\MyDatabaseFiles_20110120.csv -NoTypeInformation

Output to a table:

I’ve talked about how to save rows to a table a couple of times before.  Do you remember that Wait Stats survey that Paul Randal ( blog | twitter ) did a little while ago?  Well, we have several hundred SQL instances where I work and I wanted to be able to send Paul results from a large number of those servers. The thing is I didn’t want to have to actually login to each instance, run the query, save off the results, then repeat over and over again.  So I came up with this post.  You can do the same to gather info multiple instances/databases in your environment.  It’s pretty straight-forward and very similar to the CSV example above but with a small pair of changes.  We need the two scripts that I mentioned in the other posts:  Chad Miller’s (Blog|Twitter)  invoke-sqlcmd2 and Write-DataTable functions.  Once we have those loaded (and hopefully their in our profile by now ) we’re off to the races being able to write information from multiple databases back into a single database.

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook)
{
$dt=invoke-sqlcmd2 -Query " SELECT DB_NAME(db_id()) AS 'DatabaseName', * FROM sysfiles" -ServerInstance  Win7NetBook -Database $db.name -As 'DataTable'
Write-DataTable -ServerInstance Win7NetBook -Database CentralInfo -TableName dbFileName -Data $dt
}

Here’s the SQL code to create that table so that you can test it out:

CREATE TABLE [dbo].[dbFileName](
[DatabaseName] [nvarchar](128) NULL,
[fileid] [smallint] NULL,
[groupid] [smallint] NULL,
[size] [int] NOT NULL,
[maxsize] [int] NOT NULL,
[growth] [int] NOT NULL,
[status] [int] NULL,
[perf] [int] NULL,
[name] [sysname] NOT NULL,
[filename] [nvarchar](260) NOT NULL
) ON [PRIMARY]

Output to Email:

Call me lazy but…

I put this next script together just to see if it could be done.  Going back to that Wait Stats Survey, if you read all the way to the end you’ll see I managed to get the collection process down to 3 lines.  The only thing is that you still had to do something to the result set to exclude your instance names for security, and then after that you still had to do the whole copy-paste thing.

To combat all that wasted time I came up with this script which will go ahead and enumerate the instances for you.  That way if for any reason Paul emailed you back and said ‘Holy Crap, how the hell did you manage to get that as your highest wait on Instnce #14?’ you’d still be able to go back and figure out which server he was talking about.  Also, If you would still have the results around in case you wanted to be proactive and take a look yourself (but let’s not get too carried away).

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | where {$_.Mode -ne "d"} )
{
$dt=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -InputFile ./PaulAndGlensWaitQuery.sql -As 'DataTable'
Write-DataTable -ServerInstance "Win7NetBook" -Database CentralInfo -TableName TopWaitTypes -Data $dt
}            

$MultipleResults = Invoke-Sqlcmd2 -ServerInstance "Win7NetBook" -Database CentralInfo -Query "SELECT DENSE_RANK() OVER (ORDER BY [InstanceName]) AS 'SQLInstance' ,[WaitType] ,[Wait_S] ,[Resource_S] ,[Signal_S] ,[WaitCount] ,[Percentage] FROM [CentralInfo].[dbo].[TopWaitTypes] ORDER BY SQLInstance, Percentage" | ConvertTo-Html -Property SQLInstance, WaitType, Wait_S, Resource_S, Signal_S, WaitCount, Percentage | Out-String;
Send-MailMessage -To paul@SQLskills.com -Subject "Wait Stats Query Results" –From YourEmailAddress@GoesHere.com -SmtpServer smtp.SQLvariant.com -Body $MultipleResults -BodyAsHtml

Finally, you know we could put that SELECT statement into a .sql file.  That would cut it back down to ~6 lines.  We might even be able to wrap the email portion into something a little smaller but considering all that this script does I think this is good enough.

It’s Later

When I first tried to do something like this I was was shocked to find out that there is no –Append switch for Export-CSV.  I wasn’t very happy about that, I mean I couldn’t wrap my head around how you could leave something like that out.  This ranked right up there with finding out Utility Control Point only worked for managing other SQL 2008 R2 instances.  Luckily my story ended much better Tom’s.  First I reached out to ScriptingGuys ( blog | twitter ) to make sure that I wasn’t crazy.  Then I did all I could do, sulked and filed a Connect Item.  The following day I woke up to the sun was shining, the birds chirping, and a tweet from Dmitry freaking Sotnikov himself ( blog | twitter ) telling me that he had gone ahead and fixed that for me by building a proxy command.  (It’s a function and since functions out rank cmdlets in call order anyone can augment the PowerShell language by adding a proxy command.)

…  and THAT is why I love PowerShell.

Homework

Use the 3 techniques we walked through to run sp_configure against as many instances as possible.  Save it to a text file, CSV, and a table.  You might even want to add a date column or something.  You could also try email the results to yourself.

Do Not spam Paul Randal!  I cleared the email script with Paul before I included it.  If you want to send Paul the wait stats results from your servers I recommend that you test it first by emailing it to yourself.  If it looks good you go ahead and send it but make sure to include your email address in the –From parameter.

We’re going to cover 3 different sources for our loop that are the most common for DBAs to use: table, text file, Registered Server/Central Management Server.  After that we’re going to do a double loop and then it’s time for you to find something to do with these.

Text File

In this chunk of code we’re just going to read from a simple text file from our local hard drive and then loop through the instances in that file one at a time.  You put one instance on each line of the text file.  Don’t put it in quotes unless you’re using a non-standard port number.  This is the easiest method because each “row” that comes out of the text file only has one property.  We’ll find out why that’s important in the next example.  In the meantime setup your text file and test it by running just this: Get-Content C:\PowerShell\AllInstances.txt.

foreach ($Instance in Get-Content C:\PowerShell\AllInstances.txt)
{
$Instance;
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  object_name ,
        counter_name ,
        instance_name ,
        cntr_value ,
        cntr_type
  FROM sys.dm_os_performance_counters"
}

Database Table/ Query

In this example we could be reading rows out of a table or running a more complex query to determine the list of databases that we want to run out query against.  This may actually seem easier to most database people and it is.  We’ve got a centrally located table and we can just look at it and know that we can change the first query to select a list of databases from somewhere else.  But there’s a really important thing to know if you swap out the query.   This approach is sending one usable property (column) to the foreach loop and it’s called “name”.  If you change the query and the column ends up being called database_name, you’re going to have to change $($db.name) to be called $($db.database_name).  Otherwise you’re going to loose a lot of hair and get really ticked after about 20 minutes like I did!

foreach ($db in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-sqlcmd -Query 'SELECT *
  FROM sys.dm_exec_procedure_stats' -ServerInstance  Win7NetBook -Database $($db.name)
}

Registered Servers/ Central Management Server

Before you get started looping through your Registered Servers you’ll need to run this: Import-Module Agent if you want to do this exact example.  What we’re looking for here is all the jobs that have failed in the last 3 days in our “QA” group of servers.  This example should be easy enough for everyone to tweak on their own.  If you get stuck just remember to do Get-Help -Full Set-AgentJobHistoryFilter
(Huge thanks to Chad Miller for helping me put together this demo so that it would be a fast one to run!)

$filter = Set-AgentJobHistoryFilter -startDate $(get-date).AddDays(-3) -endDate $(get-date) -outcome 'Failed'

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
Get-AgentJobHistory $RegisteredSQLs.ServerName $filter | where-object {$_.StepID -ne 0}
}

Double Loop

This has to be one of my favorite PowerShell scripts of all time (so far).  I had to run a query against every database in a group of over 10 servers.  I’ve changed this one around a little but I’m sure you’ll find a use for it!  (Think permissions.)

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
    foreach ($DBName in invoke-sqlcmd -query "SELECT name
  FROM sys.databases WHERE name in ('AdventureWorks',
            'AdventureWorks2008',
            'AdventureWorks2008R2',
            'AdventureWorksDW'
            ) " -database master -serverinstance $RegisteredSQLs.ServerName )
            {
                    invoke-sqlcmd -query 'SELECT *
        FROM sys.dm_db_index_usage_stats' -ServerInstance $RegisteredSQLs.ServerName -database $DBName.name
            } #EndOfTheFoundDatabasesLoop
} #EndOfTheRegisteredServerLoop

Homework

Try out each of these methods and think up something you could use this for.  If you hit on something that save you some clicking around in SQL Management Studio please mention it in the comments.

So far we’ve walked though how to turn on PowerShell and add modules, adding SQL Server 2008 snapins and using PowerShell variables, what cmdlets and functions are, what providers are and how to work with methods & properties.

Profiles

Today we focus on load things in our profile so that they are always available in PowerShell.  First, some basics:

What is a Profile?  A profile is nothing more than a PowerShell script that gets run as soon as you open up a given PowerShell window.

Why would we want to do this?  I use PowerShell almost exclusively for automating work with my SQL Servers.  When I’m ready to do something I don’t want to start by reloading the same toolset every time I open it. I want to click the icon and be ready to go.  The profile saves me from this repetitive loading.  I update my profile as I find cool new scripts that I want to add.

You are not limited to using a single profile in PowerShell.  Personally, I use one of three profiles depending on the task at hand.  Here is how that works. I use the PowerShell ISE or other third party products as my primary way of writing PowerShell scripts (.ps1) and rarely use the “console.”   I only use the console if I need to troubleshoot something that might have to do with STA/MTA. 

When I open up the PowerShell ISE it’s uses it’s own profile CurrentUserCurrentHost that is stored in C:\Users\USERNAME\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1 (picture-right).  Inside of that file on my machine it currently looks like this:

import-module ISEPack
cd C:\Users\Aaron\Documents\PoSh\Load
. ./invoke-sqlcmd2.ps1
. ./Write-DataTable.ps1
. ./Out-DataTable.ps1
. ./Add-SqlTable.ps1
. ./Export-CSV-Append.ps1
. ./invoke-sqlcommand.ps1
. C:\Users\Aaron\Documents\PoSh\Tasks\Get-DisksSpace.ps1
cd c:\temp\

Whatever is in this file only gets loaded into my session when I open the ISE.  Nothing above gets run when I open up the console.  These are items I use in ISE, but not in the console PowerShell window.

There are items I use in both ISE and the console.  These items live in a “super profile” of sorts since I want these items no matter how I access PowerShell CurrentUserAllHosts (which is located in C:\Users\USERNAME\Documents\WindowsPowerShell\profile.ps1). Profile.ps1 (picture-center) gets run every time I open up any PowerShell “host” including 3rd party tools.  This also includes the SQLPS host which may cause errors messages to appear when you open it or run a SQL Agent Job.  These error messages don’t impact a PowerShell step in a SQL Agent Job from running.

Here’s what I have inside of my Profile.ps1 file.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Import-Module SQLServer

Whenever I open the ISE both the Microsoft.PowerShellISE_profile.ps1 and the Profile.ps1 scripts get run.

PowerShell.exe (the little blue “host”) has it’s very own CurrentUserCurrentHost profile that it loads.  That’s the exact same name as the profile that we talked about the ISE loading so does that mean that it’s loading that same file?  No.  Each one is configured to load it’s own CurrentUserCurrentHost from a different location.  So if there are items I want in console world only I have a place to store them thanks to C:\Users\USERNAME\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1.  Since I rarely use the console my profile there sat empty until recently.  I only added items after I spotted a post by Oisin Grehan ( blog | twitter ) on Implementing a Matrix-Style Console Screen Saver and I HAD to try it.  This only works in the console and not in the ISE so it made my console profile the perfect place for implementing this.  Now my PowerShell_profile.ps1 file (picture-left) looks like this:

Import-Module screensaver
set-screensavertimeout (new-timespan -minutes 5)
enable-screensaver
Start-ScreenSaver

To edit one of the profiles that only load for the console or the ISE all you have to do is open them up and run notepad $profile.  It will figure out which file is the right one for that environment and open it for you.  To edit the profile that is run in every PowerShell environment just run notepad $profile.CurrentUserAllHosts.  If your WindowsPowerShell directory isn’t there it will not automatically create that for you and you will get an error message stating that it “can’t find the specified path“.  I’ve written a little script to check for that and create one for you if it’s not already there:

$myPowerShell = $HOME
$myPowerShell +="\Documents\WindowsPowerShell"
if ((Test-Path $myPowerShell) -eq "true") {"$myPowerShell is already there mate"}
else { "you need to make one"
mkdir $myPowerShell  }

Homework Assignment:

Setup one of your profiles to at least have one of the modules from the SQLPSX project and the SQL Server Snapins loaded up every time you start up the PowerShell ISE. Which profile to use is completely up to you.  Feel free to play around with your choices.  Optionally you can also include the Get-DisksSpace function like I do; but you don’t have to.

Let me know how this goes for you by dropping a note it the comments.  In tomorrow’s post we are going to work with multiple SQL Servers so you’ll need these things up and running for that.

If you’re confused by where your files are you can just run this script that I lifted off of the HeyScriptingGuys site to have it tell you where all of the profiles for your session have been loaded from.  Keep in mind that we’re not touching the ones in the Windows directory.

$profile |

Get-Member -MemberType note* |

Format-Table -Wrap -AutoSize -Property name,

@{ Label=“Path”; Expression={$_.definition -replace “System.String”,“”} }

In Case you would like more to play with…

There is a lot more that can be done with PowerShell Profiles but these are the basics you need to get up and running. If you want to dive deeper check out these posts over on the Hey Scripting Guy Blog.  Go here to download the SQLServer or ISEPack modules; they are both part of larger projects.  If you’d like some of the scripts that I reference in my profile you can grab the Get-DisksSpace one here and I’ll be blogging about the rest later this week.

Be Advised:

Everything that you put into you profile eats up RAM.  Thankfully most scripts are only a few kilobytes.  Keep in mind that the more items you add into your profile the longer that it is going to take to load.

In case you’re wondering if that “Matrix Style Console Screen Saver” is worth having around, here is what it looks like after a few minutes:

That’s all that I’ve got for you today, see you back tomorrow!

Mike says that we’re going to “Square off” but I heard the new SSIS has pretty new rounded corners.  I don’t know about you but that a feature that I’ve been waiting a loooong time for.  I bet he’s going to be doing a lot of talking about what might be in the Denali version of SSIS but I’ll be showing you what 4 or 5 lines of PowerShell can do for you today!  So if you’re looking for something to do around 2:45 Saturday afternoon come see what PowerShell can do for you!

So I just wanted to encourage everyone who makes their living off of SQL Server to take a minute to add these two RSS feeds to your favorite feed-reader. 

https://connect.microsoft.com/rss/68/RecentFeedbackForConnection.xml

https://connect.microsoft.com/rss/68/RecentlyModifiedFeedbackForConnection.xml

If you haven’t been on Connect for a while and wonder what’s out there, well I’ve got a PowerShell script for you  

Invoke-Sqlcmd -ServerInstance denali.db.5026258.hostedresource.com `
-Database denali -Username denaliconnect -Password Wide0pen -Query "
SELECT ID
        , Title
        , ItemStatus
        , UpVoteCount
        , DownVoteCount
        , ItemDescription
        , Author
        , Modified
        , ValidationCount
        , WorkAroundCount
        , URL
        , OpenedDate
  FROM denali.dbo.ConnectItems" | out-gridview

OK, you can totally take that login info and use your SQL Management Studio to connect to this database too but I wanted to show you a REALLY cool use for the Out-GridView command.  When run the PowerShell script and the window pops up Just Click on + Add Criteria > ItemDescription > Add.

Like this:

Then just type in a search term like “SSIS” and see what you get back…

Rob has also put together a way to search the data that is much better and where we hope to ultimately go with it.  Again, that’s all for another post.

It’s a huge honor to get the chance to be a guest blogger on Hey Scripting Guy! and I hope these posts were able to make some people’s (o.k. A Lot of People’s) lives easier. Given the number of people checking out PowerShell for the first time based on the comments to my MCM Videos download script I figured I’d call these out today:

Use PowerShell to Obtain SQL Server Database Sizes

Use PowerShell to Change SQL Server Service Accounts

Use PowerShell to Script SQL Database Objects

I know that the Hey Scripting Guy! blog is the #1 blog on TechNet but I’m not allowed to know any specifics beyond that. I did however catch this in yesterday’s post: “In general, the articles we published on SQL Server and on SharePoint have been really popular.” So hopefully that means we’ll get to hear some more SQL voices there in the near future. Maybe even in time for SQL Rally, who knows?

Learn some of the quirks and capabilities of SSIS. Learn about the pitfall of “parallel path choicing”. Use the Script Component as a data source (to solve complex sourcing issues) and as a data destination (to get around the identity column conundrum). See how to use Script Tasks to build directory scanners for efficient file import techniques. We’ll also go over some the tunable properties of the Data Flow object.

Josef Richberg
Josef is a DBA for HarperCollins Publishers working with SQL Server and SSIS.  He has over 17 years’ experience designing, building, and tuning SQL Server.  Josef was the recipient of the ’2009 Exceptional DBA’ award and actively blogs at http://josef-richberg.squarespace.com.

How do I view the presentation?
Attendee URL:  Live Meeting link

To get a very fast answer to this answer I opened up PowerShell.exe because while I normally demo in PowerShell_ISE.exe, I don’t use a profile in PowerShell.exe (more on that in a later post).  I ran this command to get the count of just the cmdlets.

(get-command -CommandType Cmdlet).count

Then I fired up SQLPS.exe (typically found in C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn) and ran the same command (after switching out of SQL Server and over to the C drive; no idea why that was necessary).

The only problem here is that it’s not really 236 – 130 = 106 because SQLPS.exe adds 5 cmdlets that I made sure to exclude (along with everything else I normally use) from PowerShell.exe. 

To get a more accurate list of the cmdlets and which one has what I decided to write a script that everyone con run on their own machine.  One of the many cool features of PowerShell is that you call call it’s executable and pass it a -command or even a –file.  I went ahead and threw together this simple command below so that you can see the differences yourself.

Note: I assume you are running Windows 7 which comes with PowerShell 2.0

$SQLPS=SQLPS.exe -command "CD C:\; get-command -CommandType Cmdlet | select CommandType, Name; exit";
$PSTwo=powershell.exe -command "get-command -CommandType Cmdlet | select CommandType, Name; exit";

Compare-Object -ReferenceObject $SQLPS -DifferenceObject $PSTwo|
Export-CSV C:\temp\SQLPSCommandDifferences.csv -NoTypeInformation

Now all that you have to do is open up that csv file that was just created (C:\temp\SQLPSCommandDifferences.csv) and have a look. 

Note: I could have just displayed the info inside of my PowerShell window by leaving off the last line and the pipe but then I would have needed to do something to format the output so I went with the CSV file instead.

For more information on this topic be sure to check out Chad Miller’s post on it.

Onto the boring part:
I put a lot of work into that session and even on the day of the event I was still second-guessing what scripts to put in and which to leave-out. Thankfully fellow [PowerShell] speaker Trevor Barkhouse ( blog | twitter ) sat through my entire presentation that morning and helped me carefully tweak the sequence of scripts. I had the 4th most well attended session of the 186 sessions at the PASS Summit on the official scoring list I was sent! My scores were nothing short of amazing considering I had never spoken on a stage this large before. Even if you leave the whole first-time-at-Summit-speaker thing out I still scored just above the midpoint for all speakers at the Summit. Not half bad for a n00b <—Literally!

I am not a professional speaker. In fact I have been speaking for less than a year still. I had a time budget in preparation and I had to make trade offs. I decided to focus vastly more time on demos and script flow than slides or things like inserting jokes in the session. I’ll definitely work to improve the things that people comment on and I’ll share those in a later post (short on time today).

The Scores:

Time allotted: Based on the comments from people who came up and talked to me afterwards at the Summit, they would have loved for me to have kept going for another 20 minutes. I don’t think Adam Machanic ( blog | twitter ) would have appreciated that much but I sure had the material for it!

Presentation skills:I will definitely work on upping my presentation skills but this is the toughest competition I’ve ever been up against so not half bad!

Quality of the materials: This may have been lowered because of the many A/V related comments I received. Or maybe refining my presentation skills will help this too.

Usefulness in your day-to-day environment: is the entire reason I put the presentation together in the first place. My goal for this particular score is not a number but a note from the organizers mentioning the shocking number of people who scratched out the choices and wrote in a number above the range. I will not let people down the next time

Accuracy of title: I have some ideas on how to tweak the abstract a little but I’m happy with this score.

Speaker’s knowledge: I’m happy with this score. I’m sure Adam (who was speaking right after me) got nothing but 5s on this score. Considering the company, my score was stellar. I will not spend any time thinking about let alone trying to improve this score.

I’ll post the comments I received in a separate post. Gotta run.

Oh btw… that table up there. Generated if off of an Excel spreadsheet using a single line of PowerShell code! (I’ll blog that soon, promise.)

If you’d like more training than just the videos SQLskills has some classes that you can attend.

If you’re like me you’ve either already downloaded the videos or you’re not even reading this right now because you clicked the link and just started downloading the videos.    Well if you don’t want to spend the rest of your day downloading all of those videos by hand I’ve got a little PowerShell script I think you’ll enjoy.

$wc = new-object net.webclient
[regex]$regex ="(?<url>http://download.microsoft.com/download/./././[0-f]{8}-[0-f]{4}-[0-f]{4}-[0-f]{4}-[0-f]{12}/(?<file>[^>]*?wmv))"
[xml]$xml =  $wc.DownloadString("http://www.microsoft.com/feeds/TechNet/en-us/How-to-videos/SQL_Server_2008_Microsoft_Certified_Master_(MCM)_Readiness_Videos.xml")
$xml.rss.channel.item | foreach {
    if ($wc.DownloadString($_.link) -match $regex)
    {
        $url = $matches.url
        $file =  "$home\Videos\$($matches.file)"
        if (Test-Path $file)
        {Write-Host "$file is already there mate"}
        else {
            Write-Host "Downloading $file"
            $wc.DownloadFile($url,$file)
        }
    }
}

This script will check the RSS feed, connect to all of the links in it, find the first .wmv link that it comes across, then copy that file to your videos directory in your documents folder IF it’s not already there.  If you have to stop this script that’s fine, it will figure out which videos have already been downloaded and skip them*.  If you want to try downloading the .MP4 files instead, just swap out .wmv for .mp4 and you should be all set.  A warning about that though; they seem to be a little more than twice the size of the .wmv files

*This script is written for Windows 7 and maybe for Windows Vista.  You’ll have to edit it yourself for Windows XP or just bug Nic Cain to post one
**IMPORTANT:  I think I have fixed the HTML rendering issues for the code block but you may need to download the script here.

PowerShell First Timers!

First off, welcome to the best addiction that you will ever have!  Since so many people are checking out this post and firing up PowerShell for the first time, I’ve gathered together some useful links.  Here’s one from the ScriptingGuys themselves: How Do I Install PowerShell on Windows 7 and Other Questions 

I have a series of posts you may want to look at to help you get up & running as well as a video I did a while back for MSDN’s geekSpeak:
PowerShell Week Post 0 (5/17)
PowerShell Week Post 1 (5/18)
PowerShell Week Post 2 (5/20)
PowerShell Week Post 3 (5/20)

I’ll blog more later this week about how I put together the script but I wanted to get it into people’s hands ASAP.  Special Thanks go out to Jeremiah Peschka ( blog | twitter ) for not laughing too hard at my first ever attempt at a RegEx as well as Nicolas Cain ( blog | twitter ) & Chad Miller (Blog|Twitter) for giving me pointers on how to use my new RegEx hammer inside of PowerShell.