SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 DTS, or Replication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Harry C
Posting Yak Master

147 Posts

Posted - 08/20/2008 :  00:42:50  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 08/20/2008 :  01:03:45  Show Profile  Reply with Quote
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

147 Posts

Posted - 08/20/2008 :  08:20:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/20/2008 :  09:14:52  Show Profile  Reply with Quote
You can use transactional replication.
Go to Top of Page

Harry C
Posting Yak Master

147 Posts

Posted - 08/20/2008 :  09:42:50  Show Profile  Reply with Quote
Can I use replication if one of the databases is SQL Server 2005? Thanks

HC
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/20/2008 :  10:19:37  Show Profile  Reply with Quote
Yes you can use transactional replication from 2000 to 2005
Go to Top of Page

Harry C
Posting Yak Master

147 Posts

Posted - 08/20/2008 :  10:22:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/20/2008 :  13:04:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 08/20/2008 :  23:05:49  Show Profile  Reply with Quote
What kind of control do you have on shared host server? You need proper permission to set replication.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000