Replicating the result set of a view
-
Hello room,
I created a view and I want the result set (records) of the view to be replicated to another server.
I have tried replicating the view by checking the view in the replication wizards but it seems that only the definition of the view is being replicated.
Is there away that I can fulfill the desired result through replications. That to store the result of the view to a next table in the replication destination.
I am using transaction replication.
Thank a lot.
TJ
-
To do this through transactional replication, you will need to include the view and all of the tables the view uses.
Transactional replication reads the transaction log of your database and sends commands to the subscriber to insert, update, and delete records. Since a view cannot be directly inserted, updated, or deleted, there are no transactions to replicate.
You could "cheat" and set up replication to read the log on the publisher and then run a DTS package to transfer the updates and deletes, but this gets complicated and to get the correct results will be rather difficult.
If the view is small enough, use SSIS to periodically insert and update the data. If you have a field or two that you can use as a primary key, the SSIS Slowly Changing Dimension wizard can make a simply insert / update process pretty easy to write.
-
If the view can be converted to an "indexed view" it *is* possible to replicated the view as a table.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply