Prev: SQL database not accessible during transactional replication
Next: skiperrors
From: jk on 2 Mar 2010 11:41
Ben, I've used a few, leaving status at default.

exec sp_addarticle @publication = N'all_objects', @article =
N'ImpactCost_KD_WI', @source_owner = N'dbo', @source_object =
N'theIndexedViewArticle', @type = N'indexed view schema only', @description =
N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000011, @destination_table = N'ImpactCost_KD_WI',
@destination_owner = N'dbo', @status = 16


I've also tried @schema_option = 0x0000000008000041 and @schema_option =
0x0000000008000051 with no success.

Thanks.

"Ben Thul" wrote:

> Can you post the sp_addarticle invocation that you used? I'm
> specifically interested in what you used for the @type and
> @schema_option parameters. Thanks!
> --
> Ben
> On Mar 1, 3:06 pm, jk <j...(a)discussions.microsoft.com> wrote:
> > Using SQL 2005 SP2.
> > I have a publication that contains indexed views, and some other objects
> > that query the indexed view using WITH (NOEXPAND). Currently replication
> > fails because the CLUSTERED INDEX on the view is NOT replicated. I've
> > experimented with various schema options but nothing changes. The view is
> > replicated but not the clustered index on that view.
> >
> > I've seen some discussion on replicating indexed views to a table, but I
> > would like to replicate indexed view schema fully. (Including the clustered
> > index on that view).
> > Is there a way to make this work?
> >
> > Thanks.
>
> .
>
From: Ben Thul on 2 Mar 2010 12:45
I would have thought that setting the 0x10 bit in the @schema_option
parameter would have done the trick, but apparently not. One thing
that I noticed from BOL is that with @type = N'indexed view schema
only', the base tables also need to be published. Is this the case?
Regardless, have you tried @type = N'indexed view logbased'?
--
Ben

On Mar 2, 10:41 am, jk <j...(a)discussions.microsoft.com> wrote:
> Ben, I've used a few, leaving status at default.
>
> exec sp_addarticle @publication = N'all_objects', @article =
> N'ImpactCost_KD_WI', @source_owner = N'dbo', @source_object =
> N'theIndexedViewArticle', @type = N'indexed view schema only', @description =
> N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
> 0x0000000008000011, @destination_table = N'ImpactCost_KD_WI',
> @destination_owner = N'dbo', @status = 16
>
> I've also tried @schema_option = 0x0000000008000041 and @schema_option =
> 0x0000000008000051 with no success.
>
> Thanks.
From: jk on 3 Mar 2010 11:54
Yes, according to BOL that schema option should have worked. I suspect it may
be a 'feature'. I did not try 'indexed view logbased' because, as I've
mentioned, I do not want a table on the subscriber - I want the view.


"Ben Thul" wrote:

> I would have thought that setting the 0x10 bit in the @schema_option
> parameter would have done the trick, but apparently not. One thing
> that I noticed from BOL is that with @type = N'indexed view schema
> only', the base tables also need to be published. Is this the case?
> Regardless, have you tried @type = N'indexed view logbased'?
> --
> Ben
From: Ben Thul on 3 Mar 2010 14:13
I'm running out of ideas, but I did come up with a proof of concept
that it is possible to replicate a view with its clustered index.
Here's the script:
/*******************
BEGIN SCRIPT
*******************/
use <publisher_db, sysname,>
go
if (select object_id('dbo.my_view', 'V')) is not null
drop view dbo.my_view
go
if (select object_id('dbo.base_table', 'U')) is not null
drop table dbo.base_table
go
create table base_table (
a int not null PRIMARY KEY,
b varchar(40)
)
go

create view my_view
with schemabinding
as
select a, b
from dbo.base_table
where a > 5
go
create unique clustered index idx_my_view on my_view (a)
go
exec sp_replicationdboption
@dbname = '<publisher_db, sysname,>',
@optname = 'publish',
@value = 'true'
exec sp_addpublication @publication = 'test_pub',
@status = 'active'
exec sp_addpublication_snapshot @publication = 'test_pub'

