Symptoms
SQL Server Smartobjects drop milliseconds form DateTime values
Diagnoses
We extensively use SQL DateTime fields - and particularly the millisecond values in order manage concurrency in our Line-Of-Business application.
SQL Smart Objects are dropping or ignoring the millisecond portions of the datetime values - making comparisons with existing data to very concurrency impossible.
Create a table:
CREATE TABLE [dbo].[TestMilliseconds](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Created] [datetime] NOT NULL CONSTRAINT [DF_TestMilliseconds_Created] DEFAULT (getdate()),
[Updated] [datetime] NULL,
CONSTRAINT [PK_TestMilliseconds] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into TESTMILLISECONDS(UPDATED) VALUES (NULL)
Create a SmartObject that has read and update methods on the table.
NB: Test the fetch or list methods with the test tool - it only displays the seconds portion
Create a workflow and add a reference to the smartobject which fetches the recently inserted row (Id=1). Add a smartobject step to update the record. Use the "Created" column to set the "Updated" column value.
Run the workflow.
select * from TESTMILLISECONDS
Note the 'Created' column has milliseconds, the 'Updated' column doesn't.
Tried using an email step with an inline function that explicitly formats for fractions of seconds (HH:mm:ss.ffff) the fractions are zero.
.Publish file attached is the export from the SMO services tester.
Resolution
This is a know issue, a TFS has been logged for this which will be reviewed for a future release.
TFS 544933: K2Blackpearl SQLServiceInstance: SQLServiceInsetance Smartobjects drop milliseconds form DateTime values