Yesterday we went over some loop constructs to get information out of SQL Server. While getting the information out is great, as Data Professionals our very next concern is going to be: “ok, where do I put this so that I can refer to it later?”
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:tempMyDatabaseFiles_20110120.txt
Side Note: In addition to the “>>” we could have also used Out-File –FilePath C:tempMyDatabaseFiles_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:tempMyDatabaseFiles_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:tempMyDatabaseFiles_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:tempMyDatabaseFiles_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,
[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.
