Prev: Explorer Right Context Menu
Next: MSComm
From: Michael Cole on 25 May 2010 23:30
Situation is as follows: -

We have an SQL Server database table that is being updated in a
synchronisation process from another source database, via code that
loops through the source records, finds the corresponding records in
the destination database, and updates relevent fields (via RDO, SELECT
corresponding record, Edit, change all fields, Update.) This is
existing code.

There is a field in the destination database (ModifiedDate) that should
only be updated if any of the destination fields have changed as a
result of the synchronisation. The question relates to how this should
be done.

One additional point that should be mentioned is that field lengths
between source and destination may differ, e.g., a Source string of
"ABCDEFG" may be mapped to a Destination of length 4. If the
destination field contains "ABCD" then no modification has occurred,
however it it contains "ABCE", then a modification will occur.

My original thought was to add a trigger to the destination database,
to compare the .inserted against the original, and use that to control
the Modified flag. This may be an issue in terms of releasing a
database change to the client - it is easier to release an application
change.

Otherwise, it looks like some sort of lengthy comparison would need to
be made against all fields prior to the update, and then calling the
update only if a field is different.

I would be interested in any thoughts as to this. Are there options
that I have missed? Does anyone have any suggestions?

--
Michael Cole


From: Jason Keats on 26 May 2010 11:00
Michael Cole wrote:
> Situation is as follows: -
>
> We have an SQL Server database table that is being updated in a
> synchronisation process from another source database, via code that
> loops through the source records, finds the corresponding records in the
> destination database, and updates relevent fields (via RDO, SELECT
> corresponding record, Edit, change all fields, Update.) This is existing
> code.
>
> There is a field in the destination database (ModifiedDate) that should
> only be updated if any of the destination fields have changed as a
> result of the synchronisation. The question relates to how this should
> be done.
>
> One additional point that should be mentioned is that field lengths
> between source and destination may differ, e.g., a Source string of
> "ABCDEFG" may be mapped to a Destination of length 4. If the destination
> field contains "ABCD" then no modification has occurred, however it it
> contains "ABCE", then a modification will occur.
>
> My original thought was to add a trigger to the destination database, to
> compare the .inserted against the original, and use that to control the
> Modified flag. This may be an issue in terms of releasing a database
> change to the client - it is easier to release an application change.
>
> Otherwise, it looks like some sort of lengthy comparison would need to
> be made against all fields prior to the update, and then calling the
> update only if a field is different.
>
> I would be interested in any thoughts as to this. Are there options that
> I have missed? Does anyone have any suggestions?
>

Stored procedure(s) could also be used to do the comparision - but that
involves changes to the database as well as the original code, so I'm
guessing that's not a satisfactory alternative.

However, if your program is connecting via a dbo user role then you
could create stored procedures on the fly - but that's a little but "out
there".

I'd probably go for the "lengthy comparison" method.


 | 
Pages: 1
Prev: Explorer Right Context Menu
Next: MSComm