September 25, 2008 at 9:10 am
Can I do transactional replication of database views? For example: if the view is based on table1, table2, table3, can we just replicate view into subscriber even if those 3 tables are not present in the subscriber. Thanks!
September 25, 2008 at 1:16 pm
Perhaps this response will help with your question.
1. Data is stored in tables.
2. Views display data stored in tables.
How do you intend to 'replicate' (not 'remotely view') a view on content WITHOUT importing the content?
To my knowledge, to avoid replicating the three tables, you would need to convert the view to table(s) and then replicate them.
HOWEVER, here is an answer that may give you what you really want:
You can replicate the 3 tables, ONLY those fields which preserve the keys stored in the table and the fields used by the views, ignoring all other table content.
To do this, you would need to replicate the three tables, and drill down in the selection to specify which fields are to be replicated. The net result is to only replicate your view data, which is what I think you were really asking.
Hope this helps.
Steve
September 25, 2008 at 1:40 pm
MH,
I tried to do this on one of my virtual servers just to see if I could make it work. It can't be done because (for starters) you can't create an indexed view without the underlying objects.
As Steve said, you'll need to replicate either the indexed view to a table on the subscriber or all of the objects.
Hope that helps.
Chris.
Chris.
September 25, 2008 at 2:07 pm
steve smith (9/25/2008)
Perhaps this response will help with your question.1. Data is stored in tables.
2. Views display data stored in tables.
How do you intend to 'replicate' (not 'remotely view') a view on content WITHOUT importing the content?
To my knowledge, to avoid replicating the three tables, you would need to convert the view to table(s) and then replicate them.
HOWEVER, here is an answer that may give you what you really want:
You can replicate the 3 tables, ONLY those fields which preserve the keys stored in the table and the fields used by the views, ignoring all other table content.
To do this, you would need to replicate the three tables, and drill down in the selection to specify which fields are to be replicated. The net result is to only replicate your view data, which is what I think you were really asking.
Hope this helps.
Steve
Hi steve, thanks for your reply. Could you please explain what you meant by:
"convert the view to table(s) and then replicate them"
Thanks!
October 2, 2008 at 9:42 am
Sorry to take so long to respond - I've been away....
Anyway, to answer your question, let's pretend you only need the content used by the view:
Let's say that your view actually uses an 'inner join' between three tables, using a total of 15 fields in the view itself, and another 6 fields as the keys to link the tables together.
You would replicate the three tables, including ONLY the key fields and the fields referenced by the view.
Then, on the replication database, you would replicate the view coding. The result is that the view would work because all the underlying data is there.
For instance, let's say that table MainSourceofView has 25 fields, but only 5 are used directly by the view (and another 4 represent the key fields). In the replication wizard, you would select the table named MainSourceofView, expand the table, and select the key fields and the data fields included in the view, EXCLUDING (no check in the checkbox) all other fields. You would see 10 check marks - one for the table itself, and 9 for the individual selected fields.
Repeat the process for the other two tables, and complete the wizard, providing the type of replication you require (snapshot or transactional, but probably snapshot). You can then include the view in your replication and everything should flow smoothly (assuming correct settings for connectivity, etc.)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply