Powershell module: Write-ObjectToSQL

This Powershell function writes objects from the pipeline into a database table. If the table does not exist it will be created based on the properties of the first object. For every property of the object a column will be created.

 
 
 
 
 
4.4 Star
(11)
3,909 times
Add to favorites
5/26/2017
E-mail Twitter del.icio.us Digg Facebook
  • Update instead of insert
    1 Posts | Last post March 19, 2018
    • Has anyone managed to get the update existing row methods worked out?  I see in the code where pieces were remarked out, but I cannot seem to get this working.
  • Order of columns in the created table
    1 Posts | Last post February 16, 2018
    • I love the ease of the module to get quick and (not so) dirty data into a sql db. I've patched below code to allow for the same property order that is specified for the custom object (get-member orders properties alphabetically). Seems like something that would be of use in main script. 
      
      if ($ObjectIsHash){
                      $names = $InputObject.keys
                  }else{
                      # if TryAllPropertyTypes parameter is used then all types of properties will be checked
                      # otherwise only Property and NoteProperty will be checked (default)
                      if ($TryAllPropertyTypes){
                          $names = $InputObject.PSObject.properties | Select-Object -ExpandProperty name 
                      }else{
                          $names = $InputObject.PSObject.properties | Where-Object {$_.MemberType -eq 'Property' -or $_.MemberType -eq 'NoteProperty' } | Select-Object -ExpandProperty name 
                      }
                  }
      
      answer was taken from stackoverflow...
      https://stackoverflow.com/questions/27361418/how-to-get-powershell-object-properties-in-the-same-order-that-format-list-does
  • Update instead of Insert
    2 Posts | Last post February 08, 2018
    • Hi John,
      
      First of all,Great script, works like charm.
      I would like to be able to update a row if the row already exists.
      
      Example
      
      $data = Get-ciminstance -CimSession SRV1,SRV2 -ClassName CIM_BiosElement |Select Name,Manufacturer, SerialNumber, ReleaseDate, SMBIOSBIOSVersion,Version,@{l='Hostname';e={$_.pscomputername}}
      
      $data | Write-ObjectToSQL -Server DB\TEST -Database TEST -TableName Bios
      
      When I update the Bios of a system (e.g SRV1) I would like that the row of SRV1 is updated instead of inserted.
      This way I always have the latest data in the database.
      
    • I guess you can replace that with a SQL statement to only get the latest value (MAX? I don't remember SQL from the top of my head)
  • New-TimeSpan : A positional parameter cannot be found that accepts argument
    1 Posts | Last post December 07, 2017
    • Whenever I run Write-ObjectToSQL it gives me an error as below, though the insertion works fine.
      
      $ServiceSecurityBaseline | Write-ObjectToSQL @WriteObjhash -TableName ServiceSecurityBaseline -ReportEveryXObject 10
      New-TimeSpan : A positional parameter cannot be found that accepts argument 'â€Start 12/07/2017 06:04:36 â€End'.
      At D:\Repo\MyProjects\Powershell\modules\thirdparty\Write-ObjectToSQL.ps1:904 char:47
      + ... th]::round((NEW-TIMESPAN –Start $reportstarttime –End $reportendt ...
      +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : InvalidArgument: (:) [New-TimeSpan], ParameterBindingException
          + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.NewTimeSpanCommand
      
      Attempted to divide by zero.
      At D:\Repo\MyProjects\Powershell\modules\thirdparty\Write-ObjectToSQL.ps1:906 char:102
      + ... inserted ($([math]::round( ($rowsinserted-$reportrowsinsertedstart)/$ ...
      +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : NotSpecified: (:) [], RuntimeException
          + FullyQualifiedErrorId : RuntimeException
  • Unable to Insert Data using Write-ObjecttoSQL function from VB.net
    1 Posts | Last post July 04, 2017
    • Hi John, 
      I am trying to insert get-service details of a remote computer using Write-ObjectToSQL function. The function after is working fine from the powershell command line .. However the same thing when I am trying to insert it from a VB.NET code it is failing .. Can you help me please?
      
      It is throwing the below error and VB.net code is on the same machine where I am able to insert data using powershell command prompt. 
      
      The term 'Write-ObjectToSQL' is not recognized as the name of a cmdlet,
      function, script file, or operable program. Check the spelling of the name, or
      if a path was included, verify that the path is correct and try again.At
      D:\Collect-ServerInfo-1.0.1\gather-service-info.ps1:23 char:19
      
      I have imported the below modules in my VB.net code.. 
      
      Option Explicit On
      Imports System.Data
      Imports System.Data.SqlClient
      Imports System.Web.UI.HtmlControls.HtmlTableCell
      Imports System.Web.UI.WebControls.Label
      Imports System.Web.Security
      Imports System.Environment
      Imports System.Web.HttpRequest
      Imports System.Collections.ObjectModel
      Imports System.IO
      Imports System.IO.Compression
      Imports System.IO.Packaging
      Imports System.Management.Automation
      Imports System.Diagnostics
      Imports System.Management.Automation.Runspaces
      Imports System.Text
      
      Can you help me please?
      
      Regards,
      Jaideep
      
  • System.Net.IPAddress
    2 Posts | Last post May 12, 2016
    • Hi John.  Fantastic function.  I have a PSCustomObject that includes several NoteProperty type members that use a System.Net.IPAddress definition.  Any support for that coming soon?
    • Hi,
      
      Thats an interesting idea. You could try yourself if you want. If you look around line 358 and 378 you will see how the conversion works from PowerShell (.Net) data types to SQL Server data types. You can add your own conversions there.
      
      For NoteProperties based on the System.Net.IPAddress class it might become slightly more complicated since the class contains several properties. I would probably create a more simple object with only the IP address as a string or binary which can be stored in the database table.
      
      Custom objects which contains more complicated objects as properties will always be tricky to store in a simple table since you only have one row to fit them in. Try to figure out how you can simplify the object to make it easier to store in the database. Then add the conversion in the script if the data type is not available.
      
      /John
  • Incorrect syntax near '('
    2 Posts | Last post March 01, 2016
    • Hi John! First of all, great script, especially the object to data type matching. Really valuable for the PS community. And precisely what I'm looking for to combine my script with it. See: https://gallery.technet.microsoft.com/scriptcenter/Show-Hyper-V-Virtual-652fdd54
      
      My script outputs pscustomobjects with column names like 'Memory(m)'. When I use your script it errors out with the following error.
      
      "Write-ObjectToSQL : Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '('."" 
      
      This is because the insert values statement takes the column names without enclosing them in '[  ]'. I finally traced it to line 694. And as a quick fixed replaced the following.
      
      OLD
      $null = $strBuilderColumns.Append(", $prekey$($key.Replace(' ','_'))")
      NEW
      $null = $strBuilderColumns.Append(", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast")
      
      $quoteFirst and $quoteLast are added just like like you did on line 708
      
      I can imagine you want to modify it the right way :) I'm going to mention your script on my script page as a way to log the Hyper-V performance data to SQL.
      
      Thanks, Ruud
      
      
    • You bet :)
      The script has now been updated to fix that.
      
      Thanks for the help!
  • Support Guid DataType
    2 Posts | Last post January 25, 2016
    • Thank you John for this greet script , and in my scenario we are dealing with Guid Data Types which is not supported by script.
    • Thanks for the feedback. I have now updated the script to support System.Guid as well. It will be converted to nvarchar when inserted into the table.
  • Problem if first row has nulls/empties
    2 Posts | Last post April 16, 2015
    • I'm loving this function, but I just tripped across a problem.  I am pulling from Active Directory just the SAMACCOUNTNAME and EMPLOYEEID, in that order, using Get-ADUser.  (yes, I know employeeID is a custom property, I'm including it in the property list so it gets output :).  So here's the problem, if I filter to return objects that have all the values like everyone named Andy* and then pipe to your write- function, everything outputs to my DB server appropriately.  But, if I just select ALL people from AD, and the first person doesn't have an employeeID, the table that gets created doesn't include that second column...
      
      In other words, it SEEMS like your function looks at the first row of data and creates the table based on the data that's there, but if one of the columns is missing/null/empty, that column doesn't ever get created on the destination server, even though subsequent rows have data in that column.  In my case, it makes a table with one column instead of two, even though Export-CSV clearly shows both columns exist.  
      
      Any thoughts?
    • You are absolutely correct. This is one of the limitations of this script as it looks right now. This has to do with how Powershell handles properties with no value. A property without a value (NULL) often doesnt have a data type. If the script cannot figure out the data type it wont create a column for that property.
      
      The script has a fallback scenario/workaround which it tries for these kind of properties. It simply pipes the object to Get-Member and then parses the output to get the data type. Get-Member seems to sometimes be able to get the data type even if the value is NULL.
      However, this doesnt always work. If you come up with a better solution for this I would be happy to add it to the script.
      
      As a manual workaround you could create the table by piping an object that you know have all the properties that you want and then pipe the rest of the objects in there. Its not great, but thats what I sometimes do if this happens.