Get SQL Query Results as E-Mail

Question

Get SQL Query Results as E-Mail

jvkondapalli

SSCommitted

Points: 1587
More actions
November 16, 2012 at 9:16 pm

#393625

Gurus, 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.
#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 .[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()
$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
}
}
Thanks
Jagan K

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Gosh 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).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jvkondapalli SSCommitted Points: 1587 More actions · Answer 2

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

Thanks
Jagan K

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

Instead 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.

Joie Andrew
"Since 1982"

jvkondapalli SSCommitted Points: 1587 More actions · Answer 4

Thanks for your suggestions. will edit my script to use that cmdlet instead of my send-email function.

Thanks again!

Thanks
Jagan K

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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

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! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 6

I 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)

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

hope it's useful 🙂

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 7

What is the version of powershell you are using?

Raunak J

jvkondapalli SSCommitted Points: 1587 More actions · Answer 8

jvkondapalli

SSCommitted

Points: 1587

January 9, 2013 at 11:18 am

#1575358

2.0

Thanks
Jagan K

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 9

See this:

http://stackoverflow.com/questions/11263538/powershell-display-table-in-html-email

and

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-MailMessage

Raunak J

RichardIfUwill Valued Member Points: 54 More actions · Answer 10

Worked for me thanks!!

"To be 'The Man,' you gotta beat the man!" ~The Nature Boy