Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
davecoleman2k5
Starting Member
1 Post |
 Posted - 2009-01-30 : 17:38:56
|
| My "invoices" table is merge replicated between servers in different time zones. The applications at either lack time-zone awareness. They both assume that Invoices.InvoiceDateTime means local time, where it's obviously only correct for one of them (where the insert occurred).Step 1: we add the column InvoiceDateTimeUTC and assign a value on insert. Now we know when it happened, regardless of where it happened.Step 2: When the merge agent inserts the row to the receiving server, we update InvoiceDateTime from InvoiceDateTimeUTC and the local offset.We're stuck cold on Step 2.We tried dropping the column from the replication, and manually adding it back to the table on the subscriber, but that throws the schema out of whack and the merge agent throws errors trying to BCP the snapshot. I'm not sure if this is in some way invalid, or if I just haven't been persistant enough to figure it out.Other things we tried:An insert trigger on the invoice table. Logically, it merges that update back to the other server, which causes that insert trigger to fire, thus gets into a nice little loop.A computed column (with different computations thus different values) on both ends, but of course "localtime" doesn't make a good computed column since the offset is non-deterministic.Any observations or ideas greatly appreciated. FWIW next release we're storing UTC and making the apps time-zone aware. |
|
|
|
|
|
Resolve UTC to local on each end of Merge?