July 16, 2021 at 4:25 pm
Hi,
i found some script to list out the primary ag groups in servers in powerhsell.
but the results not coming in attachment.
i need results through email email body.
can any one help please find below script
## Setup dataset to hold results
$dataset = New-Object System.Data.DataSet
## populate variable with collection of SQL instances
$serverlist='server1','','server2','server13','server14','server15'
## Setup connection to SQL server inside loop and run T-SQL against instance
foreach($Server in $serverlist) {
$connectionString = "Provider=sqloledb;Data Source=$Server;Initial Catalog=Master;Integrated Security=SSPI;"
## place the T-SQL in variable to be executed by OLEDB method
$sqlcommand="
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT
AGC.name
, RCS.replica_server_name
, ARS.role_desc
, AGL.dns_name
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
ON
AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY'
END
"
## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()
## Execute T-SQL command in variable, fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
#$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
}
## Return all of the rows from dataset object
$dataSet.Tables | FT -AutoSize
$from = New-Object System.Net.Mail.MailAddress "ppp@ppp.com"
$to = New-Object System.Net.Mail.MailAddress "ppp@ppp.com"
# Create Message
$message = new-object System.Net.Mail.MailMessage $from, $to
$message.Subject = "Availability Group Primary List"
$message.Body = "Availability Group Primary List for our SQL Servers"
$Attachment = New-Object Net.Mail.Attachment('c:\temp\SQLAvailGroup.txt', 'text/plain')
$message.Attachments.Add($Attachment)
# Set SMTP Server and create SMTP Client
$server = "smtp.mydomain.com"
$client = new-object system.net.mail.smtpclient $server
# Send the message
"Sending an e-mail message to {0} by using SMTP host {1} port {2}." -f $to.ToString(), $client.Host, $client.Port
try {
$client.Send($message)
"Message to: {0}, from: {1} has beens successfully sent" -f $from, $to
}
catch {
"Exception caught in CreateTestMessage: {0}" -f $Error.ToString()
}
July 17, 2021 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 21, 2021 at 2:53 am
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply