Identifying Modified Database Fields

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


0
Michael
5/26/2010 3:30:53 AM
vb.general.discussion 1016 articles. 0 followers. Follow

1 Replies
525 Views

Similar Articles

[PageSpeed] 0

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.


0
Jason
5/26/2010 3:00:40 PM
Reply:

Similar Artilces:

Database query, spreadsheet size
I have a spreadsheet with several database queries from SQL Server. As queries are refreshed, spreadsheet size continues to grow. Even when I wipe out the queries, the file stays around 300K. Does anyone know where this extra data is sitting and how can I clear it out. Hi 300K is not that big but have a look at: http://www.contextures.com/xlfaqApp.html#Unused >-----Original Message----- >I have a spreadsheet with several database queries from >SQL Server. As queries are refreshed, spreadsheet size >continues to grow. Even when I wipe out the queries, the >file stay...

Importing database data.
I am using Visio Professional 2002. I have learned to link an access database to a floor plan; but, I cannot link the database fields to the rooms. I would like to be able to import personnel names and other information into the floor plan. How would I do this? consider doing a search on 'visio 2002 space plan', al "Paul" <Paul@discussions.microsoft.com> wrote in message news:0CD71740-D815-4ECB-8F15-14B823B115FB@microsoft.com... > I am using Visio Professional 2002. I have learned to link an access > database to a floor plan; but, I cannot link t...

Outlook 2007
I created an email and addressed it to one of my Contacts. When I printed the email, it printed an additional field at the top called "Contacts" with that contact's name. This is not a customized form. Why is this field printing at the bottom of the TO, SUBJECT, and ATTACHMENTS fields in the header as an additional header. Thanks so much! Jean ...

Excel amount field formatting error
Excel 2000 version 9.0.6926 sp-3. My amount is 18.00. I select format-number-currency and my amount becomes $1800.00. Always adding 2 zeroes! It formats correctly if I enter just 18. If my amount is 18.25 it formats it as $1825.00. I've tried several different formats with no success. My computer is an hp pavilion xt926. Can someone please help me?!?! Thank you. Lynn Hi, Lynn. Thanks for providing all the info---most people don't. Go to Tools-->Options, and click the Edit tab. Untick "Fixed Decimal" thingee. :) ******************* ~Anne Troy www.OfficeArticles.com &q...

Publisher does not suppress blank fields with Catalog Merge...please help
As I originally posted, I need help finding out why Publisher is not suppressing blank lines when I merge to a new (or existing) document. [I tried to reply to the existing post, but google just gave me an error.] The person who replied just referred me to the help file (F1). Well, actually, I have tried the Help file, Microsoft's knowledge base, and newsgroups. That's why I posted here. I cannot find any information as to why it is not suppressing blank fields, and inserting extra lines. Some real constructive help would really be appreciated. I am posting here as a last resort...

Modify Outlook Default Columns for New Folder??
When I create a new folder to store e-mail, one of the default columns is the "Size" column. I prefer not to have this column in my folders. I know I can right click and remove this column. Is there a way to modify my settings so that this column will never appear when I create a new folder in Outlook? Tom View, Arrange by, if Ol2003], Current View, Define views... reset the view (usually Messages) and modify it. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Othe...

Potential customer field in Quote
Hi, Is there a way to make the Potential customer field in Quote entity as NOT required? Thank you in advance for any suggestions, crsb The entire sales process from opportunity up to the invoice requires a customer. If you don't need the field, create a dummy account and set the customer id to this dummy account in OnLoad. -- Michael H�hne, Microsoft Dynamics CRM MVP CRM Blog on http://www.stunnware.com ---------------------------------------------------------- "crsb" <srinivasa.bharadwaj@gmail.com> schrieb im Newsbeitrag news:76d7f1b1-c86f-470b-bbec-0329d9d29f...

2003 Form fields losing formatting in 2007
Hi there, I have some forms, created in Word 2003, which when opened as a .docx, all form fields revert to Times New Roman font but if they are opened as a .doc, they are in the correct font of Arial. Anyone got any ideas why and how I can fix this? Thanks very much. This doesn't make any sense. Documents always open in the format in which they are saved. What *exactly* are you doing? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com ...

Can you expand/collapse columns based on a reportitem/field values
I have a report that is driven by accounting periods. The columns are 1, 2, 3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible? Thanks in advance Original Stealth, Certainly can. I created the following dataset: SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3 UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr...

Copying fields from combo box to a table
Hi, I am creating a database for our Machine Maintence Report (access 2003). I created a combo box from table1 and I want to have the selected fields (i.e. Machine, Technician, Engineer etc...) in table1 to be copied in table2. This table2 has the same fields Machine, Technician, Engineer plus the other field that will be updated when the maintenance is done such as remarks, issue and data readings. Looking forward for your help. If you really have a need to store this information in multiple tables, you can use code in the After Update event of the combo box to push the valu...

Tasks
I went to a table view for my tasks, went to the Field Chooser and chose "All Task Fields" I wanted to select the fields related to the recurrence of tasks... I "assumed" they would be similar to the calendar fields/functions: Recurrence range start & end dates, recurrence pattern, etc... but... I can not find any fields to add to my table view to display the information of this sort... I have repeating tasks, is there a way to display the information related to how they "repeat"? Sorry if I am missing something silly, I had no problems setting th...

database size question #2
Hi all - Trying to get a little perspective. Currently running 5.5 sp4 - my priv.edb is 46GB - is this considered big? The storage is big and fast - if 46GB is not considered big, what is - what have some of the larger 5.5 priv.edb been? When I move to 2003 by the end of year, I plan on throwing a large NAS box and using iscsi for the exchange database(s) - and finally giving my users 'unlimited' email storage - what is considered big for a 2003 private store? Many thanks. Peter On Tue, 24 Apr 2007 12:44:59 -0700, "PFG" <petergump@gumps.org> wrote: >H...

Table calculations. Season from Date in Hunting Harvest Database.
I am trying to set up a hunting harvest database. I would like to add a column to the original data table that automatically calculates the hunting season, which is based on a specific harvest timeframe. Each harvest record entry has a specific date. For example, when a record of a deer harvested on 12-3-2007 is entered; I want the database to calculate that the date occurred during the fall 2007 season. Thus, I want 2007 to be automatically entered for this record in a separate column. However, our seasons last into the new year, so if for example a record is entered of a ...

Field with running count of records
Hi, I have a query with fields such as date, Item Number and Quantity. I want to create an additional field called Count that will act like an autonumber. It will assign a value of 1 for the first record, 2 for the second number etc. Can someone tell me how to do this? Thanks, -- Chuck W There are tons of posts on how to create a "ranking" query. You must have a field or fields that uniquely identify the sort order. If you provided significant table and field names as well as your desired sort order, someone could create the SQL for you if you can't search for and fi...

Set form field on double click
I am using MS Access 2003. I have a continuous form that is run off of a query "srbScientific Review". The query's criteria is set to [Enter Last Name] in the LastName field. That part works fine. I get my continuous form that shows LastName, DocID, and Title of Document. I would like to double click on the DocID and have the following happen: 1. Open another form "publications" 2. Set the SRBId field to the DocId field that I double clicked on. PLEASE help. I think I need to bookmark, clone.... I'm very confused. As a kick in the right ...

Grid Pane Field Expressions
Ordinarily, I would put field expression code in the OnFormat_Detail event section. However, if it's possible to build the expression I want directly into a query grid pane while in grid design, I would prefer to do that in the current instance. Current case: I have a field "Title" (happens to be the title of a classical music piece.) and I also have the cataloging system and values in separate fields, CatSystem and CatSystemVal, e.g., "Op.", "78". So, if the current piece has a chronological catalog value, I want the query to return it appended to the title ...

Modifying Comments
When modifying the comment in the "Reading Pane" it will freeze and gives me the message that the document is locked. Then the screen will blink and Word will then allow me to make a couple of changes before the process starts over. What can I do to keep the document from "locking" while I am working. Sandi If this happens with a particular document, only, that document is probably corrupt. See http://word.mvps.org/FAQs/AppErrors/CorruptDoc.htm. -- Stefan Blom Microsoft Word MVP (Message posted via NNTP) "Sandi" <Sandi@discussions.m...

modify autoformat
How can I modify a predefined AutoFormat template available in Excel 2003? You can change it after it has been applied but you can't modify them so that the next time you use them they will be changed Regards, Peo Sjoblom "Ashoke" wrote: > How can I modify a predefined AutoFormat template available in Excel 2003? ...

Logging on to more than one HQ Database
Hi, we have several shops accross Europe where each country is using a separate Database in RMS. To connect with the HQ I created a small batchfile that imports the registry keys for the database the user has chosen into the registry and then starts the HQ Manager. That works fine right now because we are using 1.0 where the reg-keys are always the same. In 1.3 (which we will be upgrading to shortly) things are a bit different. Here the Connection Details are encrypted and are therefore from machine to machine. That means I cannot include the registry-keys in the installation routine be...

Insert Database doesn't "see" half my queries?
I am trying to insert a database” from Access 03 into Word 03 using the database toolbar in word. When I click “Insert Database” in word, and then browse for my query, the wizard doesn’t “see” any of the queries in which I changed a field name (ex. Dept: Department). Does anyone know a workaround for this? Right now, it’s only showing about half my queries which is a little odd. Is there a different way to insert my database? Thank you! If you check Word Tools->Options->General->"Confirm conversion at open" and go through the whole insertion process aga...

truncation of customer fields
I am moving data from a legasy system that allowed 60 to 100 characters for customer name and address fields. GP only allows 60 to 30 respectively. Is there a workaround for this? For example if I used integration manager could I some how put 30 char in address then the remaining in a user field? Would that work or is there a more elegant solution? Thank You for any info. Certianly using the scripting language in IM to split the fields and use one of the address fields is one solution. You could also use extender fields but it is difficult with IM. -- Richard L. Whaley Author / Co...

Upsize AutoNumber field to SQL Server
The Upsizing Wizard Add-In in Access 97 would automatically create an Insert Trigger when upsizing to SQL Server. But when I use the built-in Upsizing Wizard in Access XP (2002), it doesn't seem to create those triggers. Is there an option I'm missing somewhere or will I have to manually create those triggers for my upsized DB? TIA! ...

Date Modified changed Money 2005
I am not sure what is going on. After I closed the Microsoft money file for the night about 2 hours later I noticed that the time change of when the files was last updated. Example. I ran the Program and money file at 8 pm. I was done by 8:30 pm. but I noticed it chaged the date of last modified to 10 pm. Sould I be worried that someone might be hacking in to my system? how can I tell who last accessed the file and where from? is Microsoft sending updates to my passport acount with out me knowing it? please help me out. Should I be worried? Thank you Mike H Have you a Pocket PC...

Database will not work on another pc?
I have a database (with some VBA modules) that I gave to someone else to look at. The databse works fine on my pc and I have installed in on three other boxes and they all worked fine. His pc, for some reason, first give a "Compile error" when he starts to run the application. I think asked him to look at the module (to see if I could help him debug) and he gets a message that says the module is locked? Plus under tools the references is grayed out? Anyone have any ideas whay this would happen on a certain pc? All of the pc's are running XP. It is a 2003 database running ...

Problem inserting calculated pivot fields into Pivot Table (2007)
Working with Excel 2007 and a Pivot Table. Created a Calculated field. The field shows in the Field List, but Excel will not let me drag the Calculated field into the Pivot Table areas (other than into the Values area). What am I doing wrong? Hi A calculated item or field, can ONLY be allocated to the data area. It's position within the data area can be modified, but it cannot be moved to any other area -- Regards Roger Govier wamiller36 wrote: > Working with Excel 2007 and a Pivot Table. Created a Calculated field. The > field shows in the Field List, b...