I need to go through a list of SQL Servers from a SQL table;hit the respective server and collect this data (SystemName, DisplayName, Name, State, Status, StartMode, StartName) and insert those values into a sql table dbo.ServicesTable.How do I achieve this ?
I cannot get the below code to work. Any help is appreciated.Thanks
$Server = "ServerName"
$Database = "DBName"
$con = "server=$Server;database=$Database;Integrated Security=sspi"
$cmd = "SELECT DISTINCT ServerName FROM dbo.ServersTable"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
foreach ($srv in $dt)
{
$ServerName = $srv.ServerName
$NewObj = Get-WmiObject win32_Service
$sql = "INSERT INTO dbo.ServicesTable(SELECT SystemName, DisplayName, Name, State, Status, StartMode, StartName)
Invoke-SQLcmd -serverinstance $Server -database $Database -query $sql
}