Okay, I need some help synchronizing two tables. Basically, I want all the changes in one database to be reflected in the other (but not necessarily vice versa). The table uses an IDENTITY as a primary key and it does have a DBTIMESTAMP field.
It would easy to write an SP to do that, but the names of the servers and the databases for the source/destination are going to be dynamic. If you can show me how to write an SP that gets around that issue (without using dynamic SQL) then I would accept that as an answer.
If however, that's not possible, then I would like to know how to configure a DTS package to accomplish the same thing. With the GUI, I can see how to add a task to copy data, but not really to remove or update the changed data. So, if you could show me how to do that, then I'd accept that as an answer also.
Let me know if you need any more information.
Dex*
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Yeah, that's how I would do it, but that doesn't work if the server / DBs aren't known. First I have to remove any records that don't belong in the destination table, then copy the new records into the destination table, and then update the changed records in the destination table.
So, for example,
DELETE * FROM Remote.RemoteDB.dbo.tblWha
How do I provide values for Remote/RemoteDB and Main/MainDB at runtime?
Dex*
Yes it is possible:
Adding DTS Lookups and Global Variables:
http://doc.ddart.net/mssql
Global Variables in DTS:
http://doc.ddart.net/mssql
DTS and global variables should solve your problem.
Yes,
Setup of merge replication:
http://www.databasejournal
Validation of Merge Replication:
http://www.mssqlcity.com/A
Okay, so you know how I was going to use "Snapshot Replication" for the small, infrequently updated things (restricting updates to only the central DB), and then "Merge Replication" for the things that need to be consistent at all sites?
Well, I don't think that is going to work out. Consider the following situation:
I have a table that will be handled by the "Snapshot Replication". Let's call it "tblObjectTypes". Very infrequently updated. Then I have another table that will be handled by "Merge Replication". Let's call it "tblObjects". There is a many-to-one relationship between tblObjectTypes and tblObjects.
Now, say at the central DB, they add a new ObjectType record. And then they add a new Object record that uses this new ObjectType. Won't it fail if the "Merge Replication" happens before the "Snapshot Replication" because the new object type has not been replicated, but a record that references it is trying to be replicated violating a foreign key constraint?
How do I handle that situation? Since that situation, although rare, could definitely happen, does that mean I should just use Merge Replication for everything?
Dex*
Why don't you also move tblObjectTypes to merge replication as it is infrequently updated.... this will not create a major overhead then it will all work together in the correct order you require every time.
The other alternatives would be dropping the FK on the dependant sites (if this is an acceptable solution).
@danblake:
Well, I have lots of tables that would fall into the "tblObjectTypes" category. So, I guess the answer is that I need to use Merge Replication for everything, which could actually simplify things. And, since Merge Replication doesn't really incur much overhead (at least, it doesn't seem to) when there are no changes, that is totally acceptable.
So, "Merge Replication" takes care of 2 of the 3 scenarios I have to deal with. Now, I just have one more: Some of the data that is inserted at a remote site needs to be replicated back to the Central DB for reporting, but it doesn't need to be pushed back out to the other remote sites. Is that possible? What do I need to do to make that happen?
Dex*
@danblake:
Okay, so in that situation, the Remote DB publish the data, and the main DB subscribes to it? And that allows each remote site to have their own data, and the central DB to have everything?
How do I keep the Central DB from pushing it's data back to the Publishing database? If that happens, then every DB will end up with all the data, which isn't what I want.
Dex*
Okay, so in that situation, the Remote DB publish the data, and the main DB subscribes to it? And that allows each remote site to have their own data, and the central DB to have everything?
Yes.
How do I keep the Central DB from pushing it's data back to the Publishing database? If that happens, then every DB will end up with all the data, which isn't what I want.
The publishers are just publishing the information to send back to the central database system, you can use
the horzontal filtering so any subscribed information is filtered against each sites location identifier.
Have a look here on the purpose of filtering data:
http://www.developer.com/d
Lots of replication resources here...Including the model I am describing.
http://www.eduonline.com.a
What you can do is setup the article filtering by the sp_articlefilter (refering to BOL) or by using EM (It is possible to do this using the Advanced Settings in the Setup Replication Wizard)
To see a diagram, of the row filtering at each site in use:
http://www.eduonline.com.a
You're welcome. I haven't gotten the replication completely working yet, but I'm working on something else for the time being. I might have more questions when I get back to it, but in the meantime, I don't want to hold up the points. Especially since I've got more to give... ;)
So, keep your eyes out for more Q's from me... :)
Dex*
Business Accounts
Answer for Membership
by: wytcomPosted on 2004-03-09 at 12:10:09ID: 10554047
You could use a Execute SQL Task object in your DTS package to clear the destination table and copy the data from the source table. You would still have to explicitly reference the database names though. To treat the source/destination as dynamic, you could put that information in the database somewhere. Then write the SQL in the Execute SQL Task object to get these locations and then do the clear and copy.
Right click on your Execute SQL Task object in your DTS designer, then select properties. That takes you to the editor for adding your SQL code.