Date field and replication

Is it true that using date fields in tables being
replicated is not recommended? If true, why?
Thanks
Emma
Emma,
I've never heard this before, and many of my articles have datetime columns.
I do have a few thoughts though...
In snapshot replication I can't see how logically there can be any issues.
In transactional, if there is a default of GetDate() which is often used for
the DateAdded field, as in common with other detfaults, it will not be
transferred to the subscriber so no issue there. Queued updating subscribers
and datetime primary keys could conceivably cause problems, but this is a
relatively obscure situation as the recommendation is not to use datetime
columns for PKs anyway.
The only real issues I can think of are to do with merge replication - if
you are using column-level conflict resolution and you have a "date-changed"
column, you'll get conflicts that you didn't necessarily anticipate. The
other issue you might find is if you are using filters based on date, and
the locale is different on the subscriber and publisher, unexpected records
may be filtered out, and a recent poster was using customided conflict
resolution to solve a similar issue.
If anyone else can add to this list I'd also be interested.
HTH,
Paul Ibison
|||are you sure you are not thinking of the timestamp column. SQL Server 7.0
had problems replicating this data type.
Timestamp columns cannot be published by Publishers running SQL Server 7.0
or to Subscribers running SQL Server 7.0.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#f66WgLFEHA.576@.TK2MSFTNGP11.phx.gbl...
> Emma,
> I've never heard this before, and many of my articles have datetime
columns.
> I do have a few thoughts though...
> In snapshot replication I can't see how logically there can be any issues.
> In transactional, if there is a default of GetDate() which is often used
for
> the DateAdded field, as in common with other detfaults, it will not be
> transferred to the subscriber so no issue there. Queued updating
subscribers
> and datetime primary keys could conceivably cause problems, but this is a
> relatively obscure situation as the recommendation is not to use datetime
> columns for PKs anyway.
> The only real issues I can think of are to do with merge replication - if
> you are using column-level conflict resolution and you have a
"date-changed"
> column, you'll get conflicts that you didn't necessarily anticipate. The
> other issue you might find is if you are using filters based on date, and
> the locale is different on the subscriber and publisher, unexpected
records
> may be filtered out, and a recent poster was using customided conflict
> resolution to solve a similar issue.
> If anyone else can add to this list I'd also be interested.
> HTH,
> Paul Ibison
>
|||Thanks all for the response. I am thinking of the date
field. There are some fields in the database set as
VARCHAR and the vendor is refusing to change them to date
claiming that they will not work with replication.
Emma

>--Original Message--
>are you sure you are not thinking of the timestamp
column. SQL Server 7.0
>had problems replicating this data type.
>Timestamp columns cannot be published by Publishers
running SQL Server 7.0
>or to Subscribers running SQL Server 7.0.
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in
message
>news:#f66WgLFEHA.576@.TK2MSFTNGP11.phx.gbl...
have datetime
>columns.
there can be any issues.
which is often used
>for
detfaults, it will not be
Queued updating
>subscribers
problems, but this is a
not to use datetime
merge replication - if
have a
>"date-changed"
necessarily anticipate. The
based on date, and
publisher, unexpected
>records
customided conflict
interested.
>
>.
>
|||the vendor's lying or confused.
"Emma" <eeemore@.hotmail.com> wrote in message
news:1592901c41659$c8659f20$a501280a@.phx.gbl...
> Thanks all for the response. I am thinking of the date
> field. There are some fields in the database set as
> VARCHAR and the vendor is refusing to change them to date
> claiming that they will not work with replication.
> Emma
>
> column. SQL Server 7.0
> running SQL Server 7.0
> message
> have datetime
> there can be any issues.
> which is often used
> detfaults, it will not be
> Queued updating
> problems, but this is a
> not to use datetime
> merge replication - if
> have a
> necessarily anticipate. The
> based on date, and
> publisher, unexpected
> customided conflict
> interested.

Date based filter in merge replication

