| Author |
Topic |
|
Harry C
Posting Yak Master
148 Posts |
 Posted - 2008-08-20 : 00:42:50
|
| I have two different databases, one is a SQL Server 2000 DB that I have complete access and control over. The other is a SQL Server 2005 database that is on a shared host. I have two tables that I need to synchronize every night. Data can be changed in either database, so I need to have the record that has the greatest datetime be the one that is kept. Both tables have primary keys. What is the best solution for something like this? I can set up the two tables to be identical initially, but what do I need after that? How can I accomplish what I need. I am just looking for starter points, not really "code" per se. Thanks for any help you can provide.HC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-20 : 01:03:45
|
| If its for nightly run you can create a DTS package for this which compares the tables and only retain latest data deleting all others. you could then create a sql job to call the dts each night. |
 |
|
|
Harry C
Posting Yak Master
148 Posts |
Posted - 2008-08-20 : 08:20:42
|
| Would you be able to give me a little more detail? Such as, Obviously I would create teh DTS package in the SQL Server 2000 DB. But I am not exactly sure how I would compare the tables? Thanks again |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-20 : 09:14:52
|
| You can use transactional replication. |
|
|
|
Harry C
Posting Yak Master
148 Posts |
Posted - 2008-08-20 : 09:42:50
|
| Can I use replication if one of the databases is SQL Server 2005? ThanksHC |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-20 : 10:19:37
|
| Yes you can use transactional replication from 2000 to 2005 |
|
|
|
Harry C
Posting Yak Master
148 Posts |
Posted - 2008-08-20 : 10:22:24
|
| Ok, I will check into that. One last question. With transactional replication, can I have the updates take place in both databases, and have both databases merge? Basically, at the end, I need both tables to have to same exact (correct) data. Thanks for taking the time to look at this.HC |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-20 : 13:04:17
|
| Never tried with Merge replication and Transactional rep with updatable subs.It involves with triggers so may not work.Test it. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-20 : 23:05:49
|
| What kind of control do you have on shared host server? You need proper permission to set replication. |
|
|
|
|
DTS, or Replication