Indexed View

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Jones - 27 Sep 2006 16:53 GMT

I would like to publish data to other SQL servers, however I'd like to have
control over how the information is published. I may not include all of the
fields, may change the name of the column, or perform a simple transformation
on a column.

Is replication the best approach?

I tried to accomplish the above by creating an indexed view as a
transactional publication and subscribe to the indexed view to a physical
table. The indexed view and table have the same name and structure. When
initializing the synchronization, I receive the following error:

Cannot use DROP VIEW with 'dbo.Currency' because 'dbo.Currency' is a table.
Use DROP TABLE.

Any help is appreciated. Thanks.

Signature

David Jones

Reply to this Message

Hilary Cotter - 27 Sep 2006 17:14 GMT

You are doing it the correct way. There is an option to replicate an indexed
view as a table in SQL 2000 in the wizards, but in SQL 2005 you can only do
this through the stored procedures.

Here is an example of what it would look like

exec sp_addarticle @publication = N'test', @article = N'Currency',

@source_owner = N'dbo',

@source_object = N'Currency', @type = N'indexed view logbased',

@description = N'', @creation_script = N'',

@pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001,

@destination_table = N'myview',

@destination_owner = N'dbo', @status = 16

GO

--where currency is the name of your indexed view and the name of the
subscriber table.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

>I would like to publish data to other SQL servers, however I'd like to have
> control over how the information is published. I may not include all of
[quoted text clipped - 15 lines]
>
> Any help is appreciated. Thanks.

Reply to this Message

Mellice - 24 Apr 2008 10:39 GMT

Can you give a detailed example on how to replicate views in SQL server 2005.

> You are doing it the correct way. There is an option to replicate an indexed
> view as a table in SQL 2000 in the wizards, but in SQL 2005 you can only do
[quoted text clipped - 40 lines]
> >
> > Any help is appreciated. Thanks.

Reply to this Message