Hopefully, someone can provide some advice regarding filtering data by dates.
We have data that we want to expire from the sqlce database because it has reached a certain age. Filters have been applied to only replicate data wihtin the date boundaries we require.
What we have found through testing is that when we increment server and device dates to a date where data should expire, it is not removed from the database on the device. During this testing we made sure our snapshot was updated before applying more chan
ges through our application, allowing us to see data travelling to and from the device. But, alas, the outdated data survived.
So, I suppose it makes sense that if a record is not updated, it will not be affected by replication - or is there some way around this?
Other than archiving data on the server side which has its own set of implications (ms_MergeTombstone growing excessively, effects on historical reports), is there some other technique we can use to limit the volume of data on the device?
Thanks
Steve
Steve,
As you mentioned already, records that are not updated are not affected by
replication. Creating a new snapshot won't help on this side either. So the
technique I term as 'Induced Updates' could be applied in your case. This
simply means making an update in the table for all rows you want replication
to re-assess without changing the data. This sample script will demo:
update YourTable
set column_name = column_name
where date_column = some_date_criteria
You can also create a job to do this periodically, where some_date_criteria
can be dependent on the getdate() function.
Hope the above helps.
Raj Moloye.
|||Steve,
As you mentioned already, records that are not updated are not affected by
replication. Creating a new snapshot won't help on this side either. So the
technique I term as 'Induced Updates' could be applied in your case. This
simply means making an update in the table for all rows you want replication
to re-assess without changing the data. This sample script will demo:
update YourTable
set column_name = column_name
where date_column = some_date_criteria
You can also create a job to do this periodically, where some_date_criteria
can be dependent on the getdate() function.
Hope the above helps.
Raj Moloye.
|||Thanks Raj,
I have to say I was starting to think along those lines as I wrote the original post.
When I update these rows that are out of date range, does replication cause these updated rows to be removed from the device?
I think some testing might be in order here just to confirm what you are saying.
Thanks again.
SteveM
"Raj Moloye" wrote:

> Steve,
> As you mentioned already, records that are not updated are not affected by
> replication. Creating a new snapshot won't help on this side either. So the
> technique I term as 'Induced Updates' could be applied in your case. This
> simply means making an update in the table for all rows you want replication
> to re-assess without changing the data. This sample script will demo:
> update YourTable
> set column_name = column_name
> where date_column = some_date_criteria
> You can also create a job to do this periodically, where some_date_criteria
> can be dependent on the getdate() function.
> Hope the above helps.
> Raj Moloye.
>
>
|||Thanks Raj,
I have to say I was starting to think along those lines as I wrote the original post.
When I update these rows that are out of date range, does replication cause these updated rows to be removed from the device?
I think some testing might be in order here just to confirm what you are saying.
Thanks again.
SteveM
"Raj Moloye" wrote:

> Steve,
> As you mentioned already, records that are not updated are not affected by
> replication. Creating a new snapshot won't help on this side either. So the
> technique I term as 'Induced Updates' could be applied in your case. This
> simply means making an update in the table for all rows you want replication
> to re-assess without changing the data. This sample script will demo:
> update YourTable
> set column_name = column_name
> where date_column = some_date_criteria
> You can also create a job to do this periodically, where some_date_criteria
> can be dependent on the getdate() function.
> Hope the above helps.
> Raj Moloye.
>
>

DATE & TIME OF REPLICATION OF DATA

Hi,
I have 20 databases in merge replication between local & remote server.
I need the date & time to know when that particular change is replicated.
is it possible?
& what is use of the column rowguid can we get any information from this?
Thanks,
Soura
You would have to do something like this
select coldate from MSmerge_genhistory,msmerge_Contents where
MSmerge_genhistory.generation=msmerge_Contents.gen eration
and convert(varchar(36),rowguid)='F0AE2ED2-5FDC-4013-8079-00BC00C37A4A'
The rowguid column is used to identity which row has changed. Each row has a
different value for the rowguid and this value will be global across your
replication solution. So if your first row in the authors table had a value
of 11111111-1111-1111-1111-111111111111, the first row in each of your
subscribers will also have this same value.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:292732AF-103D-4F4C-8DB5-774C8E5D3B76@.microsoft.com...
> Hi,
> I have 20 databases in merge replication between local & remote server.
> I need the date & time to know when that particular change is replicated.
> is it possible?
> & what is use of the column rowguid can we get any information from this?
> Thanks,
> Soura