exec sp_addarticle @publication='test_pub',
@article = 'base_table',
@source_table = 'base_table',
@destination_table = 'base_table',
@type = 'logbased',
@schema_option = 0x000000000803509F,
@destination_owner = 'dbo',
@source_owner = 'dbo'
exec sp_addarticle @publication='test_pub',
@article = 'my_view',
@source_table = 'my_view',
@destination_table = 'my_view',
@type = 'indexed view logbased',
@schema_option = 0x0000000008000011,
@destination_owner = 'dbo',
@source_owner = 'dbo'
exec sp_addsubscription @publication = 'test_pub',
@subscriber = 'rvntestsql04\dbaonly',
@destination_db = '<subscriber_db, sysname,>',
@article = 'all',
@sync_type = 'automatic',
@subscription_type = 'push'
/*******************
END SCRIPT
*******************/
Of course, just like with all code you find on the internet, don't run
it in production, understand it before you run it, etc. But when I
ran this on my test server, the view was replicated with the clustered
index. I'd be curious to see if you meet with the same fate on your
system. If you do, I'd then be curious to see what differences there
are between the publications and articles.
--
Ben

On Mar 3, 10:54 am, jk <j...(a)discussions.microsoft.com> wrote:
> Yes, according to BOL that schema option should have worked. I suspect it may
> be  a 'feature'. I did not try 'indexed view logbased' because, as I've
> mentioned, I do not want a table on the subscriber - I want the view.

From: jk on 3 Mar 2010 19:32
Thanks, Ben. Your test worked, except as I expected, per the @type = 'indexed
view logbased' in your script, it created a TABLE on the subscriber instead
of the VIEW I wanted.

Jan.

"Ben Thul" wrote:

> I'm running out of ideas, but I did come up with a proof of concept
> that it is possible to replicate a view with its clustered index.
> Here's the script:
> /*******************
> BEGIN SCRIPT
> *******************/
> use <publisher_db, sysname,>
> go
> if (select object_id('dbo.my_view', 'V')) is not null
> drop view dbo.my_view
> go
> if (select object_id('dbo.base_table', 'U')) is not null
> drop table dbo.base_table
> go
> create table base_table (
> a int not null PRIMARY KEY,
> b varchar(40)
> )
> go
>
> create view my_view
> with schemabinding
> as
> select a, b
> from dbo.base_table
> where a > 5
> go
> create unique clustered index idx_my_view on my_view (a)
> go
> exec sp_replicationdboption
> @dbname = '<publisher_db, sysname,>',
> @optname = 'publish',
> @value = 'true'
> exec sp_addpublication @publication = 'test_pub',
> @status = 'active'
> exec sp_addpublication_snapshot @publication = 'test_pub'
>
> exec sp_addarticle @publication='test_pub',
> @article = 'base_table',
> @source_table = 'base_table',
> @destination_table = 'base_table',
> @type = 'logbased',
> @schema_option = 0x000000000803509F,
> @destination_owner = 'dbo',
> @source_owner = 'dbo'
> exec sp_addarticle @publication='test_pub',
> @article = 'my_view',
> @source_table = 'my_view',
> @destination_table = 'my_view',
> @type = 'indexed view logbased',
> @schema_option = 0x0000000008000011,
> @destination_owner = 'dbo',
> @source_owner = 'dbo'
> exec sp_addsubscription @publication = 'test_pub',
> @subscriber = 'rvntestsql04\dbaonly',
> @destination_db = '<subscriber_db, sysname,>',
> @article = 'all',
> @sync_type = 'automatic',
> @subscription_type = 'push'
> /*******************
> END SCRIPT
> *******************/
> Of course, just like with all code you find on the internet, don't run
> it in production, understand it before you run it, etc. But when I
> ran this on my test server, the view was replicated with the clustered
> index. I'd be curious to see if you meet with the same fate on your
> system. If you do, I'd then be curious to see what differences there
> are between the publications and articles.
> --
> Ben
>
> On Mar 3, 10:54 am, jk <j...(a)discussions.microsoft.com> wrote:
> > Yes, according to BOL that schema option should have worked. I suspect it may
> > be a 'feature'. I did not try 'indexed view logbased' because, as I've
> > mentioned, I do not want a table on the subscriber - I want the view.
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: SQL database not accessible during transactional replication
Next: skiperrors