Hi,
I am using sql server 2000. I need to get only updated records from remote server and need to insert that record in my local server on daily basis. But that table did not have created date or modified date field.
A:
If you can't change the remote server's database, your best option may be to come up with some sort of hash function on the values of a given row, compare the old and new tables, and pull only the ones where function(oldrow) != function(newrow).
You can also just do a direct comparison of the columns in question, and copy that record over when not all the columns in question are the same between old and new.
This means that you cannot modify values in the new table, or they'll get overwritten daily from the old. If this is an issue, you'll need another table in which to cache the old table's values from the day before; then you'll be able to tell whether old, new, or both were modified in the interim.
Jay
2010-01-28 05:17:45
I cannot modify the table structure in remote server. and i cannot create any trigger in remote server. only i can able to read the table records.
Srinivasan
2010-01-28 07:25:42
I understand that those are the constraints, and I've provided your options, given these constraints. I recognize that it is not ideal. Still, I think 4 million rows is probably manageable for a daily update. If you can install something on the remote server, but just can't modify the database at all, you could speed things up by running a service on that machine that will do the work of comparison and just report the IDs of affected rows, so the update query can pull data across the wire only for the few affected rows.
Jay
2010-01-28 07:38:08
A:
You need one of the following
- a column in the table which flag new or updated records in a fashion or other (lastupdate_timestamp, incremental update counter...)
- some trigger on Insert and Update, on the table, which produces some side-effect such as adding the corresponding row id into a separate table
You can also compare row-by-row the data from the remote server against that of the production server to get the list of new or updated rows... Such a differential update can also be produced by comparing some hash value, one per row, computed from the values of all columns for the row.
Barring one the above, and barring some MS-SQL built-in replication setup, the only other possibility I can think of is [not pretty]:
- parsing the SQL Log to identify updates and addition to the table. This requires specialized software; I'm not even sure if the Log file format is published/documented, though I have seen this types of tools. Frankly this approach is more one for forensic-type situations...
mjv
2010-01-28 05:24:25
I cannot compare each and every row in the table. Because the record size is in the range of million(4 million rows in romote server). The problem in this is, may be one or two record is affected(inserted or updated or deleted). I need to sync both tables(in server and in local) on daily basis.
Srinivasan
2010-01-28 07:23:46
I cannot create trigger on that server. Because i have only permission to read that table.
Srinivasan
2010-01-28 07:31:28
+2
A:
Use Transactional Replication.
Update
If you cannot do administrative operations on the source then you'll going to have to read all the data every day. Since you cannot detect changes (and keep in mind that even if you'd have a timestamp you still wouldn't be able to detect changes because there is no way to detect deletes with a timestamp) then you have to read every row every time you sync. And if you read every row, then the simplest solution is to just replace all the data you have with the new snapshot.
Remus Rusanu
2010-01-28 06:58:58
I assume this must be configured, and it sounds like the the OP has hands tied with respect to the remote server.
Jay
2010-01-28 07:40:13
A:
I solved this by using tablediff utility which will compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.
See the link.
Srinivasan
2010-01-28 08:56:34
You realize for this utility to work it requires grabbing a copy of all 4 million records from both the source and destination?
Chris Lively
2010-01-28 19:17:55
Initially when i sync these two tables(which has millions of record) by direct field to field comparison, it takes 1 hour to finish it.But by using the tablediff utility it takes only 1 minutes to do it.
Srinivasan
2010-01-29 04:40:24
A:
TO sum up:
- You have an older remote db server that you can't modify anything in (such as tables, triggers, etc).
- You can't use replication.
- The data itself has no indication of date/time it was last modified.
- You don't want to pull the entire table down each time.
That leaves us with an impossible situation.
You're only option if the first 3 items above are true is to pull the entire table. Even if they did have a modified date/time column, you wouldn't detect deletes. Which leaves us back at square one.
Go talk to your boss and ask for better requirements. Maybe something that can be done this time.
Chris Lively
2010-01-28 19:16:10