July 13, 2011 at 3:51 pm
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.
July 14, 2011 at 10:27 am
Any idea?
July 14, 2011 at 10:36 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.
July 14, 2011 at 11:18 am
I'm not entirely sure I follow you. DDL might be useful showing us the source articles and what you want to see at the subscriber.
If I understand you correctly why dont you manually create the SELECT * view at the subscriber and vertically partion the published article.
July 14, 2011 at 3:09 pm
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.
July 15, 2011 at 5:08 am
Any ideas? Thanks.
July 15, 2011 at 12:20 pm
Mh-397891 (7/14/2011)
Create view dbo.Orders_Viewas
Select col1,col2,col3,col4 from dbo.Orders
Since this is a published view i'm not sure it will be delivered successfully without the orders object existing first.
Mh-397891 (7/14/2011)
Create view dbo.Ordersas
Select col1,col2,col3,col4 from dbo.Orders_View
And this just makes a circular reference.
Where's your data coming from? Views don't replicate data.
July 20, 2011 at 12:03 am
Hi
If you just want a subset of columns from the order table you can simple open the article and go in and untick the columns you don't want.
Just keep in mind it will require the subcriber schema to be the same or other columns should be nullable. It really dependings on if you use scall,mcall,xcall.
You can also published the view if you want to do joins.
Cheers
Jannie
July 20, 2011 at 3:42 am
Let me see if I have this right...
Server A - Table Orders, new view Orders_View
Publication containing Orders_View as the article (select columns you require via the GUI)
Now what I would do is this -
Server B - Table Orders_Replicated, new view Orders (based on table)
Replicate to the Orders_replicated table. Users query/use the Orders view.
That's how I have read your question - you want users to use views at either end. You don't replicate from a view into a view.
July 27, 2011 at 1:28 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.
Hope I made myself clear.
Thanks.
July 27, 2011 at 7:50 pm
Hi
If you replicate a view replication is automatically going to add the underlying dependant objects.
So in terms of volumne there is no difference.
If you want to publish specific columns you can do so by specifying the columns of the table you want to replicate.
By default when setting up replication it will add all the columns.
You can use sp_addarticlecolumn ( I think ) to specifiy columns or remove them through the GUI.
Please note, if you remove columns through the GUI it will cause reninitialize. Adding a column is fine.
But removing is a problem.
Cheers
Jannie
July 28, 2011 at 3:43 am
Mh-397891 (7/27/2011)
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.
Hope I made myself clear.
Thanks.
Yes if you replicate it to to a table called Orders_Replicated on the subscriber, then base a view on it and call it orders. Don't replicate to the view, replicate to your orders_replicate table. That will stop you having to change the stored procs.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply