Syncing row containing datetime column with date value 1753-01-01 00:00:00.000 on device

Question

Hello.

I have a sync setup with a 2008 sql db syncing through a WCF server to a Desktop client and a Device client.

One of my tables has a row containing a datetime column with date value 1753-01-01 00:00:00.000.

The Desktop clients syncs this row just fine, but on the device it's missing. 

If I change it to a present date the device syncs the row just fine.

I've testet it on the 6.0 professional emulator and on a 6.1 proffesional device.

/Ole

Answer 1

hi Ole,

have you tried checking on the ApplyChangesFailed event if there's anything being raised there that may have some information on why the row is not synching?

Answer 2

hi Ole,

have you tried checking on the ApplyChangesFailed event if there's anything being raised there that may have some information on why the row is not synching?

Hello June.

Just tried it. 

It gives me the following error message : "An overflow occurred while converting to datetime."

/Ole

Answer 3

did you find any other error information in the innerException?

Answer 4

Hello Oled,

I assume you are using SQL CE on device client.

Just FYI, not all data types are compatible for sync because some data type support is not exactly same between SQL and SQL CE.

Can you entering the data directly on CE and see what happens?

Thanks,

Patrick

Answer 5

Hello Oled,

I assume you are using SQL CE on device client.

Just FYI, not all data types are compatible for sync because some data type support is not exactly same between SQL and SQL CE.

Can you entering the data directly on CE and see what happens?

Thanks,

Patrick

Hello Patrick.

The desktop client is also using SQL CE and it has no problem syncing the date.

/Ole

Answer 6

did you find any other error information in the innerException?

Hello June.

The innerexception is null.

http://img20.imageshack.us/img20/6643/syncfejl.png

But the stacktraces shows that the error is comming from the sqlcecommand. Maybe there is a difference between SQL CE on the desktop and on devices?

/Ole

Answer 7

Please search for WCF datetime - this may be an issue with how datetime value is serialized over WCF, or a Nullable datetime issue.

Answer 8

Hi Ole,

In addition, is the device timezone different than the source?


JuneT

Answer 9

Hello.

JuneT:

The device timezone is the same as the server timezone.

Sid:

As far as I know I can't control how Sync Framework serialize datetimes over WCF?

/Ole

Answer 10

Hello Old - "An overflow occurred while converting to datetime." appears in the ApplyChangesFailed event, what is the "source" value for this datetime field (it can be found through the event argument, SyncContext and DataSet property)?  Do you still see "1753-01-01 00:00:00.000"?

Thanks

---

Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 11

Hello Leo

e.Context.DataSet is null when the ApplyChangesFailed event is thrown.

/Ole

Answer 12

Hi Ole,

How about the e.Conflict.ClientChange and e.Conflict.ServerChange? do you see the row there?

Cheers,

JuneT

Answer 13

Hi June.

 

e.Conflict.ClientChange is null.

 

e.Conflict.ServerChange.Dataset is null. 

 

There is a column and a row collection under serverchange, but I can't see the values in the row collection. The column collection has the correct columns in it.

 

 

/Ole

Answer 14

try this, replacing "YourDateColumnName" with the name of your date column

e.Conflict.ServerChange.Rows[0]["YourDateColumnName"]

Answer 15

http://img90.imageshack.us/img90/5393/syncfejl2.png

 

/Ole

Answer 16

hi ole,

is this the date you're getting when you sync 1753-01-01 00:00:00.000?

 

Answer 17

Hello June.

 

Yes it is.

 

Is it a timezone problem?

 

/Ole

Answer 18

maybe...

your 1753-01-01 looks like it went back to 1752-12-31 (i cant tell exactly cause the image you provided doesnt have the year).

can you look at the other dates and see if they adjusted when reaching the client?

JuneT

Answer 19

Hello June.

It is a timezone problem.

Our server timezone is GMT +1.

 

A couple of test results with different timezones on a PDA.

Date in sql server: 2010-02-01 00:00:00.000


Regional setting English
Timezone -8
1/31/2010 3:00:00 PM

Regional setting Danish
Timezone +1
2/1/2010 12:00:00 AM

Date in sql server: 1753-01-01 00:00:00.000

Regional setting English
Timezone -8
Exception - row is not synced.

Regional setting Danish
Timezone +1

1/1/1753 12:00:00 AM

 

We will have PDA's all over the world connecting to this server. Any suggestions to a workaround on this?

 

/Ole

Answer 20

In this case, using UTC might be a solution (calling GETUTCDATE() instead of GETDATE()).

Thanks.

---

Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 21

Problem is that we don't have any control over these dates, they are coming from an ERP system.

/Ole

Answer 22

You can convert it to UTC on the client - since you know what timezone the date is for, then convert back into local time.

Answer 23

try the approach here: http://support.microsoft.com/kb/842545

you can do the conversion on the ApplyingChanges event of the client provider.