disable publishing and distribution error

Ok, which database are you creating sysmergepublications and
sysmergesubscriptions in ?
I've created sysmergesubscriptions in distribution , master , msdb ...
I still receive an error when I run
use master
exec sp_dropdistributor @.no_checks = 1
go
I receive :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
> create table sysmergepublications
> (
> publisher sysname,
> publisher_db sysname,
> name sysname,
> description nvarchar(510),
> retention int,
> publication_type tinyint,
> pubid uniqueidentifier,
> designmasterid uniqueidentifier,
> parentid uniqueidentifier,
> sync_mode tinyint,
> allow_push int,
> allow_pull int,
> allow_anonymous int,
> centralized_conflicts int,
> status tinyint,
> snapshot_ready tinyint,
> enabled_for_internet bit,
> dynamic_filters bit,
> snapshot_in_defaultfolder bit,
> alt_snapshot_folder nvarchar(510),
> pre_snapshot_script nvarchar(510),
> post_snapshot_script nvarchar(510),
> compress_snapshot bit,
> ftp_address sysname,
> ftp_port int,
> ftp_subdirectory nvarchar(510),
> ftp_login sysname,
> ftp_password nvarchar(1048),
> conflict_retention int,
> keep_before_values int,
> allow_subscription_copy bit,
> allow_synctoalternate bit,
> validate_subscriber_info nvarchar(1000),
> ad_guidname sysname,
> backward_comp_level int,
> max_concurrent_merge int,
> max_concurrent_dynamic_snapshots int,
> use_partition_groups smallint,
> dynamic_filters_function_list nvarchar(1000),
> partition_id_eval_proc sysname,
> publication_number smallint,
> replicate_ddl int,
> allow_subscriber_initiated_snapshot bit,
> distributor sysname,
> snapshot_jobid binary(16),
> allow_web_synchronization bit,
> web_synchronization_url nvarchar(1000),
> allow_partition_realignment bit,
> retention_period_unit tinyint,
> decentralized_conflicts int,
> generation_leveling_threshold int,
> automatic_reinitialization_policy bit
> )
> --
> 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
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:%23exvteE$GHA.4196@.TK2MSFTNGP03.phx.gbl...
>
Publication database. Check which databases are published for merge
replication and put it there.
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
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
> Ok, which database are you creating sysmergepublications and
> sysmergesubscriptions in ?
> I've created sysmergesubscriptions in distribution , master , msdb ...
> I still receive an error when I run
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> I receive :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
>
|||My database is no longer being published for replication because I used
exec sp_replicationdboption 'databasename','merge publish',false
Nonetheless, I added a dbo.sysmergesubscriptions table to this database and
ran
use master
exec sp_dropdistributor @.no_checks = 1
go
but I still receive the error :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
Could it be because I need to add dbo.sysmergesubscriptions as a system
object ? How do I do this ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
> Publication database. Check which databases are published for merge
> replication and put it there.
> --
> 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
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
>
|||these are the lines around 103
if not exists (select * from dbo.sysmergesubscriptions
where UPPER(subscriber_server) =
UPPER(publishingservername()) and db_name = db_name() and subid <> pubid)
begin
select @.ignore_merge_metadata = 1
end
It is complaining about the database you are running the command in. Is the
table there? Does it have the owner dbo?
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
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ePkGbNQBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> My database is no longer being published for replication because I used
> exec sp_replicationdboption 'databasename','merge publish',false
> Nonetheless, I added a dbo.sysmergesubscriptions table to this database
> and ran
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> but I still receive the error :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
> Could it be because I need to add dbo.sysmergesubscriptions as a system
> object ? How do I do this ?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
>

Disable publishing

We are using SQL 2K with sp4 and use push subcription. The publisher
database and the distribution database are on the same SQL server.
I need to disable publishing and distribution so I can recreate the
publication from scratch. I use SQL EM's Disable Publishing and Distribution
wizard and after I 'finish' the wizard, it proceed to do its thing and gives
me an error that 'distribution database' is in use. I click ok and the
wizard finished its stuff. I checked the publication and it is droped along
with subscrtiptions. Here are my questions.
1. To avoid the 'in use' error, am I supposed to do something like stop
some jobs or agents before I do that?
2, If the wizard didn't encounter any error, what is supposed to happen to
the distribution database?
Wingman
The distribution database will get removed when it works ok. When I have
seen this error, the publications are removed, and disabling publication for
the second time will remove the distribution database correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the info.
I ran the second time and it removed the distribution database as you
described. It clean out a lot of stuff.
I check the replication folder in EM and find a subscription in the
Subscriptions folder. Is it ok to leave it there before I redo the
publishing from scratch? Why do they want to leave a subscription there?
Wingman
"Paul Ibison" wrote:

> The distribution database will get removed when it works ok. When I have
> seen this error, the publications are removed, and disabling publication for
> the second time will remove the distribution database correctly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Wingman,
You can have a look at sp_MSenumsubscriptions to see what is happening when
you click on the subscriptions folder. The key tables in each subscribing
databases are sysmergesubscriptions and MSreplication_subscriptions. If this
database is no longer a subscriber, then run sp_removedbreplication, which
removes the redundant system tables. If you want these tables to remain,
then simply locate the redundant record in the above system table and delete
it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)