In case you’re in need of some images to help us promote the largest PASS event on the East Coast this year, I’ve gathered some together for you For all you chapter leaders I’ve also included a flyer that you can print out and hand out at your user group meeting or SQL Saturday. You can right-click save-as on these or just grab this zip with everything. 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
February, 2011:
Emailing tempdb Query Results to Paul Randal with PowerShell
I was referred to someone on twitter today who wants to email query results without setting up database mail. I explained this in Post 6 of PowerShell Week at SQL University but that example was somewhat complicated. Instead, I thought I’d whip up a new example using Paul’s latest survey. Paul want’s to know know how many cores your instances have and how many data files that tempdb has. 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.
PowerShell Interfaces with Other Hammers
This month’s installment of T-SQL Tuesday is hosted by Pat Wright (blog | twitter). Pat says: “So the topic I have chosen for this month is Automation! It can be Automation with T-SQL or with PowerShell or a mix of both. Give us your best tips/tricks and ideas for making our lives easier through Automation.” 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!
Recursive Find and Replace Your SQL Files with PowerShell
This is just a quick blog to help out with something I saw discussed on twitter yesterday. 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!