We have a SQL Server Merge Replication configuration that uses a business logic handler to do conflict resolution on a datetimeoffset column.

All we're doing is a copy of the Subscriber or Publisher dataset to the CustomDataset in the UpdateConflictsHandler function and logging the win. When profiling we see the below, where it appears that all non-datetime date and time types (datetime2, datetimeoffset, date, time) are being mangled by adding spaces between each character.

We are surmising that somehow after our function returns the column is being encoded as UTF-16 and decoded by SQL Server as UTF-8. Is there any way to fix this? It seems to be happening wholly outside of our code. (SQL Server 2008R2, with Merge Replication through IIS over web)

exec [MSmerge_cft_sp_7BF3FB7C8F8D4A33D64FC13EF2DE42B6] '4BCCFA1D-891F-4E7D-9A0B-000409743B2B'
,'2 0 1 3 - 0 4 - 2 0   1 7 : 0 1 : 3 1 . 0 0 0 0 0 0 0   - 0 7 : 0 0'
,'2 0 1 8 - 0 4 - 1 8   1 1 : 0 8 : 4 0 . 4 7 2 5 2 7 2   - 0 7 : 0 0 '
,1,NULL,1,1,1,NULL,'4BC6FA1D-891F-4E7D-9C0B-000409793B2B',N'PUBLISHER-NAME',1,1,N'The same row was updated at both ''SUBSCRIBER.RepTest11'' and ''PUBLISHER.RepTestCorp''. The resolver chose the update from ''SUBSCRIBER.RepTest11'' as the winner.','D64FC13E-F2DE-42B6-A40E-05DF4159644F',0x00,46258027,'D64FC13E-F2DE-42B6-A40E-05DF4159644F',0
  • Does this happen with regular DATETIME values or just DATETIMEOFFSET? The string parameters all seem to be handled correctly. It looks like an issue with whatever code generates that T-SQL. The DATETIMEOFFSET values coming from the DB shouldn't be strings, so this should be happening on the way out. Are you getting errors because of this? or is it purely an issue with how it appears in SQL Server Profiler? – Solomon Rutzky Apr 18 '18 at 21:53
  • Just offsets, as far as I can tell. My merge conflict resolver works correctly on a test table with only DATETIMES – Jason Apr 18 '18 at 21:57
  • All I get in my UpdateConflictsHandler function is a publisherDataSet and a subscriberDataSet. They're both C# System.Data.DataSet objects, and the datatypes of nearly all the fields is either System.String, GUID, or Int, and I only pass back which one is the winner. – Jason Apr 18 '18 at 21:59
  • I assume you pass back the object / reference and not a custom DataSet that you created, right? Meaning, the datatypes were all assigned automatically and you didn't have a hand in that. Is the datatype for the offset column in both DataSets DateTimeOffset or System.String? It kinda looks like it is being mishandled as a string. – Solomon Rutzky Apr 18 '18 at 22:07
  • Also, I highly doubt that the problem is within SQL Server. This is getting messed up before it gets there. If SQL Server was misinterpreting the incoming value(s), then the entire thing would be double-spaced. Which actually leads to a question: are you seeing exactly that in SQL Server Profiler? If so, then it is being passed in as a full string (i.e. not a parameterized query). Otherwise you would see parameters being passed into that stored proc (i.e. @p1). So whatever is building that query is misunderstanding how to handle DATETIMEOFFSET. – Solomon Rutzky Apr 18 '18 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.