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 Edbo].]TestMilliseconds](
/Id] ]int] IDENTITY(1,1) NOT NULL,
/Created] ]datetime] NOT NULL CONSTRAINT TDF_TestMilliseconds_Created] DEFAULT (getdate()),
/Updated] ]datetime] NULL,
CONSTRAINT TPK_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 NPRIMARY]
) ON NPRIMARY]
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
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
