SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get SQL Query Results as E-Mail


Get SQL Query Results as E-Mail

Author
Message
jvkondapalli
jvkondapalli
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 684
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 [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
}
}



Thanks
Jagan K
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86652 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jvkondapalli
jvkondapalli
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 684
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

Thanks
Jagan K
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 2032
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
jvkondapalli
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 684
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86652 Visits: 41098
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


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ivanna Noh
Ivanna Noh
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1144 Visits: 3028
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
Raunak Jhawar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 1944
What is the version of powershell you are using?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
jvkondapalli
jvkondapalli
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 684
2.0

Thanks
Jagan K
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 1944
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/

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

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search