• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

I am unable to get table content to my mailbox

$SqlServer = “servername”
 $SqlCatalog = “database”
 $SqlQuery = “select employeeId, Employeename from table;"
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = “Server = $SqlServer; Database =
 $SqlCatalog; Integrated Security = True”
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 $SqlCmd.CommandText = $SqlQuery
 $SqlCmd.Connection = $SqlConnection
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SqlAdapter.SelectCommand = $SqlCmd
 $DataSet = New-Object System.Data.DataSet
 $SqlAdapter.Fill($DataSet)
 $SqlConnection.Close()
 $DataSet.Tables[0]
 $results = $DataSet.Tables | format-table -autosize | out-string
 $body1 =”$results”


 $body1

 $emailFrom = 'email@email.com'
 $emailTo = 'email@email.com'
 $subject = 'Test'
 $emailbody = $Test mail
 $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
 $smtpServer = 'smtpserver'
 $smtp = New-Object Net.Mail.SmtpClient($smtpServer)
 $smtp.Send($message)

Resultant are getting into my mailbox.I like to format the header and body of the content as report.
Can anyone help me in dooing this?
0
VIVEKANANDHAN_PERIASAMY
Asked:
VIVEKANANDHAN_PERIASAMY
  • 8
  • 8
1 Solution
 
chrismerrittCommented:
Hmm, this may or may not help you, but when I wanted to write some SQL data into an email report, I wrote a function that would take a Datatable and then convert it to an HTML table.

Brace Yourself!

The main difference though is that I used Datatables where you are using Datasets, so for my code to work you may need to use Datatables instead of Datasets...

Code for the SQL commands:

#Open Connection
$Connection.Open()
		
#SQL Command
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "
SELECT last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DATABASENAME')
AND OBJECT_ID=OBJECT_ID('TABLENAME')"
			
$Reader = $Command.ExecuteReader()

$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)

#Close the database connection 
$Connection.Close()

Open in new window


Function to convert to HTML:

Function ConvertDatatableTo-HTML
{
param
(
	$Datatable,
	$ShowFooter
)
	#Column Data
	$ColumnCount = $Datatable.Columns.Count
	$Columns = $Datatable.Columns | % {$_.ColumnName}

	#Start Table
	$Code = "<table>"
	
	#Columns
	$Code += "<tr>"
	foreach ($Column in $Columns)
	{
		$Code += "<th>" + $Column + "</th>"
	}
	$Code += "</tr>"
	
	#Rows
	foreach ($Row in $Datatable)
	{
		$Code += "<tr>"
		
		for ($i = 0; $i -lt $ColumnCount; $i ++)
		{
			$Code += "<td>" + $Row.Item($i) + "</td>"
		}
		
		$Code += "</tr>"
	}
	
	#Footer
	if ($ShowFooter -ne $Null)
	{
		foreach ($Number in $ShowFooter)
		{
			New-Variable -Name "ShowFooter$Number"
			
			$Sum = 0
			
			foreach ($Row in $Datatable)
			{
				$Sum += $Row.Item($Number)
			}
		
			Set-Variable "ShowFooter$Number" -Value $Sum
		}
		
		$Code += "<tr>"
		
		for ($i = 0; $i -lt $ColumnCount; $i ++)
		{
			$Temp = $Null
			Try
			{
				$Temp = (Get-Variable "ShowFooter$i").Value
			}
			Catch [Exception]
			{
			}
			
			if ($Temp -eq $Null)
			{
				$Code += "<th></th>"
			}
			else
			{
				$Code += "<th>" + $Temp + "</th>"
			}
		}
		
		$Code += "</tr>"
		
	}
	
	#End Table
	$Code += "</table>"
	
	#Return the Table HTML
	return $Code
}

Open in new window


Code to format the HTML body:

$HTML = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Page Title</title>

<style type="text/css">
body
{
	background: #fff;
	padding: 0px;
	border: 0px;
	margin: 0px;
}

table
{
	background: #FFFFFF;
	margin: 5px;
	border-collapse: collapse;
 	border-spacing: 0px;
	font-family: Tahoma;
	font-size: 13px;
	width: 500px;
}

td
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	font-family: Tahoma;
	font-size: 13px;
}

th
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	background: #0054a1;
	color: #fff;
}

h1
{
	font-family: Tahoma;
	font-size: 18px;
	color: #6CF;
}

