Get SQL Query Results as E-Mail

#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 SQLServerInstanceName
FROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNP
INNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNP
ON [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 RunDate
FROM dbo.[sysjobhistory] AS S
INNER JOIN dbo.[sysjobs] AS S2
ON [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
}
}

#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 SQLServerInstanceName
FROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNP
INNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNP
ON [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 RunDate
FROM dbo.[sysjobhistory] AS S
INNER JOIN dbo.[sysjobs] AS S2
ON [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
}
}

jvkondapalli (11/17/2012)

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:

#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  SQLServerInstanceName
FROM    [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNP
INNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNP
ON      [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 RunDate
FROM    dbo.[sysjobhistory] AS S
INNER JOIN  dbo.[sysjobs] AS S2
ON [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
    }
}

Please suggest if there is any other efficient way of doing this.

Thanks

-- ************************************************************************************************-- 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_id
FOR 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'