| Author |
Topic |
|
hmat911
Starting Member
8 Posts |
 Posted - 2008-03-29 : 20:08:23
|
| I need to replicate a database from one server to another and transform some of the data in particular fields (and add a few new fields) in the process. Should I use Replication for this or would DTS be better? I'm unable to figure out how to do the same transformations in Replication as in DTS. Especially adding columns in the destination database. If I point to the DTS package with the Replication wizards, it errors out.Thanks for any help you can provide!hmat911HM at 911 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-29 : 20:14:47
|
| Do you need real time sync b/w publisher and subscribers? |
 |
|
|
hmat911
Starting Member
8 Posts |
Posted - 2008-03-29 : 20:27:56
|
| Preferably, yes.HM at 911 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-29 : 20:46:38
|
| Yes you can do with Transactional ReplicationSee this one:http://www.sql-server-performance.com/articles/dba/ddl_replication_2000_2005_p1.aspx |
|
|
|
hmat911
Starting Member
8 Posts |
Posted - 2008-03-29 : 20:52:26
|
| Specifically, I'm trying to replicate data from one server to another. In the process, I need to change a int field to varchar and add an "agency identifier" on the front. For example: ID = 15 and the Agency is 0003 (no field in the table for this). The new field value would be 0003-15. Then there are other fields where the main table stores a code and I want the text value. For example: City = 10 and in the cities table CityCode 10 = Denver, so in my replicated table, I want the City to say Denver, not 10. I've got several that are just like that. I'm new to DTS and it doesn't appear to be as easy as they make it sound. Especially if you're dealing with replication.HM at 911 |
|
|
|
hmat911
Starting Member
8 Posts |
Posted - 2008-03-29 : 20:56:03
|
| Unfortunately, I can't change anything about the publishing database. Everything has to be on the subscribing end. Thanks for the quick replies. I've been beating my head against the wall with this one for days, weeks...HM at 911 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-29 : 21:02:05
|
| what do you mean? So does publisher has to be same as subscribers? Is this change to column one time- or is it often changed? Be specific |
|
|
|
hmat911
Starting Member
8 Posts |
Posted - 2008-03-29 : 21:32:57
|
| See my examples above of what I'm trying to do. The source database is what it is and I can't change it. Therefore, we need to replicate it to another server and transform it in the process. So I can have fields like I mentioned above with the city name and not the city code right in the table. If someone makes a change in the source database, I need to see that change in the destination database.Thanks again.HM at 911 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-29 : 22:19:32
|
| In that case, replication will not work nor dts. May need trigger. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-29 : 22:48:58
|
| You need to have linked server setup for triggers to work |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-29 : 23:28:15
|
| So what? |
|
|
|
|
DTS or Replication?