I've read a good article by Don Jones describing how to create a custom object, using WMI drive information, and write it to a database, CSV file, etc.

http://www.scriptinganswers.com/essentials/index.php/2008/04/09/custom-objects-example/

I'm trying to do something similar except that instead of using WMI objects I'm Selecting data from a handful of SQL servers and wish to write it to a central table.  In Don's article he creates a custom object with properties for each selected property of the WMI win32_logicaldisk object.  He does it like this:
#*******************************
function Get-DriveInventory {

     PROCESS {

          #get drives from WMI

          $drives = gwmi win32_logicaldisk -comp $_ -filter "drivetype=3"

          

          #construct output objects

          foreach ($drive in $drives) {

               $obj = New-Object psobject

               $obj | Add-Member NoteProperty ComputerName $_

               $obj | Add-Member NoteProperty DriveLetter $drive.deviceid

               $free = $drive.freespace/1MB -as [int]

               $obj | Add-Member NoteProperty AvailableSpace $free

               $total = $drive.size/1MB -as [int]

               $obj | Add-Member NoteProperty TotalSpace $total

              

               #write output object

               Write-Output $obj              

          }

     }

}
#*********************************

I would like to create a custom object like this, except have the properties be populated from the results of my SQL SELECT query that is being run against a handful of servers.  I think this would make it easier to contruct an INSERT statement to write the data to one central place and would also give me some thing that I could reuse later.  I'd like to be able to create a custom object that could be piped to another function that would accept the "row" object and insert the data.  Could anyone point me in the right direction, provide examples or a link to an article on this?  Thanks!

In the attached file you'll see how I'm doing converting this data to an HTML file, also useful, but I suspect that much of the script will need to be rewritten in order to meet my new requirements of writing that same data to a db table.

(trying to fix this post by oceanhai still having a sporadic issue with some posts, sorry --management)

I've read a good article by Don Jones describing how to create a custom object, using WMI drive information, and write it to a database, CSV file, etc.

http://www.scriptinganswers.com/essentials/index.php/2008/04/09/custom-objects-example/

I'm trying to do something similar except that instead of using WMI objects I'm Selecting data from a handful of SQL servers and wish to write it to a central table.  In Don's article he creates a custom object with properties for each selected property of the WMI win32_logicaldisk object.  He does it like this:
#*******************************
function Get-DriveInventory {

     PROCESS {

          #get drives from WMI

          $drives = gwmi win32_logicaldisk -comp $_ -filter "drivetype=3"

          

          #construct output objects

          foreach ($drive in $drives) {

               $obj = New-Object psobject

               $obj | Add-Member NoteProperty ComputerName $_

               $obj | Add-Member NoteProperty DriveLetter $drive.deviceid

               $free = $drive.freespace/1MB -as [int]

               $obj | Add-Member NoteProperty AvailableSpace $free

               $total = $drive.size/1MB -as [int]

               $obj | Add-Member NoteProperty TotalSpace $total

              

               #write output object

               Write-Output $obj              

          }

     }

}
#*********************************

I would like to create a custom object like this, except have the properties be populated from the results of my SQL SELECT query that is being run against a handful of servers.  I think this would make it easier to contruct an INSERT statement to write the data to one central place and would also give me some thing that I could reuse later.  I'd like to be able to create a custom object that could be piped to another function that would accept the "row" object and insert the data.  Could anyone point me in the right direction, provide examples or a link to an article on this?  Thanks!

In the attached file you'll see how I'm doing converting this data to an HTML file, also useful, but I suspect that much of the script will need to be rewritten in order to meet my new requirements of writing that same data to a db table.

Hal,
  The "attached file" is missing...

oceanhai,
   I will try and look at this at work today. No SQL servers at home to test with.... If you have the command to perform the SELECT.  Then replace Don's $drives with your POSH code.  There are many many MANY examples of object creation... Might i suggest this overview from Brandon Shell LINK presented to the Powershell Virtual user group.

-Glenn

Using an example from Allen White here, emitting a custom object is actually very easy.

$cn = new-object system.data.SqlClient.SqlConnection("Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=AdventureWorks");
$ds = new-object "System.Data.DataSet" "dsPersonData"
$q = "SELECT [ContactID]"
$q = $q + "      ,[FirstName]"
$q = $q + "      ,[LastName]"
$q = $q + "      ,[EmailAddress]"
$q = $q + "      ,[Phone]"
$q = $q + "  FROM [AdventureWorks].[Person].[Contact]"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)

$dtPerson = new-object "System.Data.DataTable" "dtPersonData"
$dtPerson = $ds.Tables[0]
$dtPerson | FOREACH-OBJECT { 
    $obj = New-Object psobject
    $obj | Add-Member NoteProperty ContactID $_.ContactID
    $obj | Add-Member NoteProperty FirstName $_.FirstName
    $obj | Add-Member NoteProperty LastName $_.LastName
    $obj | Add-Member NoteProperty EmailAddress $_.EmailAddress
    $obj | Add-Member NoteProperty Phone $_.Phone

    Write-Output $obj
}

Now that the results from the query have been formated into full objects. Where you go from there the sky is the limit. -Glenn