h2
{
	font-family: Tahoma;
	font-size: 12px;
	color: Orange;
}
</style>

</head><body>
"@

#Add SQL Datatable
Clear-Variable -Name "LastRefreshTime"
$HTML += "<h1>Section Title</h1>"
#The numbers after the Function name are for summing up the values in those columns, they can be left out and no footer will be shown.
$HTML += (ConvertDatatableTo-HTML $DatatableBackupSummary 1,2,3)

#End HTML
$HTML += "</body></html>"

Open in new window


0
 
prashanthdCommented:
What are all attributes you require?
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
SELECT last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DATABASENAME')
AND OBJECT_ID=OBJECT_ID('TABLENAME')"

here we are using DMV so that we don't to defince the instance name. but if i want to connect to named instance then how can i connect?
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
chrismerrittCommented:
Sorry for any confusion, that is a dummy query, you can do this with any query you want, just replace the Server and DB names from the first section, then put your query into the $Command.CommandText section.

$Connection = New-Object System.Data.SQLClient.SQLConnection
$DatabaseServerName = "SQLSERVERNAME"
$DatabaseName = "DATABASENAME"
$Connection.ConnectionString = "server=$DatabaseServerName;database=$DatabaseName;trusted_connection=True;Connection Timeout=120;"

#Open Connection
$Connection.Open()
		
#SQL Command
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "
SELECT * FROM Table"
			
$Reader = $Command.ExecuteReader()

$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)

#Close the database connection 
$Connection.Close()

Open in new window

0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
so can use all these 3 section in PS1 file?

Since i am in home,i can't test the code now. Just for the confirmation, can i use all 3 section in one ps1 file?
and followed with
$body1 =”$results”


 $body1
@"
 $emailFrom = 'email@email.com'
 $emailTo = 'email@email.com'
 $subject = 'Test'
 $emailbody = $Test mail
 $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
 $smtpServer = 'smtpserver'
 $smtp = New-Object Net.Mail.SmtpClient($smtpServer)
 $smtp.Send($message)
"@
Please correct me if i am wrong.
0
 
chrismerrittCommented:
Yep should be able to, though for sending mail I prefer to use the built in commands like this one instead of using .Net precisely, think you need V2 Powershell though, see if your client supports the command.

#Mail Message
$Sender = "sender@domain.com"
$Recipient = "recipient@domain.com"
$Server = "MailServer"
$Subject = "Message Subject"

#Optional - you can also add -UseSSL switch if your mail server supports SSL
Send-MailMessage -Subject $Subject -From $Sender -To $Recipient -SmtpServer $Server -Body $HTML -BodyAsHTML

Open in new window

0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
I am getting below error code:
Clear-Variable : Cannot find a variable with name 'employee'.
At C:\Documents and Settings\vivek\Desktop\mail.ps1:171 char:15
+ Clear-Variable  <<<< -Name "employee"
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\vivek\Desktop\mail.ps1:68 char:22
+                 $Sum += $Row.Item( <<<< $Number)
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\vivek\Desktop\mail.ps1:68 char:22
+                 $Sum += $Row.Item( <<<< $Number)
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\vivek\Desktop\mail.ps1:68 char:22
+                 $Sum += $Row.Item( <<<< $Number)
The term 'Send-MailMessage' is not recognized as a cmdlet, function, operable program, or script file. Verify the term
and try again.
At C:\Documents and Settings\vivek\Desktop\mail.ps1:186 char:17
+ Send-MailMessage  <<<< -Subject $Subject -From $Sender -To $Recipient -SmtpServer $Server -Body $HTML -BodyAsHTML
0
 
chrismerrittCommented:
Looks like you're trying to sum columns which are not integers, when you call the ConvertDatatableTo-HTML function don't put any numbers after it like this:

$HTML += (ConvertDatatableTo-HTML $DatatableBackupSummary)
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
thanks Sir,

Can you please check below error,
PS C:\Documents and Settings\vivek\Desktop> .\mail.ps1
The term 'Send-MailMessage' is not recognized as a cmdlet, function, operable program, or script
and try again.
At C:\Documents and Settings\vivek\Desktop\mail.ps1:186 char:17
+ Send-MailMessage  <<<< -Subject $Subject -From $Sender -To $Recipient -SmtpServer $Server -Body $HTML -BodyAsHTML

FYI,

I am using powershell 1.0 version.
0
 
chrismerrittCommented:
You will probably need to use the .Net method of sending the mail then like you had it originally, or update PowerShell to v2.0 so that it supports the Send-MailMessage command.

Unfortunately I don't have any code for sending mail using the .Net method but your original code looked OK, I am assuming it worked for you anyway?
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
i tried with my original code, but didn't work.

How to sent the html content with my original code?
0
 
chrismerrittCommented:
Can you include your whole code you have at the moment please so I can better help you?
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
I know, I am missing something. Please see below for the code i have used.
$Connection = New-Object System.Data.SQLClient.SQLConnection
$DatabaseServerName = "Instance"
$DatabaseName = "database"
$Connection.ConnectionString = "server=$DatabaseServerName;database=$DatabaseName;trusted_connection=True;Connection Timeout=120;"

#Open Connection
$Connection.Open()
		
#SQL Command
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "select employeeId, Employeename from dbo.employee;"
			
$Reader = $Command.ExecuteReader()

$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)

#Close the database connection 
$Connection.Close()

Function ConvertDatatableTo-HTML
{
param
(
	$Datatable,
	$ShowFooter
)
	#Column Data
	$ColumnCount = $Datatable.Columns.Count
	$Columns = $Datatable.Columns | % {$_.ColumnName}

	#Start Table
	$Code = "<table>"
	
	#Columns
	$Code += "<tr>"
	foreach ($Column in $Columns)
	{
		$Code += "<th>" + $Column + "</th>"
	}
	$Code += "</tr>"
	
	#Rows
	foreach ($Row in $Datatable)
	{
		$Code += "<tr>"
		
		for ($i = 0; $i -lt $ColumnCount; $i ++)
		{
			$Code += "<td>" + $Row.Item($i) + "</td>"
		}
		
		$Code += "</tr>"
	}
	
	#Footer
	if ($ShowFooter -ne $Null)
	{
		foreach ($Number in $ShowFooter)
		{
			New-Variable -Name "ShowFooter$Number"
			
			$Sum = 0
			
			foreach ($Row in $Datatable)
			{
				$Sum += $Row.Item($Number)
			}
		
			Set-Variable "ShowFooter$Number" -Value $Sum
		}
		
		$Code += "<tr>"
		
		for ($i = 0; $i -lt $ColumnCount; $i ++)
		{
			$Temp = $Null
			Try
			{
				$Temp = (Get-Variable "ShowFooter$i").Value
			}
			Catch [Exception]
			{
			}
			
			if ($Temp -eq $Null)
			{
				$Code += "<th></th>"
			}
			else
			{
				$Code += "<th>" + $Temp + "</th>"
			}
		}
		
		$Code += "</tr>"
		
	}
	
	#End Table
	$Code += "</table>"
	
	#Return the Table HTML
	return $Code
}

$HTML = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Page Title</title>

<style type="text/css">
body
{
	background: #fff;
	padding: 0px;
	border: 0px;
	margin: 0px;
}

table
{
	background: #FFFFFF;
	margin: 5px;
	border-collapse: collapse;
 	border-spacing: 0px;
	font-family: Tahoma;
	font-size: 13px;
	width: 500px;
}

td
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	font-family: Tahoma;
	font-size: 13px;
}

th
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	background: #0054a1;
	color: #fff;
}

h1
{
	font-family: Tahoma;
	font-size: 18px;
	color: #6CF;
}

h2
{
	font-family: Tahoma;
	font-size: 12px;
	color: Orange;
}
</style>

</head><body>
"@

#Add SQL Datatable
#Clear-Variable -Name "Employeename"
$HTML += "<h1>Section Title</h1>"
#The numbers after the Function name are for summing up the values in those columns, they can be left out and no footer will be shown.
$HTML += (ConvertDatatableTo-HTML $DatatableBackupSummary)

#End HTML
$HTML += "</body></html>"

#Mail Message
$emailFrom = 'emailaddress'
 $emailTo = 'emailaddress'
 $subject = 'Test'
 $emailbody = $DatatableBackupSummary
 $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
 $smtpServer = 'smtpserver'
 $smtp = New-Object Net.Mail.SmtpClient($smtpServer)
 $smtp.Send($message)

Open in new window

0
 
chrismerrittCommented:
Nice one, changed stuff about a bit. Functions should generally be above all other code because PowerShell plays the code in the order it's presented rather than compiling it.

Fixed up your mismatched names for things, as you'd taken some of my examples quite literally and used the same names rather than renaming based on what you were doing.

Put all the customisation stuff generally into a section near the top called #Customise Vars. Please replace my values in there with your own correct ones.

You may also need to adjust a couple of the other bits such as the SQL query that is used.

I have tested this and it works fine for me:

Function ConvertDatatableTo-HTML
{
param
(
	$Datatable
)
	#Column Data
	$ColumnCount = $Datatable.Columns.Count
	$Columns = $Datatable.Columns | % {$_.ColumnName}

	#Start Table
	$Code = "<table>"
	
	#Columns
	$Code += "<tr>"
	foreach ($Column in $Columns)
	{
		$Code += "<th>" + $Column + "</th>"
	}
	$Code += "</tr>"
	
	#Rows
	foreach ($Row in $Datatable)
	{
		$Code += "<tr>"
		
		for ($i = 0; $i -lt $ColumnCount; $i ++)
		{
			$Code += "<td>" + $Row.Item($i) + "</td>"
		}
		
		$Code += "</tr>"
	}
	
	#End Table
	$Code += "</table>"
	
	#Return the Table HTML
	return $Code
}

#Customise Vars
$DatabaseServerName = "DBSERVERNAME"
$DatabaseName = "DBNAME"
$EmailFrom = 'Sender@domain.com'
$EmailTo = 'Recipient@domain.com'
$EmailSubject = 'Test Subject'
$SMTPServer = 'SMTP Server'

#SQL Connection
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=$DatabaseServerName;database=$DatabaseName;trusted_connection=True;Connection Timeout=120;"

#Open Connection
$Connection.Open()
		
#SQL Command
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "SELECT * FROM Disks"
			
$Reader = $Command.ExecuteReader()

$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)

#Close the database connection 
$Connection.Close()

$HTML = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Page Title</title>

<style type="text/css">
body
{
	background: #fff;
	padding: 0px;
	border: 0px;
	margin: 0px;
}

table
{
	background: #FFFFFF;
	margin: 5px;
	border-collapse: collapse;
 	border-spacing: 0px;
	font-family: Tahoma;
	font-size: 13px;
	width: 500px;
}

td
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	font-family: Tahoma;
	font-size: 13px;
}

th
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	background: #0054a1;
	color: #fff;
}

h1
{
	font-family: Tahoma;
	font-size: 18px;
	color: #6CF;
}

h2
{
	font-family: Tahoma;
	font-size: 12px;
	color: Orange;
}
</style>

</head><body>
"@

#Add SQL Datatable
$HTML += "<h1>Section Title</h1>"
$HTML += (ConvertDatatableTo-HTML $Datatable)

#End HTML
$HTML += "</body></html>"

#Mail Message
$EmailBody = $HTML
$Message = New-Object Net.Mail.MailMessage($EmailFrom, $EmailTo, $EmailSubject, $EmailBody)
$Message.IsBodyHtml = $True
$SMTP = New-Object Net.Mail.SmtpClient($SMTPServer)
$SMTP.Send($Message)

Open in new window

0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Thanks chrismerritt! It's working now. You are really awesome.

$Command.CommandText = "Exec dbo.PRC_report @startdate='2011-11-01 00:00:00:000', @enddate='2011-10-06 23:59:59:000'"
But if thry to execute the sp with out params,i'm getting below error

Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near '-'."
At C:\Documents and Settings\vivek\Desktop\mail.ps1:62 char:33
+ $Reader = $Command.ExecuteReader( <<<< )
Exception calling "Load" with "1" argument(s): "Value cannot be null.
Parameter name: dataReader"
At C:\Documents and Settings\vivek\Desktop\mail.ps1:65 char:16
0
 
chrismerrittCommented:
Works fine for me here to call a stored proc which doesn't need params like this:

$Command.CommandText = "EXEC dbo.GetMailboxNumbers"

If I try to call one which one expects params then it says it expects them and asks for them instead.

Can you run the command in SSMS without error?
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
yes i am able to run the sp in ssms without any error.
And it also able run the sp successfully which are not with any params.

will there be any issue with quotes which we are using in out params

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now