SQLServerCentral / Programming / Powershell / Get SQL Query Results as E-Mail / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 15:27:55 GMT20RE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxSee this:[url=http://stackoverflow.com/questions/11263538/powershell-display-table-in-html-email]http://stackoverflow.com/questions/11263538/powershell-display-table-in-html-email[/url]and [url=http://lukieb.wordpress.com/2011/03/03/powershell-script-to-send-sql-server-query-results-via-e-mail/]http://lukieb.wordpress.com/2011/03/03/powershell-script-to-send-sql-server-query-results-via-e-mail/[/url]What you should note here is that these blog links shared here have defined their own mail snippet to send the result, alternatively, you can also use the Send-MailMessaage cmdlet to email the result set. Which is nothing but an encapsulation over the existing code.PS Prompt> help Send-MailMessageWed, 09 Jan 2013 21:36:57 GMTRaunak JhawarRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspx2.0Wed, 09 Jan 2013 11:18:06 GMTjvkondapalliRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxWhat is the version of powershell you are using?Tue, 08 Jan 2013 23:23:05 GMTRaunak JhawarRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxI use the following code (inside a scheduled job) to extract the data and email it in HTML table format:(disclaimer - this is my version of similar code that was found online)[code="sql"]-- ************************************************************************************************-- Returns a list of scheduled jobs that ran the previous day, and includes:-- status (success or failure), last run date and name of scheduled job-- ************************************************************************************************DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)-- get scheduled job data ------------------------------------------------------SET @xml =CAST(( SELECT -- status LTRIM(RTRIM(CASE WHEN jh.run_status = 1 THEN 'Succeeded' Else 'Failed' END)) AS 'td' ,'' -- formatting spacer -- last run date ,LTRIM(RTRIM(RIGHT(jh.run_date,2) +' '+ DATENAME(MONTH, CONVERT(DATETIME, LEFT(jh.run_date,4) +'-'+ RIGHT(LEFT(jh.run_date,6),2)+'-'+ RIGHT(jh.run_date,2), 102))+' '+ LEFT(jh.run_date,4) )) AS 'td' ,'' -- formatting spacer -- job name ,LTRIM(RTRIM(j.[name])) AS 'td' FROM msdb..sysjobhistory jh INNER JOIN msdb..sysjobs j ON j.job_id = jh.job_id INNER JOIN msdb..sysjobschedules js ON j.job_id = js.job_id WHERE run_date >= DATEPART(YEAR, GETDATE()-1)*10000+ DATEPART(MONTH, GETDATE()-1)*100+ DATEPART(DAY, GETDATE()-1) -- yesterday's scheduled jobs AND step_id = 0 ORDER BY jh.run_status ,[name] ,j.job_id , jh.step_idFOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))-- highlight any failed jobs with red background ------------------------------SELECT @xml = REPLACE(@xml,'<td>Failed</td>','<td bgcolor=#FF3333>Failed</td>')-- get name of SQL Server -----------------------------------------------------DECLARE @server VARCHAR(5)SELECT @server = [server] FROM msdb..sysjobhistory-- send email with results in table format ------------------------------------SET @body ='<html><H1><FONT color="blue">'+ @server +' - Scheduled Job Status</FONT></H1> <body bgcolor="white"> <table bgcolor=#CCFFCC border = 1> <tr bgcolor=#99FFCC> <th>Last Run Status</th> <th>Date Last Run</th> <th>Job Name</th> </tr>' SET @body = @body + @xml +'</table></body></html>'EXEC msdb.dbo.sp_send_dbmail@recipients =N'admin@xyz.com' -- for multiple reipients, use ";" as a separator,@body = @body,@body_format ='HTML',@subject ='Scheduled Jobs Status',@profile_name ='myMailProfileName'[/code]hope it's useful :-)Mon, 19 Nov 2012 16:02:56 GMTIvanna NohRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspx[quote][b]jvkondapalli (11/17/2012)[/b][hr]Thank your for appreciating my code!!Your suggestions for using sp_dbmail can only work if I open a new connection to SQL. I have written something which will avoid it. Here is how:[code="other"]#Set-ExecutionPolicy RemoteSigned############################################################################ Declare ServerName, DatabaseName and TableName to Get Server List###########################################################################$SourceServerName = '*******'$SourceDatabaseName = 'dbadb'#$SourceTablename = 'LookUp_ServerList_NonProd'############################################################################ Declare Variables for Sending E-mails###########################################################################$ToRecipient = "******@email.com"$From = "******@email.com"$SMTPServer = "smtp.email.com"$GetDate = get-date -format g############################################################################ Create SqlConnection object and define connection string###########################################################################$SQLCon = New-Object System.Data.SqlClient.SqlConnection$SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"############################################################################ Create SqlCommand object, define command text, and set the connection###########################################################################$SQLCmd = New-Object System.Data.SqlClient.SqlCommand$SQLCmd.CommandText = "SELECT SQLServerInstanceNameFROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNPINNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNPON [LUSSILNP].[ServerID] = [LUSLNP].[ServerID]WHERE [SqlPingFlag] = 1"$SQLCmd.Connection = $SQLCon############################################################################ Create SqlDataAdapter object and set the command###########################################################################$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlDataAdapter.SelectCommand = $SQLCmd############################################################################ Create and fill the DataSet object###########################################################################$DataSet = New-Object System.Data.DataSet$SqlDataAdapter.Fill($DataSet, "SQLServerInstanceName") | Out-Null############################################################################ Close the connection###########################################################################$SQLCon.close()############################################################################ Function for sending E-mails to ******@email.com###########################################################################Function SendEmail{ #param($strTo, $strFrom, $strSubject, $strBody, $smtpServer) param($To, $From, $Subject, $Body, $smtpServer) $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = $From $msg.To.Add($To) $msg.Subject = $Subject $msg.IsBodyHtml = 1 $msg.Body = $Body $smtp.Send($msg)}############################################################################ Declare Database and query###########################################################################$SQLDatabaseName = "msdb"$SQLQueryText = "SELECT [S2].[name], [dbo].[agent_datetime]([run_date] , [run_time]) AS RunDateFROM dbo.[sysjobhistory] AS SINNER JOIN dbo.[sysjobs] AS S2ON [S].[job_id] = [S2].[job_id]WHERE [dbo].[agent_datetime]([run_date] , [run_time]) >= GETDATE() - 1 AND ([run_status] IN ( 0 , 3 ) AND [S].[step_id] = 0)"################################################################################################### Iterate through the dataset to Run the query on Remote Server and send Results as E-Mails##################################################################################################foreach ($row in $DataSet.tables["SQLServerInstanceName"].rows){ $DestinationServerName = $row.SQLServerInstanceName #RunSQLQuery -SQLServerName $DestinationserverName -SQLDatabaseName $SQLDBName -SQLQuery $SQLQueryText $SQLConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $DestinationServerName; Database = $SQLDatabaseName; Integrated Security=true" $SqlCommand = New-Object System.Data.SqlClient.SqlCommand $SqlCommand.CommandText = $SQLQueryText $SqlCommand.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCommand $SQLDataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($SQLDataSet) | Out-Null $SqlConnection.Close() $CountRows = $SQLDataSet.Tables[0].Rows.Count################################################################################################# Following code is for prepping HTML E-Mail Message############################################################################################### $table = $SQLDataSet.Tables[0] $col1 = New-Object system.Data.DataColumn SQlJobName, ([string]) $col2 = New-Object system.Data.DataColumn SQLRunDate, ([string]) $table.columns.add($col1) $table.columns.add($col2) foreach($row2 in $SQLDataset.tables["Name"].rows) { $row3 = $table.NewRow() $table.Rows.Add($row3) } # Create an HTML version of the DataTable $html = "<table><tr><td>SQLJobName</td><td>JobRunDateTime</td></tr>" foreach ($row4 in $table.Rows) { $html += "<tr><td>" + $row4[0] + "</td><td>" + $row4[1] + "</td></tr>" } $html += "</table>" $HTMLmessage = @"<font color=""black"" face=""Arial, Verdana"" size=""3""><h3>Following are the Failed SQL Jobs</u></h3><style type=""text/css"">ol{margin:0;padding: 0 1.5em;}table{width:850px;}thead{}thead th{padding:1em 1em .5em;border-bottom:1px dotted #FFF;font-size:120%;text-align:left;}thead tr{}td{padding:.5em 1em;}tfoot{}tfoot td{padding-bottom:1.5em;}tfoot tr{}#middle{background-color:#900;}</style><body BGCOLOR=""white"">$html</body>"@################################################################################################# If Any Failed Jobs Exists - Then Send and E-mail############################################################################################### IF($CountRows -ne 0 ) { $DestinationServerName $Subject = "$DestinationServerName - Failed SQL Jobs - $GetDate" #$Body = $TableResults #$body = $HTMLmessage SendEmail -To $ToRecipient -From $From -Subject $Subject -Body $HTMLmessage -BodyASHTML -Auto Name -smtpServer $SMTPServer }}[/code]Please suggest if there is any other efficient way of doing this.Thanks[/quote]No, no... sorry for the confusion. I wasn't suggesting to use sp_SendDBMail. There's a T-SQL technique for easily converting a result set to a pretty HTML table in the last example of sp_SendDBMail that will work for anything! ;-)Mon, 19 Nov 2012 05:56:08 GMTJeff ModenRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxThanks for your suggestions. will edit my script to use that cmdlet instead of my send-email function.Thanks again!Sun, 18 Nov 2012 10:40:11 GMTjvkondapalliRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxInstead of creating your own function to send e-mails, have you checked out the send-mailmessage cmdlet? You can send out your message body as html.Sun, 18 Nov 2012 03:27:48 GMTJoie AndrewRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxThank your for appreciating my code!!Your suggestions for using sp_dbmail can only work if I open a new connection to SQL. I have written something which will avoid it. Here is how:[code="other"]#Set-ExecutionPolicy RemoteSigned############################################################################ Declare ServerName, DatabaseName and TableName to Get Server List###########################################################################$SourceServerName = '*******'$SourceDatabaseName = 'dbadb'#$SourceTablename = 'LookUp_ServerList_NonProd'############################################################################ Declare Variables for Sending E-mails###########################################################################$ToRecipient = "******@email.com"$From = "******@email.com"$SMTPServer = "smtp.email.com"$GetDate = get-date -format g############################################################################ Create SqlConnection object and define connection string###########################################################################$SQLCon = New-Object System.Data.SqlClient.SqlConnection$SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"############################################################################ Create SqlCommand object, define command text, and set the connection###########################################################################$SQLCmd = New-Object System.Data.SqlClient.SqlCommand$SQLCmd.CommandText = "SELECT SQLServerInstanceNameFROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNPINNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNPON [LUSSILNP].[ServerID] = [LUSLNP].[ServerID]WHERE [SqlPingFlag] = 1"$SQLCmd.Connection = $SQLCon############################################################################ Create SqlDataAdapter object and set the command###########################################################################$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlDataAdapter.SelectCommand = $SQLCmd############################################################################ Create and fill the DataSet object###########################################################################$DataSet = New-Object System.Data.DataSet$SqlDataAdapter.Fill($DataSet, "SQLServerInstanceName") | Out-Null############################################################################ Close the connection###########################################################################$SQLCon.close()############################################################################ Function for sending E-mails to ******@email.com###########################################################################Function SendEmail{ #param($strTo, $strFrom, $strSubject, $strBody, $smtpServer) param($To, $From, $Subject, $Body, $smtpServer) $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = $From $msg.To.Add($To) $msg.Subject = $Subject $msg.IsBodyHtml = 1 $msg.Body = $Body $smtp.Send($msg)}############################################################################ Declare Database and query###########################################################################$SQLDatabaseName = "msdb"$SQLQueryText = "SELECT [S2].[name], [dbo].[agent_datetime]([run_date] , [run_time]) AS RunDateFROM dbo.[sysjobhistory] AS SINNER JOIN dbo.[sysjobs] AS S2ON [S].[job_id] = [S2].[job_id]WHERE [dbo].[agent_datetime]([run_date] , [run_time]) >= GETDATE() - 1 AND ([run_status] IN ( 0 , 3 ) AND [S].[step_id] = 0)"################################################################################################### Iterate through the dataset to Run the query on Remote Server and send Results as E-Mails##################################################################################################foreach ($row in $DataSet.tables["SQLServerInstanceName"].rows){ $DestinationServerName = $row.SQLServerInstanceName #RunSQLQuery -SQLServerName $DestinationserverName -SQLDatabaseName $SQLDBName -SQLQuery $SQLQueryText $SQLConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $DestinationServerName; Database = $SQLDatabaseName; Integrated Security=true" $SqlCommand = New-Object System.Data.SqlClient.SqlCommand $SqlCommand.CommandText = $SQLQueryText $SqlCommand.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCommand $SQLDataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($SQLDataSet) | Out-Null $SqlConnection.Close() $CountRows = $SQLDataSet.Tables[0].Rows.Count################################################################################################# Following code is for prepping HTML E-Mail Message############################################################################################### $table = $SQLDataSet.Tables[0] $col1 = New-Object system.Data.DataColumn SQlJobName, ([string]) $col2 = New-Object system.Data.DataColumn SQLRunDate, ([string]) $table.columns.add($col1) $table.columns.add($col2) foreach($row2 in $SQLDataset.tables["Name"].rows) { $row3 = $table.NewRow() $table.Rows.Add($row3) } # Create an HTML version of the DataTable $html = "<table><tr><td>SQLJobName</td><td>JobRunDateTime</td></tr>" foreach ($row4 in $table.Rows) { $html += "<tr><td>" + $row4[0] + "</td><td>" + $row4[1] + "</td></tr>" } $html += "</table>" $HTMLmessage = @"<font color=""black"" face=""Arial, Verdana"" size=""3""><h3>Following are the Failed SQL Jobs</u></h3><style type=""text/css"">ol{margin:0;padding: 0 1.5em;}table{width:850px;}thead{}thead th{padding:1em 1em .5em;border-bottom:1px dotted #FFF;font-size:120%;text-align:left;}thead tr{}td{padding:.5em 1em;}tfoot{}tfoot td{padding-bottom:1.5em;}tfoot tr{}#middle{background-color:#900;}</style><body BGCOLOR=""white"">$html</body>"@################################################################################################# If Any Failed Jobs Exists - Then Send and E-mail############################################################################################### IF($CountRows -ne 0 ) { $DestinationServerName $Subject = "$DestinationServerName - Failed SQL Jobs - $GetDate" #$Body = $TableResults #$body = $HTMLmessage SendEmail -To $ToRecipient -From $From -Subject $Subject -Body $HTMLmessage -BodyASHTML -Auto Name -smtpServer $SMTPServer }}[/code]Please suggest if there is any other efficient way of doing this.ThanksSat, 17 Nov 2012 20:03:47 GMTjvkondapalliRE: Get SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxGosh that's some nice looking code. I wish everyone took the tiny bit extra it takes to format and comment code like that. Well done!To answer your question, the method you seek is in the last example of sp_send_dbmail in Books Online (the help system that comes with SQL Server).Sat, 17 Nov 2012 15:59:56 GMTJeff ModenGet SQL Query Results as E-Mailhttp://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxGurus, I am trying to write a code to send out list of failed jobs in the last 24 hours. I am using out-string to convert my table to string. I am able to get email with list of jobs. But, i would like to see the e-mail in a table format rather than a string format. Here is code. Please help me out for converting my code to send email in HTML format. Thank you in advance. [code="plain"]#Set-ExecutionPolicy RemoteSigned############################################################################ Declare ServerName, DatabaseName and TableName to Get Server List###########################################################################$SourceServerName = 'local'$SourceDatabaseName = 'dbadb'#$SourceTablename = 'LookUp_ServerList_NonProd'############################################################################ Declare Variables for Sending E-mails###########################################################################$ToRecipient = "********@email.com"$From = "*********@email.com"$SMTPServer = "smtp.email.com"$GetDate = get-date -format g############################################################################ Create SqlConnection object and define connection string###########################################################################$SQLCon = New-Object System.Data.SqlClient.SqlConnection$SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"############################################################################ Create SqlCommand object, define command text, and set the connection###########################################################################$SQLCmd = New-Object System.Data.SqlClient.SqlCommand$SQLCmd.CommandText = "SELECT SQLServerInstanceNameFROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNPINNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNPON [LUSSILNP].[ServerID] = [LUSLNP].[ServerID]WHERE [SqlPingFlag] = 1"$SQLCmd.Connection = $SQLCon############################################################################ Create SqlDataAdapter object and set the command###########################################################################$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlDataAdapter.SelectCommand = $SQLCmd############################################################################ Create and fill the DataSet object###########################################################################$DataSet = New-Object System.Data.DataSet$SqlDataAdapter.Fill($DataSet, "SQLServerInstanceName") | Out-Null############################################################################ Close the connection###########################################################################$SQLCon.close()############################################################################ Function for sending E-mails to ********@email.com###########################################################################Function SendEmail{ #param($strTo, $strFrom, $strSubject, $strBody, $smtpServer) param($To, $From, $Subject, $Body, $smtpServer) $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = $From $msg.To.Add($To) $msg.Subject = $Subject $msg.IsBodyHtml = 1 $msg.Body = $Body $smtp.Send($msg)}############################################################################ Declare Database and query###########################################################################$SQLDatabaseName = "msdb"$SQLQueryText = "SELECT [S2].[name], [dbo].[agent_datetime]([run_date] , [run_time]) AS RunDateFROM dbo.[sysjobhistory] AS SINNER JOIN dbo.[sysjobs] AS S2ON [S].[job_id] = [S2].[job_id]WHERE [dbo].[agent_datetime]([run_date] , [run_time]) >= GETDATE() - 1 AND ([run_status] IN ( 0 , 3 ) AND [S].[step_id] = 0)"################################################################################################### Iterate through the dataset to Run the query on Remote Server and send Results as E-Mails##################################################################################################foreach ($row in $DataSet.tables["SQLServerInstanceName"].rows){ $DestinationServerName = $row.SQLServerInstanceName #RunSQLQuery -SQLServerName $DestinationserverName -SQLDatabaseName $SQLDBName -SQLQuery $SQLQueryText $SQLConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $DestinationServerName; Database = $SQLDatabaseName; Integrated Security=true" $SqlCommand = New-Object System.Data.SqlClient.SqlCommand $SqlCommand.CommandText = $SQLQueryText $SqlCommand.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCommand $SQLDataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($SQLDataSet) | Out-Null $SqlConnection.Close() $Results = $SQLDataSet.Tables | Format-Table -Auto Name, RunDate | out-string $CountRows = $SQLDataSet.Tables[0].Rows.Count #$Text = @"Following Jobs have Failed:"@ $BodyText = ("$Results") IF($CountRows -ne 0 ) { $DestinationServerName $Subject = "$DestinationServerName - SQL Job Failed - $GetDate" #$Body = $TableResults SendEmail -To $ToRecipient -From $From -Subject $Subject -Body $BodyText -BodyASHTML -Auto Name -smtpServer $SMTPServer }}[/code]Fri, 16 Nov 2012 21:16:12 GMTjvkondapalli