Powershell module: Write-ObjectToSQL

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.

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

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)

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

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

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

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!

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.

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.