Indexed Views - Clustered Index is NOT replicated (not scr..

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 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.
>
> .
>
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr.. 

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 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.
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr.. 

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
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr.. 

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 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.
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr.. 

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 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.
>
> .
>
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr.. 

That's my fault...I saw the same behavior. When I did it with @type =
'indexed view schema only' did create the indexed view.
--
Ben

On Mar 3, 6:32 pm, jk wrote:
> 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.
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr.. 

You are right. It does seem to work. I did it on the same database (and
server), and it worked, while the other articles dont. It has to be some sort
of glitch because all the settings are the same. Will have to experiment some
more as this test shows that it should work.

"Ben Thul" wrote:

> That's my fault...I saw the same behavior. When I did it with @type =
> 'indexed view schema only' did create the indexed view.
> --
> Ben
>
> On Mar 3, 6:32 pm, jk wrote:
> > 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.
>
> .
>
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr.. 

One thing that I thought of...try creating the view manually at the
subscriber. Maybe there's some weird condition that prevents the
indexed view from being created there. If that's the case, it could
be detected by replication and lead to the situation you're seeing.
Just a thought...
--
Ben

On Mar 4, 10:37 am, jk wrote:
> You are right. It does seem to work. I did it on the same database (and
> server), and it worked, while the other articles dont. It has to be some sort
> of glitch because all the settings are the same. Will have to experiment some
> more as this test shows that it should work.
 >> Stay informed about: Indexed Views - Clustered Index is NOT replicated (not scr..