http://www.sqlservercentral.com/blogs/sqlvariant/2011/02/23/emailing-tempdb-query-results-to-paul-randal-with-powershell/

Printed 2014/08/28 09:18AM

Emailing tempdb Query Results to Paul Randal with PowerShell

By Aaron Nelson, 2011/02/23



imageI 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.  Smile 

$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 Surery Results" `
 –From YourEmailAddress@GoesHere.com `
 -SmtpServer YourSMTPServerGoesHere `
 -Body $MultipleResults -BodyAsHtml

Hopefully this will help you email query results to your heart’s content :-)

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.