I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:
1. The article is dropped from the publication
2. the object is then changed
3. The article is added back to the publication
My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.
Thanks,
Bill
Are you using SQL 2000 and 2005? In SQL 2005, you don't need to drop/recreate articles in order to change the schema. You can directly do ALTER TABLE/VIEW/FUNCTION. For more info, please take a look at BOL http://msdn2.microsoft.com/en-us/library/ms151870.aspx.
Peng
|||I am using 2005, but that will not work, as the publication only contains programmability objects, the underlying base tables are in another publication.|||Not sure what you mean by that will not work. What Peng means is that when you need to change one your progammability article like stored proc or view etc, you can just run alter view or alter proc ... and this DDL action will be replicated when using SQL 2005. so you dont need to drop the article, alter it and readd it. Hence you will not even need to reinitialize your subscriptions.|||When you execute a DDL statement against a view that: is in a publication without any tables in that publication, the query will run but will not complete execution.|||This is a known issue and the workaround is to add a dummy table in that publication.|||When you say create a dummy table, what exactly do you mean? Is this just the "real" table name with one column or something else?|||Correct, create a non-necessary table that means absolutely nothing. i.e.
create table dbo.t1 (col1 int primary key, col2 int)
Add this as an article to your publication, then your DDL statements should work.
