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.

--

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.

--
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



[quoted text, click to view]