johnroos / powershell Goto Github PK

Use Case:
I wanna use the function Write-ObjectToSQL to write some users from LDAP into SQL. I want to use objectGUID as a primary key.

Need:
I need the possibility to use a GUID as primary key.

Hey I have been using your module for a few weeks, it has made my work really simple. So thank you for sharing it.

I came across an issue I thought I would bring to your attention, if I have a windows .url file that looks like this:

[{000214A0-0000-0000-C000-000000000046}]
Prop3=19,11
[InternetShortcut]
IDList=
URL=https://www.youtube.com/watch?v=fIRbS5jzuXw

Attempting to import it into session with Get-Ini returns incomplete data, the URL keys value containts a the = assignment symbol.

Maybe this is this intended?? hopefully not and maybe you we can have a way to escape = in keys values

Looks like there is a boolean test where there should be a null test which is causing number data types that are zero valued to insert with NULL values.

Line: 789 (Write-ObjectToSQL.psm1)
if ($($InputObject.$key)){ if ($datatype -eq 'timespan' -or $datatype -eq 'System.TimeSpan') { Write-Verbose "Timespan found ($key). Converting to ticks." $null = $strBuilderValues.Append(", $(($InputObject.$key).Ticks)") }else{ $null = $strBuilderValues.Append(", $($InputObject.$key)") } }else{ $null = $strBuilderValues.Append(", NULL") }

if ($($InputObject.$key)) will evaluate to false for any zero value, this causing $strBuilderValues.Append(", NULL") to fill in a NULL for your insert string. Just a heads up as I ran into this when filling in some columns of data that is usually 0,1,2 in value. Unless I am missing something that can modify the behavior so an actual zero goes in?

I may be using the credential object incorrectly but this is what I'm getting.

`
$OSDStats | Write-ObjectToSQL -Credential $mycreds -Server S021M370.AUTOEXPR.COM -Database GI_Imaging_Baseline2 -TableName TSBaseLine

PS C:\Users\USER\Desktop> C:\Users\USER\Desktop\OSD Time Stats v1.1.ps1
WARNING: Could not open the connection. See error message below3.
Write-ObjectToSQL : Exception calling "Open" with "0" argument(s): "Login failed for user 'Domain\ACCOUNT'."
At C:\Users\USER\Desktop\OSD Time Stats v1.1.ps1:104 char:13

• $OSDStats | Write-ObjectToSQL -Credential $mycreds -Server S021M370.A ...

•         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
  • FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-ObjectToSQL`

When Write-ObjectToSQL when inserting a DateTime, the table is created correctly but subsequent INSERTs rely on DateTime.ToString() ... this has local issues ...

(Get-Date).ToString()
21/06/2018 18:33:17

In order to insert date/time into SQL Server reliably as a nvarchar, Write-ObjectToSQL should convert date/time objects into the SQL Server default date/time format.

E.g.

I have modified the function including ...
$dateformattypes = @{
# PS datatype = SQL data type
'System.DateTime' = 'datetime';
'datetime' = 'datetime';
}

...
if ( $numbertypes.ContainsKey( $datatype ) ){
$null = $strBuilderColumns.Append(", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast")

                if ($($InputObject.$key) -ne $null){
                    if ($datatype -eq 'timespan' -or $datatype -eq 'System.TimeSpan') {
                        Write-Verbose "Timespan found ($key). Converting to ticks."
                        $null = $strBuilderValues.Append(", $(($InputObject.$key).Ticks)")
                    }else{
                        $null = $strBuilderValues.Append(", $($InputObject.$key)")
                    }
                    
                }else{
                    $null = $strBuilderValues.Append(", NULL")
                }
            **}elseif ( $dateformattypes.ContainsKey( $datatype ) ){
                $null = $strBuilderColumns.Append(", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast")
                $strtmp = $InputObject.$key.ToString("yyyy-MM-dd HH:mm:ss.fff")
                if ($ConnectionString){ 
                    $null = $strBuilderValues.Append(", '$strtmp'")
                }else{
                    $null = $strBuilderValues.Append(", N'$strtmp'")
                }**
            }elseif ( $stringtypes.ContainsKey( $datatype ) ){
                $null = $strBuilderColumns.Append(", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast")
                $strtmp = $InputObject.$key -replace "'", "''"
                if ($ConnectionString){ 
                    $null = $strBuilderValues.Append(", '$strtmp'")
                }else{
                    $null = $strBuilderValues.Append(", N'$strtmp'")
                }