MS SQL Server Replication - Drop merge filter (pull subscription)

Author

Drop merge filter (pull subscription)

Benno

2005-11-25, 11:23 am

Hi everybody,

I'm trying to remove a rowfilter (and 'JOINS') from one of my publication.
When I first tried this on a testdatabase everything seemed to work. But it's
not working on the productiondatabase. I always receive this message:

Cannot drop filter 'Table1_Table2' from publication 'NamePublication'
because its snapshot has been run and this publication could have active
subscriptions.

I don't know how to solve this. Any help would be appreciated!

Thanks!

Paul Ibison

2005-11-25, 1:23 pm

Benno,
try scripting it out, delete the publication then run the script, be sure to
disable the snapshot agent, then remove the filter.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Benno

2005-11-26, 9:23 am

Hi Paul,

thanks for your reply bu I'm afraid I don't understand all of it: do you
think I should delete the publication (without deleting the subscriptions?)
and than recreate it (using the script I created first)? And how would you
delete the publication: EM or stored procedure?

Thanks!

"Paul Ibison" wrote:

> Benno,
> try scripting it out, delete the publication then run the script, be sure to
> disable the snapshot agent, then remove the filter.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Paul Ibison

2005-11-26, 9:23 am

Benno - that's right. If you script out the publication and subscriptions,
you can then delete the subscriptions and the publication. Aftert that you
use the script to create the publication. Delete the filters you don't want
then add the subscriptions and initialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Benno

2005-11-27, 9:23 am

Hi Paul,

can I create and drop the PULL subscriptions "remotely". I don't want to
visit all subscribers to drop the subscription and recreate it afterwards...

Thanks!

"Paul Ibison" wrote:

> Benno - that's right. If you script out the publication and subscriptions,
> you can then delete the subscriptions and the publication. Aftert that you
> use the script to create the publication. Delete the filters you don't want
> then add the subscriptions and initialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>
>

Paul Ibison

2005-11-27, 1:23 pm

Benno,
in the case of merge replication, the script to be run at Subscriber
includes:
exec sp_addmergepullsubsc
ription
exec sp_addmergepullsubsc
ription_agent
If you can connect to and register in EM all the subscribers and run this,
then it can all be set up from one location.
The corresponding script to set up all these subscriptions is also
obtainable for EM: Right Clich the publications foldes and select to
Generate SQL Script...
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Sponsored Links