$svr = get-content "D:\DC\servers.txt"
 
foreach ($Reslt in $body)
{
 $con = "server=$svr;database=data_reliance;Integrated Security=sspi"
$cmd = "select * from dc_online where dc_status = 'fail' "
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt)|out-null
$svr
 $dt | Format-Table -autosize }
 
foreach ($item in $body)
{
 $txt = @" This is an informational Message from TEST Server, For online data concentator network checking either DC status Fail or success, Please kindly check it ASAP and avoid wrong data to be insert on EBS database

NOTE:- This is an auto generated mail notification from Micosoft SQLSERVER2000,Please do not reply. "@
 
$recpts = get-content "D:\DC\EmailList.txt"
 $smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
$subject="EBS DC N/W failure on" + $svr
 $from="JerpCoker@RIL.COM"
foreach ($recpt in $recpts)
 {
 $to = "ananda.murugesan@ril.com"
$cc = $recpt $msg = New-Object system.net.mail.mailmessage
$msg.From = $from
$msg.to.add($to)
$msg.cc.add($cc)
 $msg.Subject = $subject
$msg.Body = $da
$msg.Body = $txt
$smtp.Send($msg) } }

the above script is working fine, but i could not able to create script for query result should comes in email body content, please can any one help me.

Thanks

I noticed you're setting $msg.Body to $da and $txt. Also the first foreach loop (foreach ($Reslt in $body) isn't related to the second (foreach ($item in $body) ). Did you want to loop through the DataTable?

I want result with mail body like

This is query result

dc_id location dc_status IP_no dc_date
----- -------- --------- ----- -------
1 S12 Fail 10.4.20.12 7/28/2009 11:39:11 AM
2 S13 Fail 10.4.20.32 7/28/2009 11:39:30 AM
3 S16 Fail 10.4.20.44 7/28/2009 11:39:47 AM
4 S22 Fail 10.4.20.56 7/28/2009 11:40:09 AM

This is an informational Message from test Server,
For online data concentator network checking either DC status Fail or success,
Please kindly check it ASAP and avoid wrong data to be insert on test database

NOTE:- This is an auto generated mail notification from Micosoft SQLSERVER2000,Please do not reply.

Perhaps something like this...

$result = $dt | format-table -autosize | out-string

$txt = @"
This is an informational Message from TEST Server, For online data concentator network checking either DC status Fail or success, Please kindly check it ASAP and avoid wrong data to be insert on EBS database

NOTE:- This is an auto generated mail notification from Micosoft SQLSERVER2000,Please do not reply.
"@

$bodyText = "$result $txt"

#Remove these lines
$msg.Body = $da
$msg.Body = $txt

#Replace with
$msg.Body = $bodyText

hi Cmille19 thanks for your reply,

As per your reply, i have changed the below script, but email body loaded only $txt messages not query result. is there any suggestion welcomes you.

$svr = get-content "D:\DC\servers.txt"
foreach ($Reslut in $body)
{
$con = "server=$svr;database=data_reliance;Integrated Security=sspi"
$cmd = "select * from dc_online where dc_status = 'fail' "
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.Datatable
$da.fill($dt)|out-null
$svr
$Reslut = $dt | format-table -autosize | out-string
}
foreach ($item in $body)
{
$txt = @"
This is an informational Message from JGSRVR55 EBS Server,
For online data concentator network checking either DC status Fail or success,
Please kindly check it ASAP and avoid wrong data to be insert on EBS database
NOTE:- This is an auto generated mail notification from Micosoft SQLSERVER2000,Please do not reply.
"@
$recpts = get-content "D:\DC\EmailList.txt"
$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
$subject="EBS DC N/W failure on " + $svr
$from="JerpCoker@RIL.COM"
foreach ($recpt in $recpts)
{
$to = "ananda.murugesan@ril.com"
$cc = $recpt
$msg = New-Object system.net.mail.mailmessage
$msg.From = $from
$msg.to.add($to)
$msg.cc.add($cc)
$msg.Subject = $subject
$bodyText = "$result $txt"
$msg.Body = $bodyText
$smtp.Send($msg)

}
}

what i am getting in email body result is like...

This is an informational Message from JGSRVR55 EBS Server,
For online data concentator network checking either DC status Fail or success,
Please kindly check it ASAP and avoid wrong data to be insert on EBS database
NOTE:- This is an auto generated mail notification from Micosoft SQLSERVER2000,Please do not reply.
--------------
But Query result not loaded in email body.

Thanks

Hi ,

I got the full result in email body, i changed script like this

$da.fill($dt)
$Reslut = $dt | format-table -autosize | out-string
$msg = New-Object system.net.mail.mailmessage
$bodyText = ("$result"," $txt" )
$msg.Body = $bodyText
$smtp.Send($msg)

Thank you cimlle19 for your help.

$con = "server=$svr;database=data_reliance;Integrated Security=sspi"
$cmd = "select * from dc_online where dc_status = 'fail' "
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.Datatable
$da.fill($dt)|out-null
$svr
$Reslut = $dt | format-table -autosize | out-string


WARNING: 4 columns do not fit into the display and were removed.

Email Body i got warning messages, and all table column not dispaly, pl tell me how to resolve it,

I tried the following script but this is not hope.

$Reslut = $dt | format-table -autosize | select DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS @{e={(get-acl $_.mshpath).owner}};n='Owner' | ft -auto

Error details:
Select-Object : A parameter cannot be found that matches parameter name 'System.Collections.Hashtable At D:\DC\DCfinalCheck.ps1:11 char:50 + $Reslut = $dt | format-table -autosize | select <<<< DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS @{e= th).owner}};n='Owner' | ft -auto The term 'n=Owner' is not recognized as a cmdlet, function, operable program, or script file. Verify gain. At D:\DC\DCfinalCheck.ps1:11 char:133 + $Reslut = $dt | format-table -autosize | select DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS @{e={(get- ner}};n='Owner' <<<< | ft -auto

Thnaks.
ananda

I see a couple problems, first you are using format-table then select and then ft which is short for format table. Instead use should use select and then format table at the end. So remove the first format-table -autosize. The second problem is the way you are constructing your expression it should be @{name='Owner';Expression={(get-acl $_.mshpath).Owner}

Thanks for your help, as per your reply, I changed the script like below

$Reslut = $dt | select DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS @{name="Owner";Expression={(get-acl $_.mshpath).Owner} |ft -auto

It was not hope.

Error:
Select-Object : A parameter cannot be found that matches parameter name 'System.Collections.Hashtable'.
At D:\DC\DCfinalCheck.ps1:11 char:24
+ $Reslut = $dt | select <<<< DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS @{name="Owner";Expression={(get-acl $_.mshpath).Owner} | ft -auto

thanks

There's comma missing between DC_STATUS and the expression, should be:

DC_STATUS, @{name="Owner";Expression={(get-acl $_.mshpath).Owner}

Hi cmille19 thank you for reply, I trid as you suggestion me, but it is not working, the script like below

$Reslut = $dt| select DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS,@{name="Owner";Expression={(get-acl $_.mshpath).owner}}

Error
-------

Select-Object : The argument cannot be null or empty.
At D:\DC\DCfinalCheck.ps1:14 char:22
+ $Reslut = $dt| select <<<< DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS,@{name="Owner";Expression={(get-acl $_.mshpath).owner}}
Select-Object : The argument cannot be null or empty.
At D:\DC\DCfinalCheck.ps1:14 char:22
+ $Reslut = $dt| select <<<< DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS,@{name="Owner";Expression={(get-acl $_.mshpath).owner}}
Select-Object : The argument cannot be null or empty.
At D:\DC\DCfinalCheck.ps1:14 char:22
+ $Reslut = $dt| select <<<< DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS,@{name="Owner";Expression={(get-acl $_.mshpath).owner}}

I serached many powershell script, but it was not hope.

Thanks
ananda

The statement syntax looks correct. Where does mshpath come from? Is it a column in your dc_status table? Is it not null? Please post the resuls of $dt without piping through select.

I am using script below, and i got the result in List type not table format.  between location and dc_port one blank line comes because location datatype lenth is 150 varchar.

$Reslut = $dt | out-string

The result is

DC_ID : 14
LOCATION : S22_B20

DC_PORT : 2101
IP_NO : 10.60.131.149
DC_STATUS : Fail

DC_ID : 45
LOCATION : S22_B52

DC_PORT : 2101
IP_NO : 10.60.131.181
DC_STATUS : Fail

DC_ID : 134
LOCATION : S21_B32

DC_PORT : 2101
IP_NO : 10.60.131.190
DC_STATUS : Fail


This is an informational message from dc Server, For online data concentator network checking either DC status Fail or Success, kindly check DC fail status ASAP and avoid wrong data to be insert on dc database.

NOTE:- Please do not reply this is an auto generated mail notification from Micosoft windows. If you need further information and contact SERVER ADMIN TEAM.
------------------------
If i am using script like --> $Reslut = $dt | format-table -auto | out-string 
 
Result is
WARNING: 3 columns do not fit into the display and were removed.
--------
Thanks
ananda

I don't see mshpath as a property returned from your $dt variable. This will cause a problem with your original statement:

$Reslut = $dt| select DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS,@{name="Owner";Expression={(get-acl $_.mshpath).owner}}

hi cimlle, if using below script,

$Reslut = $dt | select DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS, @{name="Owner";Expression={(get-acl $_.mshpath).Owner}}


Error:
Select-Object : The argument cannot be null or empty. At D:\DC\DCNWCheck.ps1:14 char:22 + $Reslut = $dt| select <<<< DC_ID,LOCATION,DC_PORT,IP_NO,DC_STATUS,@{name="Owner";Expression={(get-acl $_.mshpath).owner}}

I don't have the same test as you, however if I use the sample pubs database. This works:

$dt | Select au_id, au_lname, @{name="City";Expression={($_.City).tolower()}}

Again as I stated in my previous post, mshpath must be a property of $dt. Is it? Please post the results of

$dt | get-member -type Property

Hi cimlle thanks for reply
I m using two different script and result as follows

1. $Reslut = $dt| select DC_ID, DC_PORT, IP_NO, DC_STATUS, @{name="LOCATION";Expression={($_.LOCATION).Owner}} | out-string

this result location data not display
DC_ID : 266
DC_PORT : 2101
IP_NO : 10.60.131.182
DC_STATUS : Ok
LOCATION :

2. $Reslut = $dt| select DC_ID, DC_PORT, IP_NO, DC_STATUS, @{name="LOCATION";Expression={($_.LOCATION).tolower()}} | out-string

I got result list in email body as below, but could not get table format becuase location column length 150 char in database table. so how to reduce column length thro powershell, data loss if i tried manully reduced column length in sql table. ( storing data length always 8 char in table)

DC_ID : 1
DC_PORT : 2101
IP_NO : 10.60.131.130
DC_STATUS : Ok
LOCATION : SS23

The above result comes around 81KB total 269 records, so i need table format in email body.

I could tired script below for table format

$Reslut = $dt|select DC_ID, DC_PORT, IP_NO, DC_STATUS, @{name="LOCATION";Expression={($_.LOCATION).toupper()}} | ft | out-string

I got result but In email body two coulmn between more space available. so how to reduce and adjust white space.


3. $dt | get-member -type Property

TypeName: System.Data.DataRow

Name MemberType Definition
---- ---------- ----------
DC_ID Property System.Int32 DC_ID {get;set;}
DC_PORT Property System.Int32 DC_PORT {get;set;}
DC_STATUS Property System.String DC_STATUS {get;set;}
IP_NO Property System.String IP_NO {get;set;}
LOCATION Property System.String LOCATION {get;set;}

Thanks


-----

You could try format-table with the wrap option i.e. ft -wrap

To remove extra spaces you do something like:

out-string | foreach {$_ -replace "\s *", " "}

As for your original error, since mshpath isn't a property of your $dt variable that would explain the error The argument cannot be null or empty.

Thanks for reply....

I am asking one final question?

$cmd = "select * from dc_online where dc_status = 'fail' "

I want send email alert for How set the when dc_staus fail?

Thanks

Just to clarify, do you want to updated the column dc_status OR do you want to send an email if dc_status is equal to fail?

To do the latter, simply add an if statement:

#This menas if $dt is not null
if ($dt)
{

}

Hi Cmille thank for reply...

Actually table of dc_status column is updated one, it is updated by application. this column contain string values.

I could try below script, but it was send email both condition for dc_status fail and success

if ($dt -eq 0)
{
"$dt is success"
}
else
{

foreach ($item in $body)
{
$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
$subject="Data Concentrator Network status Notification from EBS server"
$from="JGSRVR55@RIL.COM"
$to = "ananda.murugesan@ril.com"
#$cc = get-content "D:\DC\EmailList1.txt"
$msg = New-Object system.net.mail.mailmessage
$msg.From = $from
$msg.to.add($to)
#$msg.cc.add($cc)
$msg.Subject = $subject
$bodyText = ("$Reslut")
$msg.Body = $bodyText
$smtp.Send($msg)

}
}

$dt will never equal zero. It will either be null or contain an array of DataRows. To check whether $dt is null you should use either:

if ($dt -eq $null)

or the statement I listed in my previous reply which is short way of testing for null:

if ($dt)

Hi Cmille, i coud tried the following script but sending email both condition

1. if ($dt -eq $null)
2. if ($dt)
-----------------------------------

if ($dt -eq $null)
{
"$dt is success"
}
else
{

foreach ($item in $body)
{
$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
$subject="Data Concentrator Network status Notification from EBS server"
$from="JGSRVR55@RIL.COM"
$to = "ananda.murugesan@ril.com"
#$cc = get-content "D:\DC\EmailList1.txt"
$msg = New-Object system.net.mail.mailmessage
$msg.From = $from
$msg.to.add($to)
#$msg.cc.add($cc)
$msg.Subject = $subject
$bodyText = ("$Reslut")
$msg.Body = $bodyText
$smtp.Send($msg)

}
}

another way

if ($dt -eq $null)
{
"dt is ok"}
else{
      $smtp.Send($msg)
}

Thanks

Perhaps its your data. Are you specifying failed status in your query? Here's a quick test I ran which duplicates what you are trying to do and the results are as expected, the first query returns null for $dt and the second returns not null for $dt.

function Get-SqlData
{
    param($serverName, $databaseName, $query)
 
    $connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
    $dt = New-Object "System.Data.DataTable"
    [void]$da.fill($dt)
    $dt
 
} #Get-SqlData

$dt = Get-SqlData "$env:computername\sqlexpress" pubs "select * from authors where au_id = '000'"

#record does not exist returns null
if ($dt)
{
 "dt is not null"
}
else
{
 "dt is null"
}

$dt = Get-SqlData "$env:computername\sqlexpress" pubs "select * from authors"

#records exists return not null
if ($dt)
{
 "dt is not null"
}
else
{
 "dt is null"
}