Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 DTS, or Replication

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.
Go to Top of Page

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
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-20 : 09:14:52
You can use transactional replication.
Go to Top of Page

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? Thanks

HC
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -