• mtz676

    SSCrazy Eights

    Points: 8893

    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

  • blakemcneill

    SSC Enthusiast

    Points: 132

    Does the $dt variable have the servers in it? Are you getting an error? For the $sql, you are not using the $NewObj variable to enter the values from the Get-WmiObject command. You'll want something like this:

    $sql = "INSERT INTO dbo.ServicesTable VALUES ('$($NewObj.SystemName)', '$(NewObj.DisplayName)',

    and so on.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply