Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2011-07-14 : 07:17:07
|
| We have SQL Server 2008, currently replicating tables from SQL ServerA to SQL ServerB with transactional replication. Now for about few existing tables we want to change so that we can replicate views from SQL ServerA to SQL ServerB tables instead of replicating SQL ServerA tables. Trying to update the existing replication so that we can replicate SQL ServerA views.For example, currently replication is from SQL ServerA to SQL ServerB for replicating SQL ServerA tables.Now we want to replicate Orders view instead of Orders table from SQL ServerA to Orders table on SQL ServerB. And also some extra columns are present in SQL ServerA Orders view.I am trying to see how I can change the existing replication for that.I am thinking of doing this way:1. In the filter statement of the existing publication for orders table: write SQL statement as "Select * from Orders_view"2. Then reinitialize the subscriptionOr do I need to drop the table in SQL ServerB since there are new columns present in the view of the publisher and do the following way:1. Drop the Orders table in SQL serverB2. Recreate the publication and subscriptionPlease let me know which is the best way of doing this. Thanks. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-07-14 : 08:24:02
|
| The schema changes will automatically beupdated to the subscriber by default unless you have set the "Replicate schema changes" property to false on the publisher.PBUH |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2011-07-14 : 12:34:40
|
| The reason I want to replicate the data from a view is that only few columns can be copied over to the subscriber. I understand that we can also select only few columns whichever we need to replicate from the publisher but the user will have access to table with all the columns. So by using the view, the user can only be given access to that view which has very selected columns which needs to be replicated. Like I said before, I want to update the existing replication so that, that view will get replicated instead of the table. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-14 : 13:21:43
|
| As said here, replicating the view is just the definition.If you don't want users accessing certain data, define appropriate roles/permissions, or use filtering. |
|
|
|
|
|
|
SQL Server replication question, please help!