some records are not getting synched
RBohannon
Posts: 25
We are using the SDC 6.0 programmatically to compare yesterday's backup to today's backup and produce a SQL script of the changes at the store. The SQL script is later executed on a remote server to produce a consolidated database for all stores.
One particular table in our database is a financial daily summary table. The PK is compound with a varchar(8) field and a datetime field. The varchar(8) field is a location code and is typically identical for all records in the table. The datetime field has a date value for each date that the store has been open, the time values are all zero. The rest of the columns are numerical and contain financial summary information.
At the end of the day, the record with today's date gets populated with financial data and a record with all zeros in the financial fields gets generated with tomorrow's date in the PK.
The problem is occasionally the UPDATE statement is not getting generated resulting in random dates with all zeros in the financial fields in our central database.
The collation is the same between the two databases and there are no trailing spaces.
Do you know how this could happen? Were there any bug fixes in 6.1 or 6.11 that addressed this issue specifically?
Thank you.
One particular table in our database is a financial daily summary table. The PK is compound with a varchar(8) field and a datetime field. The varchar(8) field is a location code and is typically identical for all records in the table. The datetime field has a date value for each date that the store has been open, the time values are all zero. The rest of the columns are numerical and contain financial summary information.
At the end of the day, the record with today's date gets populated with financial data and a record with all zeros in the financial fields gets generated with tomorrow's date in the PK.
The problem is occasionally the UPDATE statement is not getting generated resulting in random dates with all zeros in the financial fields in our central database.
The collation is the same between the two databases and there are no trailing spaces.
Do you know how this could happen? Were there any bug fixes in 6.1 or 6.11 that addressed this issue specifically?
Thank you.
Comments
Does this table map automatically in the SQL Data Compare UI? If not, it could be that the order of the columns comprising the compound key are different. If this is the case, the tables cannot be mapped and there is no way of manually mapping them if the comparison is done between a live database and a backup -- with backups you're not allowed to map tables and columns manually.
I hope that this is the problem. If it is, just recreating the table with the compound key columns in the same order will fix it.
The tables do map because we receive 99% of the changes. It's just occasionally a change is not reported.
Thanks.
The only possibility I can think of at the moment is that the missing record is getting inserted or updated during the time the backup happens. If this happens, SQL Server appends a mini transaction log backup to the full backup with any changes that happen during the backup. SQL Data Compare doesn't currently use this, so there's a chance that data changing during the backup may be missed in a comparison.
Red Gate
- Insert record
- Perform backup 1
- .... one day later...
- Start backup 2
- Update record with previous day's data
- Backup 2 completes
As a first step, I'd check to see if your backups are running at the same time as the insert / update - if not, then we can rule it out as a possible cause.
Red Gate
If you've got a couple of backups that exhibit the problem, and you're able to send them to us, I can try and see what's going on. Let me know if so and I'll arrange some means of you getting them to us.
Thanks,
Robert
Red Gate