|
|
You're in the
sql server replication
group:disable publishing and distribution error
sql server replication:
the tsql use master exec sp_dropdistributor @no_checks = 1 go The error is Msg 208, Level 16, State 1, Procedure sp_MSmergepublish db, Line 103 Invalid object name 'dbo.sysmergesubscriptions'
Unpublish all of your user databases doing this command in each db.
sp_replicationdboptin 'databasename','merge publish',false You will find a database which is missing some tables. Manually create them by doing this create table sysmergesubscriptions(dummy int) and then continue to issue the above option until you clear all error messages. then try to drop the distribution db. -- 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 [quoted text, click to view] "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message news:%23LQowMJ%23GHA.2180@TK2MSFTNGP05.phx.gbl... > The "Disable Publishing and Distribution' wizard fails. It corresponds to > the tsql > > use master > exec sp_dropdistributor @no_checks = 1 > go > > The error is > > Msg 208, Level 16, State 1, Procedure sp_MSmergepublish db, Line 103 > Invalid object name 'dbo.sysmergesubscriptions' >
Ok, that worked, but now I receive the following set of error messages :
Msg 207, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 104 Invalid column name 'subscriber_server' Invalid column name 'db_name' Invalid column name 'sub_id' Invalid column name 'pub_id' [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:O7m6YqK%23GHA.4468@TK2MSFTNGP05.phx.gbl... > Unpublish all of your user databases doing this command in each db. > > sp_replicationdboptin 'databasename','merge publish',false > > You will find a database which is missing some tables. Manually create > them by doing this > > create table sysmergesubscriptions(dummy int) > > and then continue to issue the above option until you clear all error > messages. then try to drop the distribution db. > > -- > 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:%23LQowMJ%23GHA.2180@TK2MSFTNGP05.phx.gbl... >> The "Disable Publishing and Distribution' wizard fails. It corresponds to >> the tsql >> >> use master >> exec sp_dropdistributor @no_checks = 1 >> go >> >> The error is >> >> Msg 208, Level 16, State 1, Procedure sp_MSmergepublish db, Line 103 >> Invalid object name 'dbo.sysmergesubscriptions' >> > >
What is the tsql for creating sysmergepublications ?
[quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:O8kh0ZE$GHA.3480@TK2MSFTNGP04.phx.gbl... > OK, then try this > > create table sysmergesubscriptions( > subscriber_server sysname, > db_name sysname, > pubid uniqueidentifier, > datasource_type int, > subid uniqueidentifier, > replnickname binary(6), > replicastate uniqueidentifier, > status tinyint, > subscriber_type int, > subscription_type int, > sync_type tinyint, > description nvarchar(510), > priority real, > recgen bigint, > recguid uniqueidentifier, > sentgen bigint, > sentguid uniqueidentifier, > schemaversion int, > schemaguid uniqueidentifier, > last_validated datetime, > attempted_validate datetime, > last_sync_date datetime, > last_sync_status int, > last_sync_summary sysname, > metadatacleanuptime datetime, > partition_id int, > cleanedup_unsent_changes int, > replica_version int, > supportability_mode int, > application_name sysname, > subscriber_number int > > ) > > > > -- > 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:%23t1joQE$GHA.924@TK2MSFTNGP03.phx.gbl... >> Ok, that worked, but now I receive the following set of error messages : >> >> Msg 207, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 104 >> Invalid column name 'subscriber_server' >> Invalid column name 'db_name' >> Invalid column name 'sub_id' >> Invalid column name 'pub_id' >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:O7m6YqK%23GHA.4468@TK2MSFTNGP05.phx.gbl... >>> Unpublish all of your user databases doing this command in each db. >>> >>> sp_replicationdboptin 'databasename','merge publish',false >>> >>> You will find a database which is missing some tables. Manually create >>> them by doing this >>> >>> create table sysmergesubscriptions(dummy int) >>> >>> and then continue to issue the above option until you clear all error >>> messages. then try to drop the distribution db. >>> >>> -- >>> 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:%23LQowMJ%23GHA.2180@TK2MSFTNGP05.phx.gbl... >>>> The "Disable Publishing and Distribution' wizard fails. It corresponds >>>> to the tsql >>>> >>>> use master >>>> exec sp_dropdistributor @no_checks = 1 >>>> go >>>> >>>> The error is >>>> >>>> Msg 208, Level 16, State 1, Procedure sp_MSmergepublish db, Line 103 >>>> Invalid object name 'dbo.sysmergesubscriptions' >>>> >>> >>> >> >> > >
OK, then try this
create table sysmergesubscriptions( subscriber_server sysname, db_name sysname, pubid uniqueidentifier, datasource_type int, subid uniqueidentifier, replnickname binary(6), replicastate uniqueidentifier, status tinyint, subscriber_type int, subscription_type int, sync_type tinyint, description nvarchar(510), priority real, recgen bigint, recguid uniqueidentifier, sentgen bigint, sentguid uniqueidentifier, schemaversion int, schemaguid uniqueidentifier, last_validated datetime, attempted_validate datetime, last_sync_date datetime, last_sync_status int, last_sync_summary sysname, metadatacleanuptime datetime, partition_id int, cleanedup_unsent_changes int, replica_version int, supportability_mode int, application_name sysname, subscriber_number int ) -- 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 [quoted text, click to view] "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message news:%23t1joQE$GHA.924@TK2MSFTNGP03.phx.gbl... > Ok, that worked, but now I receive the following set of error messages : > > Msg 207, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 104 > Invalid column name 'subscriber_server' > Invalid column name 'db_name' > Invalid column name 'sub_id' > Invalid column name 'pub_id' > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:O7m6YqK%23GHA.4468@TK2MSFTNGP05.phx.gbl... >> Unpublish all of your user databases doing this command in each db. >> >> sp_replicationdboptin 'databasename','merge publish',false >> >> You will find a database which is missing some tables. Manually create >> them by doing this >> >> create table sysmergesubscriptions(dummy int) >> >> and then continue to issue the above option until you clear all error >> messages. then try to drop the distribution db. >> >> -- >> 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:%23LQowMJ%23GHA.2180@TK2MSFTNGP05.phx.gbl... >>> The "Disable Publishing and Distribution' wizard fails. It corresponds >>> to the tsql >>> >>> use master >>> exec sp_dropdistributor @no_checks = 1 >>> go >>> >>> The error is >>> >>> Msg 208, Level 16, State 1, Procedure sp_MSmergepublish db, Line 103 >>> Invalid object name 'dbo.sysmergesubscriptions' >>> >> >> > >
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 [quoted text, click to view] "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message news:%23exvteE$GHA.4196@TK2MSFTNGP03.phx.gbl... > What is the tsql for creating sysmergepublications ? > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:O8kh0ZE$GHA.3480@TK2MSFTNGP04.phx.gbl... >> OK, then try this >> >> create table sysmergesubscriptions( >> subscriber_server sysname, >> db_name sysname, >> pubid uniqueidentifier, >> datasource_type int, >> subid uniqueidentifier, >> replnickname binary(6), >> replicastate uniqueidentifier, >> status tinyint, >> subscriber_type int, >> subscription_type int, >> sync_type tinyint, >> description nvarchar(510), >> priority real, >> recgen bigint, >> recguid uniqueidentifier, >> sentgen bigint, >> sentguid uniqueidentifier, >> schemaversion int, >> schemaguid uniqueidentifier, >> last_validated datetime, >> attempted_validate datetime, >> last_sync_date datetime, >> last_sync_status int, >> last_sync_summary sysname, >> metadatacleanuptime datetime, >> partition_id int, >> cleanedup_unsent_changes int, >> replica_version int, >> supportability_mode int, >> application_name sysname, >> subscriber_number int >> >> ) >> >> >> >> -- >> 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:%23t1joQE$GHA.924@TK2MSFTNGP03.phx.gbl... >>> Ok, that worked, but now I receive the following set of error messages : >>> >>> Msg 207, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 104 >>> Invalid column name 'subscriber_server' >>> Invalid column name 'db_name' >>> Invalid column name 'sub_id' >>> Invalid column name 'pub_id' >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:O7m6YqK%23GHA.4468@TK2MSFTNGP05.phx.gbl... >>>> Unpublish all of your user databases doing this command in each db. >>>> >>>> sp_replicationdboptin 'databasename','merge publish',false >>>> >>>> You will find a database which is missing some tables. Manually create >>>> them by doing this >>>> >>>> create table sysmergesubscriptions(dummy int) >>>> >>>> and then continue to issue the above option until you clear all error >>>> messages. then try to drop the distribution db. >>>> >>>> -- >>>> 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:%23LQowMJ%23GHA.2180@TK2MSFTNGP05.phx.gbl... >>>>> The "Disable Publishing and Distribution' wizard fails. It corresponds >>>>> to the tsql >>>>> >>>>> use master >>>>> exec sp_dropdistributor @no_checks = 1 >>>>> go >>>>> >>>>> The error is >>>>> >>>>> Msg 208, Level 16, State 1, Procedure sp_MSmergepublish db, Line 103 >>>>> Invalid object name 'dbo.sysmergesubscriptions' >>>>> >>>> >>>> >>> >>> >> >> > >
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'. [quoted text, click to view] "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... >> What is the tsql for creating sysmergepublications ? >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:O8kh0ZE$GHA.3480@TK2MSFTNGP04.phx.gbl... >>> OK, then try this >>> >>> create table sysmergesubscriptions( >>> subscriber_server sysname, >>> db_name sysname, >>> pubid uniqueidentifier, >>> datasource_type int, >>> subid uniqueidentifier, >>> replnickname binary(6), >>> replicastate uniqueidentifier, >>> status tinyint, >>> subscriber_type int, >>> subscription_type int, >>> sync_type tinyint, >>> description nvarchar(510), >>> priority real, >>> recgen bigint, >>> recguid uniqueidentifier, >>> sentgen bigint, >>> sentguid uniqueidentifier, >>> schemaversion int, >>> schemaguid uniqueidentifier, >>> last_validated datetime, >>> attempted_validate datetime, >>> last_sync_date datetime, >>> last_sync_status int, >>> last_sync_summary sysname, >>> metadatacleanuptime datetime, >>> partition_id int, >>> cleanedup_unsent_changes int, >>> replica_version int, >>> supportability_mode int, >>> application_name sysname, >>> subscriber_number int >>> >>> ) >>> >>> >>> >>> -- >>> 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:%23t1joQE$GHA.924@TK2MSFTNGP03.phx.gbl... >>>> Ok, that worked, but now I receive the following set of error messages >>>> : >>>> >>>> Msg 207, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 104 >>>> Invalid column name 'subscriber_server' >>>> Invalid column name 'db_name' >>>> Invalid column name 'sub_id' >>>> Invalid column name 'pub_id' >>>> >>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>> news:O7m6YqK%23GHA.4468@TK2MSFTNGP05.phx.gbl... >>>>> Unpublish all of your user databases doing this command in each db. >>>>> >>>>> sp_replicationdboptin 'databasename','merge publish',false >>>>> >>>>> You will find a database which is missing some tables. Manually create >>>>> them by doing this >>>>> >>>>> create table sysmergesubscriptions(dummy int) >>>>> >>>>> and then continue to issue the above option until you clear all error >>>>> messages. then try to drop the distribution db. >>>>> >>>>> -- >>>>> 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:%23LQowMJ%23GHA.2180@TK2MSFTNGP05.phx.gbl... >>>>>> The "Disable Publishing and Distribution' wizard fails. It >>>>>> corresponds to the tsql >>>>>> >>>>>> use master >>>>>> exec sp_dropdistributor @no_checks = 1 >>>>>> go >>>>>> >>>>>> The error is >>>>>> >>>>>> Msg 208, Level 16, State 1, Procedure sp_MSmergepublish db, Line 103 >>>>>> Invalid object name 'dbo.sysmergesubscriptions' >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
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 ? [quoted text, click to view] "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... >> 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... >>>> What is the tsql for creating sysmergepublications ? >>>> >>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>> news:O8kh0ZE$GHA.3480@TK2MSFTNGP04.phx.gbl... >>>>> OK, then try this >>>>> >>>>> create table sysmergesubscriptions( >>>>> subscriber_server sysname, >>>>> db_name sysname, >>>>> pubid uniqueidentifier, >>>>> datasource_type int, >>>>> subid uniqueidentifier, >>>>> replnickname binary(6), >>>>> replicastate uniqueidentifier, >>>>> status tinyint, >>>>> subscriber_type int, >>>>> subscription_type int, >>>>> sync_type tinyint, >>>>> description nvarchar(510), >>>>> priority real, >>>>> recgen bigint, >>>>> recguid uniqueidentifier, >>>>> sentgen bigint, >>>>> sentguid uniqueidentifier, >>>>> schemaversion int, >>>>> schemaguid uniqueidentifier, >>>>> last_validated datetime, >>>>> attempted_validate datetime, >>>>> last_sync_date datetime, >>>>> last_sync_status int, >>>>> last_sync_summary sysname, >>>>> metadatacleanuptime datetime, >>>>> partition_id int, >>>>> cleanedup_unsent_changes int, >>>>> replica_version int, >>>>> supportability_mode int, >>>>> application_name sysname, >>>>> subscriber_number int >>>>> >>>>> ) >>>>> >>>>> >>>>> >>>>> -- >>>>> 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:%23t1joQE$GHA.924@TK2MSFTNGP03.phx.gbl... >>>>>> Ok, that worked, but now I receive the following set of error >>>>>> messages : >>>>>> >>>>>> Msg 207, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 104 >>>>>> Invalid column name 'subscriber_server' >>>>>> Invalid column name 'db_name' >>>>>> Invalid column name 'sub_id' >>>>>> Invalid column name 'pub_id' >>>>>> >>>>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>>>> news:O7m6YqK%23GHA.4468@TK2MSFTNGP05.phx.gbl... >>>>>>> Unpublish all of your user databases doing this command in each db. >>>>>>> >>>>>>> sp_replicationdboptin 'databasename','merge publish',false >>>>>>> >>>>>>> You will find a database which is missing some tables. Manually >>>>>>> create them by doing this >>>>>>> >>>>>>> create table sysmergesubscriptions(dummy int) >>>>>>> >>>>>>> and then continue to issue the above option until you clear all
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 [quoted text, click to view] "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... >> 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... >>> What is the tsql for creating sysmergepublications ? >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:O8kh0ZE$GHA.3480@TK2MSFTNGP04.phx.gbl... >>>> OK, then try this >>>> >>>> create table sysmergesubscriptions( >>>> subscriber_server sysname, >>>> db_name sysname, >>>> pubid uniqueidentifier, >>>> datasource_type int, >>>> subid uniqueidentifier, >>>> replnickname binary(6), >>>> replicastate uniqueidentifier, >>>> status tinyint, >>>> subscriber_type int, >>>> subscription_type int, >>>> sync_type tinyint, >>>> description nvarchar(510), >>>> priority real, >>>> recgen bigint, >>>> recguid uniqueidentifier, >>>> sentgen bigint, >>>> sentguid uniqueidentifier, >>>> schemaversion int, >>>> schemaguid uniqueidentifier, >>>> last_validated datetime, >>>> attempted_validate datetime, >>>> last_sync_date datetime, >>>> last_sync_status int, >>>> last_sync_summary sysname, >>>> metadatacleanuptime datetime, >>>> partition_id int, >>>> cleanedup_unsent_changes int, >>>> replica_version int, >>>> supportability_mode int, >>>> application_name sysname, >>>> subscriber_number int >>>> >>>> ) >>>> >>>> >>>> >>>> -- >>>> 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:%23t1joQE$GHA.924@TK2MSFTNGP03.phx.gbl... >>>>> Ok, that worked, but now I receive the following set of error messages >>>>> : >>>>> >>>>> Msg 207, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 104 >>>>> Invalid column name 'subscriber_server' >>>>> Invalid column name 'db_name' >>>>> Invalid column name 'sub_id' >>>>> Invalid column name 'pub_id' >>>>> >>>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>>> news:O7m6YqK%23GHA.4468@TK2MSFTNGP05.phx.gbl... >>>>>> Unpublish all of your user databases doing this command in each db. >>>>>> >>>>>> sp_replicationdboptin 'databasename','merge publish',false >>>>>> >>>>>> You will find a database which is missing some tables. Manually >>>>>> create them by doing this >>>>>> >>>>>> create table sysmergesubscriptions(dummy int) >>>>>> >>>>>> and then continue to issue the above option until you clear all error >>>>>> messages. then try to drop the distribution db. >>>>>> >>>>>> -- >>>>>> 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:%23LQowMJ%23GHA.2180@TK2MSFTNGP05.phx.gbl... >>>>>>> The "Disable Publishing and Distribution' wizard fails. It >>>>>>> corresponds to the tsql >>>>>>> >>>>>>> use master
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 [quoted text, click to view] "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... >> 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... >>>> 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... >>>>> What is the tsql for creating sysmergepublications ? >>>>> >>>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>>> news:O8kh0ZE$GHA.3480@TK2MSFTNGP04.phx.gbl... >>>>>> OK, then try this >>>>>> >>>>>> create table sysmergesubscriptions( >>>>>> subscriber_server sysname, >>>>>> db_name sysname, >>>>>> pubid uniqueidentifier, >>>>>> datasource_type int, >>>>>> subid uniqueidentifier, >>>>>> replnickname binary(6), >>>>>> replicastate uniqueidentifier, >>>>>> status tinyint, >>>>>> subscriber_type int, >>>>>> subscription_type int, >>>>>> sync_type tinyint, >>>>>> description nvarchar(510), >>>>>> priority real, >>>>>> recgen bigint, >>>>>> recguid uniqueidentifier, >>>>>> sentgen bigint, >>>>>> sentguid uniqueidentifier, >>>>>> schemaversion int, >>>>>> schemaguid uniqueidentifier, >>>>>> last_validated datetime, >>>>>> attempted_validate datetime, >>>>>> last_sync_date datetime, >>>>>> last_sync_status int, >>>>>> last_sync_summary sysname, >>>>>> metadatacleanuptime datetime, >>>>>> partition_id int, >>>>>> cleanedup_unsent_changes int, >>>>>> replica_version int, >>>>>> supportability_mode int, >>>>>> application_name sysname, >>>>>> subscriber_number int >>>>>> >>>>>> ) >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> 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?
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |