Looking for table / view that will tell me if I need to reinitialize subscription

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.

Looking for information regading merge replication with sql 2000 and msde 2000

Hi
we are using merge replication with anonymous subscription.
- Window 2000 and sql 2000 server (publisher and distributor)
- MSDE 2000 subscriber.
- using Active X-Control for subscription
-I have put subscriber never expire option in the property of
publisher.
Replication running without any problem in normal case but we received
some problem on the following scenario:
-I set up the subscriber call "TEST01" and synchronize it it run
without any problem. Because of some other reasons I need to change
the name of the computer, after that when I synchronize it does not
work, even if I change back the computer name to the original name it
stop working how can I fix this problem.
-If I want to delete the subscription total and generate the new one
how can I do this. I try to delete the subscription from the
publisher, distributor and delete the subscriber database for the
subscriber and create new database in subscriber, and click
synchronize, it still doesn't work- how can I solve this problem.
-What is different way of deleting the subscription if they are never
expiring in the publication property?
-Actually I try to do all of these above steps in our test
environment, it still give different error message.
-"The merge process could not able to drop the pull subscription form
"sesc-07" (which is server).
-"The remote server is not defined as a subscriber". I change and make
it as subscriber also but still same message.
- "The merge process could not update the last sent generation"
I will be greatful for the information.
Thanks in advance.
Indra.
can any subscriber pull the subscription from the publisher?
What server name did you change? the publisher or the subscriber?
Can you issue this query on the subscriber
select @.@.servername
master.dbo.xp_cmdshell 'hostname'
master.dbo.xp_cmdshell 'echo %computername%'
and post the results back here?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Indra" <itimilsina@.savannaenergy.com> wrote in message
news:e667cf1e.0409010742.1baee732@.posting.google.c om...
> Hi
> we are using merge replication with anonymous subscription.
> - Window 2000 and sql 2000 server (publisher and distributor)
> - MSDE 2000 subscriber.
> - using Active X-Control for subscription
> - I have put subscriber never expire option in the property of
> publisher.
> Replication running without any problem in normal case but we received
> some problem on the following scenario:
> - I set up the subscriber call "TEST01" and synchronize it - it run
> without any problem. Because of some other reasons I need to change
> the name of the computer, after that when I synchronize it does not
> work, even if I change back the computer name to the original name it
> stop working - how can I fix this problem.
> - If I want to delete the subscription total and generate the new one
> how can I do this. I try to delete the subscription from the
> publisher, distributor and delete the subscriber database for the
> subscriber and create new database in subscriber, and click
> synchronize, it still doesn't work- how can I solve this problem.
> - What is different way of deleting the subscription if they are never
> expiring in the publication property?
> - Actually I try to do all of these above steps in our test
> environment, it still give different error message.
> -"The merge process could not able to drop the pull subscription form
> "sesc-07" (which is server).
> -"The remote server is not defined as a subscriber". I change and make
> it as subscriber also but still same message.
> - "The merge process could not update the last sent generation"
> I will be greatful for the information.
> Thanks in advance.
> Indra.
|||Hi Hilary,
All the subscriber runing fine except those whoes computer name
(subscriber computer name) has been change.
we change the name of the subscriber not the publisher.
Our subscriber is runing on MSDE 2000, so how can i run that sql script
on MSDE?.
select @.@.servername
master.dbo.xp_cmdshell 'hostname'
master.dbo.xp_cmdshell 'echo %computername%'
CAN I RUN THIS ON PUBLISHER.
none of the subscriber are register on server becuase i am usign
anonymous subscribtion.
Thanks.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!