Replicating data across schemas

[Chakravarthy]

Hi,

I have a situation where a row added to a table in the application of one
SQL Server 2000 database needs to have this data replicated to
corresponding tables with a different schema altogehter (which i can map),
in another SQL Server 2000 database within a VPN.

I assume Merge replication works only for matching or subsets of schemas.

Since this is mapping of data across different tables, i guess using
Triggers or SSIS packages would be the alternatives.

Which is the better approach ?

Regards,
Chakravrthy

[tbradshaw via SQLMonster.com]

Hello Chakravrthy,

You don't mention how immediate the data needs to be replicated. Once-a-day?
Once-a-minute? You could set up a DTS scheduled job to copy over the new
information as needed to multiple destinations.

You could use an INSERT trigger, but it's not as visible, requires linked
servers, and if it fails (for example, rebooting remote server), how would it
recover, or notify you? SQL 2000 doesn't use the TRY/CATCH that SQL 2005
enjoys, so you'd have to find other ways around it.

Let us know how you make out.

Best Regards,
Tom

Thomas Bradshaw
Data Integration Services
MyWebGrocer LLC