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 .[job_id] = [S2].[job_id]

WHERE [dbo].[agent_datetime]([run_date] , [run_time]) >= GETDATE() - 1

AND ([run_status] IN ( 0 , 3 )

AND .[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