sql server

Last Post 15 Jul 2011 06:45 AM by rm. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqlserverdeveloper
New Member
New Member

--
14 Jul 2011 04:21 AM
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 subscription

Or 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 serverB
2. Recreate the publication and subscription Please let me know which is the best way of doing this. Thanks.
gunneyk
New Member
New Member

--
14 Jul 2011 05:48 AM
You don't need to change a thing. Views are not like tables in that unless they are Indexed Views (special meaning) they are simply sql statements that get wrapped in with the operations that run against them. Meaning that if I update a view it really updates the tables below and not the view. The data lives in the tables and thus that is what needs to be replicated. The replication will read the entries in the tran log that affect the tables and push those commands over to the subscriber to be applied. Those commands may not look like what you executed but it doesn't matter. In the end the data in the underlying tables will be the same and that is what counts. Does that make sense? So as long as you are eplicating all the tables that the views touch you will be ok.
sqlserverdeveloper
New Member
New Member

--
14 Jul 2011 09:32 AM
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.
rm
New Member
New Member

--
14 Jul 2011 10:12 AM
You can replicate views but it's just definition of views, nothing to do with data replication.
russellb
New Member
New Member

--
14 Jul 2011 10:18 AM

You can define the permissions you want to allow at the subscriber.  I replicate the underlying tables and create the same view on the subscribers.

sqlserverdeveloper
New Member
New Member

--
14 Jul 2011 02:10 PM
Can I do something like this:

The view is basically selecting columns from orders table.
For example in the publisher:
Create view dbo.Orders_View
as
Select col1,col2,col3,col4 from dbo.Orders

Then I want to replicate that view dbo.Orders_View from publisher to subscriber.

This replication will be a continuous transactional replication.

Then I will create another view with the name Orders(same name as present table Orders in subscriber, so that the existing stored procs does'nt need to be updated)
The following is the view script in the subscriber:

Create view dbo.Orders
as
Select col1,col2,col3,col4 from dbo.Orders_View

This view dbo.Orders which has been created in the subscriber will be considered as a regular table.

Thanks.
sqlserverdeveloper
New Member
New Member

--
15 Jul 2011 04:09 AM
Any ideas? Thanks.
rm
New Member
New Member

--
15 Jul 2011 06:45 AM
You still have to replicate underlying table to subscriber. Like Russell said, just grant select permission on view but not table on subscriber.
You are not authorized to post a reply.

Acceptable Use Policy