Invoke-DbaDbDataGenerator precision to high for datatime datatype

[codeunitone]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

I'm trying to insert randomized data into a table with a datatime field and getting following error

writeErrorStream      : True
PSMessageDetails      :
Exception             : System.Exception: Conversion failed when converting date and/or time from character string.
                        ---> Microsoft.Data.SqlClient.SqlException: Conversion failed when converting date and/or time
                        from character string.
                           at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
                        breakConnection, Action`1 wrapCloseInAction)
                           at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
                        stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                           at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
                        cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
                        TdsParserStateObject stateObj, Boolean& dataReady)
                           at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean
                        async, Int32 timeout, Boolean asyncWrite)
                           at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
                        completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean
                        asyncWrite, Boolean inRetry)
                           at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                           at CallSite.Target(Closure , CallSite , Object )
                           --- End of inner exception stack trace ---
TargetObject          : INSERT INTO [dbo].[TestingDateTime] ([Id],[SomeDateTime])
                        VALUES
                        ( 908676237,'2022-05-04 08:56:56.3244078' );

CategoryInfo          : NotSpecified: (INSERT INTO [db...56.3244078' );
                        :String) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Invoke-DbaDbDataGenerator,Stop-Function
ErrorDetails          : Conversion failed when converting date and/or time from character string.
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, C:\Users\benjamin.alisch\OneDrive - Mister
                        Spex\Dokumente\WindowsPowerShell\Modules\dbatools\1.1.143\allcommands.ps1: line 149040
                        at Invoke-DbaDbDataGenerator<Process>, C:\Users\benjamin.alisch\OneDrive - Mister
                        Spex\Dokumente\WindowsPowerShell\Modules\dbatools\1.1.143\allcommands.ps1: line 71798
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1}

Steps to Reproduce

$Servername = 'localhost'
$Database = 'TestDateTime'

New-DbaDatabase -SqlInstance $Servername -Name $Database -RecoveryModel Simple;

$cols = @()
$cols += @{
	Name      = 'Id'
	Type      = 'int'
}
$cols += @{
	Name      = 'SomeDateTime'
	Type      = 'datetime'
}
New-DbaDbTable -SqlInstance $Servername -Database $Database -Name TestingDateTime -ColumnMap $cols

$DataGeneratorConfigFileName = New-DbaDbDataGeneratorConfig `
	-SqlInstance $Servername `
	-Database $Database `
	-Table TestingDateTime `
	-Rows 1 `
	-Path .\
Invoke-DbaDbDataGenerator `
	-SqlInstance $Servername `
	-Database $Database `
	-FilePath $DataGeneratorConfigFileName

Please confirm that you are running the most recent version of dbatools

I'm using following dbatools version: 1.1.143

Other details or mentions

I ran Invoke-DbaDbDataGenerator with the -debug parameter and saw that the datetime string has to many digits for milliseconds. it should be only 3 for the datatype datetime

DEBUG: 71796 | [07:45:46][Invoke-DbaDbDataGenerator] INSERT INTO [dbo].[TestingDateTime] ([Id],[SomeDateTime])  VALUES                                                                                                                 
( 2082121362,'2022-10-12 10:29:06.3541309' ); 

The reason for that is, that the MaskingType for the datetime datatype inside the data generator file is date. That is letting the Get-DbaRandomizedValue creating timestamps with 7 digits for milliseconds

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.19041.1682
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.1682
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64) Apr 29 2022 18:00:13 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19044: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.4515.0

[potatoqualitee]

Thanks for the report! I'll ask @sanderstad to take a look

[sanderstad]

The command Get-DbaRandomizedValue is able to generate the correct value for a datetime with just 3 places for the miliseconds. It's just that it only did that if you supplied the DataType parameter and the command Invoke-DbaDbDataGenerator wasn't using that.

The following has been changed

• Get-DbaRandomizedValue
  • Added check if just DataType parameter was supplied and nothing else
  • Added format strings based on DataType parameter
  • All dates use the format string
• Invoke-DbaDbDataGenerator
  • Added DataType parameter to the call of Get-DbaRandomizedValue

@codeunitone Could you clone the dbatools module and switch to the branch fix-datagenerator and try it again. If this solves your problem I can create a PR so the fix will be available in the next release

[codeunitone]

Thanks for the fast response. I will have a look and let you know

[codeunitone]

I tried to load the dbatools module via following command

Import-Module .\dbatools.psd1

I get following error message

Import-Module : The required module 'dbatools.library' is not loaded. Load the module or remove the module from
'RequiredModules' in the file 'C:\Users\benjamin.alisch\Repositories\dbatools\dbatools.psd1'.

from where do I get that library?

[wsmelton]

Structure of our codebase and contributing steps have changed as we are preparing for the 2.0 release.

@codeunitone // @sanderstad

You will need to install the library now from PowerShell Gallery before you try to import the module from GitHub cloning. Install-Module dbatools.core.library -Scope CurrentUser or use Save-Module if you would like.

The repo of the library does not have a build process that is documented at this time so installing from the PS Gallery is best path. They will have independent releases now.

[wsmelton]

I've updated the dev system section on our wiki to include installing the required modules now for local development:

https://github.com/dataplat/dbatools/wiki

[andreasjordan]

I created a new branch "fix-datagenerator-new" based on the changes in the branch "fix-datagenerator" from @sanderstad. I will try to test the changes...