SQL Server Transaction Replication For Indexed Views

I am doing transaction replication for indexed views. I have other replicating schemabound views that reference the indexed views using the NOEXPAND hint. Even though I call sp_addarticle for the NOEXPANDing views after calling sp_addarticle for the indexed views, I'm getting the error:

Hint 'noexpand' on object '...' is invalid.

because SQL Server is trying to create the NOEXPANDing view at the target server before creating the index on the indexed view.

Is there a way to force SQL Server to finish replicating the indexed view indexes before starting on the NOEXPANDing views?

Answers


Have a look on the distributor database and you'll find scripts for pre and post replication. These are straight forward sql scripts so you can modify these and put whatever you like in them.

That means you could modify the pre-repl script to avoid the error and modify the post-repl script to add the noexpanding view after the index has been created.


I think i've found an easier way.

When adding an indexed view to replication using the GUI, will only copy across the schema definition meaning any Stored Procs that try to access these views will error if they contain a NOEXPAND hint.

Now I guess you can mess around with the pre/post scripts but using this article from MSDN: Indexed Views - Replications - NoExpand Hint there seems a simpler option.

You can replicate the indexes by changing the script for sp_addarticle and replace @schema_option from 0x0000000008000001 to 0x0000000008000051. The following example allows both clustered and non-clustered index to be generated at the subscriber.

This pulls across the indexes automatically and has meant less scripting too.


Need Your Help

Player does not properly get odometry data for Create in multithreaded application

multithreading player robotics robot mobile-robots

I am using Player (Player/Stage) on the iRobot Create. The interface for getting odometry data from the robot is fairly simple: call playerc_client_read, and then if you've properly subscribed a

MVC Moving data form Table to Input

javascript html model-view-controller

I'm trying to move data from a selected row in a table to a form in MVC. I'm using a jquery click event to retrieve the data from my table and move it to my form. The click event is working but it is

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.