I have a list of SQL servers that I want to loop through to test the linked servers on each SQL instance using the Test-DbaLinkedServerConnection command from dbatools and then emails the results.
I have gotten the script to work when reading from a text file with the following code:
Clear-Host
$Style = @"
<style>
BODY{font-family:Calibri;font-size:12pt;}
TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse; padding-right:5px}
TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;color:black;background-color:#FFFFFF }
TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:Green}
TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black}
</style>
"@
$servers = Get-Content D:\Powershell\LinkedServerList.txt
$linkedservers = $servers | Get-DbaLinkedServer | Test-DbaLinkedServerConnection | Select-Object SqlInstance, LinkedServerName, RemoteServer, Connectivity, Result | Sort-Object SqlInstance, LinkedServerName | ConvertTo-Html -Head $Style| Out-String
$messageParameters = @{
Subject = "Linked Servers Test"
Body = $linkedservers
From = "noreply@companyX.com"
To = "personA@companyX.com"
SmtpServer = "smtp.companyX.com"
}
Send-MailMessage @messageParameters -BodyAsHtml
But when I try to pass in values from a query instead of reading from a text file, this is where I run into an issue:
Clear-Host
$Style = @"
<style>
BODY{font-family:Calibri;font-size:12pt;}
TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse; padding-right:5px}
TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;color:black;background-color:#FFFFFF }
TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:Green}
TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black}
</style>
"@
#$Instance = "ServerA"
$Database = "DatabaseA"
$LinkedServerQuery = "SELECT DISTINCT em.ServerName as Server FROM dbo.EnvironmentMap AS em WHERE em.Retired = 0 ORDER BY em.ServerName;"
$servers = Invoke-DbaQuery -SqlInstance $Instance -Database $Database -Query $LinkedServerQuery
$linkedservers = $servers | Get-DbaLinkedServer | Test-DbaLinkedServerConnection | Select-Object SqlInstance, LinkedServerName, RemoteServer, Connectivity, Result | Sort-Object SqlInstance, LinkedServerName | ConvertTo-Html -Head $Style| Out-String
$messageParameters = @{
Subject = "Linked Servers Test"
Body = $linkedservers
From = "noreply@companyX.com"
To = "personA@companyX.com"
SmtpServer = "smtp.companyX.com"
}
Send-MailMessage @messageParameters -BodyAsHtml
And the error I am receiving is:
The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
I don't understand why the text file script works but the query method is returning an error. Any explanations on what I am doing wrong here?
