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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy