Replication of views
-
Hi everyone,
Is there a way to automate replication so that any new views I create will automatically be added to an existing publication?
Also, I have noticed this odd behavior: I can create a transactional publication & subscription that includes a table and a view of that table. When I add records to the table, the subscriber's table reflects the added records. No problem. But when I alter the view, the subscriber's view never shows the new, altered view. Is this by design, or have I missed something?
Thanks for any help on this
-
you missed something < grin >
essentially replication is of data, not objects, so if you add or modify objects you need to update the schema. I do this through T-SQL - check out BOL for the code you need to remove the object, modify it, add it back and then snapshot the item. To add new items do the same without the remove.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
OK, I'm the first to admit I'm still new at this.
I consulted BOL but got lost in there. Could you provide an example of the above-mentioned code please? Thanks.
-
will do - haven't forgotten!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Take object out of replication:-
Use publishing_database
go
--
exec dbo.sp_dropsubscription @publication='publication_name',@article='object_name',@subscriber='subscriber_server_name'
go
exec dbo.sp_droparticle @publication='publication_name',@article='object_name',@force_invalidate_snapshot=1
go
exec dbo.sp_refreshsubscriptions 'publication_name'
go
do whatever you want to the object:-
then put it back
Use publishing_database
go
--
-- put them back
--
exec dbo.sp_addarticle 'publication_name',@article='object_name',@source_table='table_name',@destination_table='table_name',@force_invalidate_snapshot=1
go
exec dbo.sp_refreshsubscriptions 'publication_name'
go
--
-- do the snapshot thing
--
exec msdb.dbo.sp_start_job @job_name= 'the snapshot job name in jobs'
go
--
Notes:-
I generally have to do this with tables, look up the procs in bol .. use @source_object for procs, views etc.
If you set up a test you'll find it's actually quite simple, it's just that BOL doesn't really explain the process only the procs!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply