Select the product you need help with

DateTime values are rounded to the nearest second when a business logic handler is used in SQL Server 2008 or SQL Server 2008 R2

Article ID: 2598510 - View products that this article applies to.

SYMPTOMS

Consider the following scenario:

You configure a merge replication in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2.
The publication contains at least one table article.
The table article contains one or more DateTime columns.
You configure the article to use a custom business logic handler.
The business logic handler handles events that occur during merge replication synchronization.
The business logic handler uses a custom dataset to return the data.

In this scenario, the business logic handler runs as expected. However, the millisecond date part is rounded to the nearest full second. For example, a DateTime value of "2030-01-01 12:34:56.789" is rounded to "2030-01-01 12:34:57.000."

Back to the top | Give Feedback

CAUSE

This issue occurs because the DateTime values are stored in a variable of data type variant when data is passed between managed code and unmanaged code.

Note The DateTime variant does not support the milliseconds date part.

Note The SQL Server merge agent is written in unmanaged code. The business logic for the Microsoft .NET assembly is written in managed code.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top | Give Feedback

WORKAROUND

You may be able to work around this issue by storing the DateTime values in a column that has the data type char or the data type varchar.

Back to the top | Give Feedback

MORE INFORMATION

For example, the issue that is described in the "Symptoms" section occurs if the custom business logic handles conflicting UPDATE statements at the publisher and subscriber. The milliseconds time part is lost when the publisher dataset or the subscriber dataset is copied into the custom dataset, and then the custom dataset is returned to the merge replication reconciler.

This issue occurs if the data is not changed in custom code that resembles the following code sample.

public override ActionOnUpdateConflict UpdateConflictsHandler(..., ref customDataSet, ...)
{
customDataSet = publisherDataSet.Copy();
conflictLogType = ConflictLogType.ConflictLogPublisher;
return ActionOnUpdateConflict.AcceptCustomConflictData;

Back to the top | Give Feedback

REFERENCES

For more information about how to implement a business logic handler for a merge article, visit the following MSDN website:

http://msdn.microsoft.com/en-us/library/ms147911.aspx

(http://msdn.microsoft.com/en-us/library/ms147911.aspx)

Back to the top | Give Feedback