Prev: how to tutorial for Replication
Next: SQL2005 - Snapshot agent won't start
From: Hilary Cotter on 12 Jan 2006 10:01
This looks like its for SQL 2000 and not for SQL 2005. Do you have any
backups you can restore?

--
Hilary Cotter
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

"Heather B." <hbaker(a)pro-merchnospam.com> wrote in message
news:3758aadfdace47689a27696ac35b5ffd(a)ureader.com...
>I found this in a forum somewhere, when I ran it all the tables seemed to
>be
> there, but when I tried to create a new publication I still go an error.
> This time it would say something about a field missing, I don't remember
> which field but I do remember that I looked in the table and it was there.
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
>
> ALTER procedure [dbo].[sp_MScreate_mergesystables] as
>
> /* This is to make sure that the varbinary columns do not get padded */
>
> set ANSI_PADDING off
>
> DECLARE @exist bit
> DECLARE @validsubs int
> select @exist = 1
>
> begin tran
> save transaction MScreate_mergesystables
>
> exec dbo.sp_MScheckvalidsystables @validsubs output
> if @validsubs = 0
> exec dbo.sp_MSdrop_mergesystables
>
> if not exists (select * from sysobjects where name =
> 'sysmergepublications')
>
> begin
>
> raiserror('Creating table sysmergepublications',0,1)
>
> create table dbo.sysmergepublications
> ( publisher sysname NOT NULL default @@servername,
> publisher_db sysname NOT NULL default db_name(),
> name sysname NOT NULL,
> description nvarchar(255) NULL,
> retention int NULL,
> publication_type tinyint NULL,
> pubid uniqueidentifier NOT NULL,
> designmasterid uniqueidentifier NULL,
> parentid uniqueidentifier NULL,
> sync_mode tinyint NULL,
> allow_push int NULL,
> allow_pull int NULL,
> allow_anonymous int NULL,
> centralized_conflicts int NULL,
> status tinyint NULL,
> snapshot_ready tinyint NULL,
> enabled_for_internet bit NOT NULL default 0,
> dynamic_filters bit NOT NULL default 0,
> -- portable snapshot support
> snapshot_in_defaultfolder bit NOT NULL
> default
> 1,
> alt_snapshot_folder nvarchar(255) NULL,
> -- Pre/post - snapshot commands
> pre_snapshot_script nvarchar(255) NULL,
> post_snapshot_script nvarchar(255) NULL,
> -- Snapshot compression
> compress_snapshot bit NOT NULL
> default
> 0,
> -- Post 7.0 Ftp support
> ftp_address sysname NULL,
> ftp_port int NOT NULL
> default
> 21,
> ftp_subdirectory nvarchar(255) NULL,
> ftp_login sysname NULL default
> N'anonymous',
> ftp_password nvarchar(524) NULL,
> conflict_retention int NULL,
> keep_before_values int NULL default 0,
> allow_subscription_copy bit NULL default 0,
> allow_synctoalternate bit NULL default 0,
> validate_subscriber_info nvarchar(500) NULL,
> ad_guidname sysname NULL,
> backward_comp_level int not NULL default 10, --7.0 RTM
> max_concurrent_merge int not NULL default 0,
> max_concurrent_dynamic_snapshots int not NULL
> default
> 0
> )
> if @@ERROR <> 0
> goto Error
>
> CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications
> ON sysmergepublications(pubid)
> if @@ERROR <> 0
> goto Error
>
> exec dbo.sp_MS_marksystemobject sysmergepublications
> if @@ERROR <> 0
> goto Error
> -- grant select on sysmergepublications to public
> end
>
> if not exists (select * from sysobjects where name =
> 'MSmerge_errorlineage')
>
> begin
> raiserror('Creating table MSmerge_errorlineage',0,1)
>
> create table dbo.MSmerge_errorlineage (
> tablenick int NOT NULL,
> rowguid uniqueidentifier NOT NULL,
> lineage varbinary(255)
> )
> exec dbo.sp_MS_marksystemobject MSmerge_errorlineage
> if @@ERROR <> 0
> goto Error
> create unique clustered index uc1errorlineage on
> MSmerge_errorlineage(tablenick, rowguid)
> if @@ERROR <> 0
> goto Error
>
> --grant select on MSmerge_errorlineage to public
> end
>
> -- this table exists at distribution/db and subscriber databse both
> if not exists (select * from sysobjects where name =
> 'MSrepl_identity_range')
> begin
> raiserror('Creating table MSrepl_identity_range',0,1)
>
> create table dbo.MSrepl_identity_range (
> objid int not NULL primary key,
> next_seed bigint NULL, --resource control
> pub_range bigint NULL, --publisher range
> range bigint NULL, -- set by sp_addmergearticle
> max_identity bigint NULL, --resource control
> threshold int NULL, --in percentage, set by sp_addmergearticle
> current_max bigint NULL --max value for current check constraint,set
> by sp_addmergearticle
> )
> exec dbo.sp_MS_marksystemobject MSrepl_identity_range
> if @@ERROR <> 0
> goto Error
>
> --grant select on MSrepl_identity_range to public
> end
>
> if not exists (select * from sysobjects where name = 'sysmergearticles')
> begin
>
> raiserror('Creating table sysmergearticles',0,1)
>
>
> create table dbo.sysmergearticles (
> name sysname NOT NULL,
> type tinyint NULL,
> objid int NOT NULL,
> sync_objid int NOT NULL,
> view_type tinyint NULL,
> artid uniqueidentifier NOT NULL,
> description nvarchar(255) NULL,
> pre_creation_command tinyint NULL,
> pubid uniqueidentifier NOT NULL,
> nickname int NOT NULL,
> column_tracking int NOT NULL,
> status tinyint NULL,
> conflict_table sysname NULL,
> creation_script nvarchar(255) NULL,
> conflict_script nvarchar(255) NULL,
> article_resolver nvarchar(255) NULL,
> ins_conflict_proc sysname NULL,
> insert_proc sysname NULL,
> update_proc sysname NULL,
> select_proc sysname NULL,
> schema_option binary(8) NULL,
> destination_object sysname NOT NULL,
> destination_owner sysname NULL,
> resolver_clsid nvarchar(50) NULL,
> subset_filterclause nvarchar(1000) NULL,
> missing_col_count int NULL,
> missing_cols varbinary(128) NULL,
> excluded_cols varbinary(128) NULL,
> excluded_col_count int not NULL default 0,
> columns varbinary(128) NULL,
> resolver_info nvarchar(255) NULL,
> view_sel_proc nvarchar(290) NULL,
> gen_cur int NULL,
> vertical_partition int not NULL default 0,
> identity_support int not NULL default 0,
> before_image_objid int NULL,
> before_view_objid int NULL,
> verify_resolver_signature int NULL,
> allow_interactive_resolver bit NOT NULL default 0,
> fast_multicol_updateproc bit NOT NULL default 0,
> check_permissions int NOT NULL default 0,
> maxversion_at_cleanup int NOT NULL default 1,
> published_in_tran_pub bit NOT NULL default 0
>
> -- Note: Please update sysmergeextendedarticlesview
> whenever
> -- there is a schema change in sysmergearticles
> )
>
> if @@error<>0
> goto Error
> else
> begin
>
> create unique clustered index uc1sysmergearticles
> on sysmergearticles(artid, pubid)
> if @@ERROR <> 0
> goto Error
>
> if not exists (select * from sysindexes where name =
> 'nc1sysmergearticles')
> begin
> create nonclustered index nc1sysmergearticles on
> sysmergearticles(nickname)
> if @@ERROR <> 0
> return 1
> end
> end
> exec dbo.sp_MS_marksystemobject sysmergearticles
> if @@ERROR <> 0
> goto Error
> grant select(nickname,gen_cur,maxversion_at_cleanup) on sysmergearticles
> to public
>
> end
>
> if not exists (select * from sysobjects where name =
> 'sysmergeschemaarticles')
> begin
>
> -- The extended merge articles table is for storing
> -- schema only article information.
> -- The current supported schema only articles are
> -- stored procs and views.
> raiserror('Creating table sysmergeschemaarticles',0,1)
>
> create table dbo.sysmergeschemaarticles
> ( name sysname NOT NULL,
> type tinyint NULL,
> objid int NOT NULL,
> artid uniqueidentifier NOT NULL,
> description nvarchar(255) NULL,
> pre_creation_command tinyint NULL,
> pubid uniqueidentifier NOT NULL,
> status tinyint NULL,
> creation_script nvarchar(255) NULL,
> schema_option binary(8) NULL,
> destination_object sysname NOT NULL,
> destination_owner sysname NULL
> -- Note: Please update sysmergeextendedarticlesview
> whenever
> -- there is a schema change in sysmergeschemaarticles
> )
>
> if @@error<>0
> goto Error
> else
> begin
> create unique clustered index uc1sysmergeschemaarticles
> on sysmergeschemaarticles(artid, pubid)
> if @@ERROR <> 0
> goto Error
> end
>
> exec dbo.sp_MS_marksystemobject sysmergeschemaarticles
> if @@ERROR <> 0
> goto Error
>
> end
>
> if exists (select * from sysobjects where name =
> 'sysmergeextendedarticlesview')
> begin
> drop view dbo.sysmergeextendedarticlesview
> end
> exec ('create view dbo.sysmergeextendedarticlesview
> as
> select name, type, objid, sync_objid, view_type, artid,
> description, pre_creation_command, pubid,
> nickname, column_tracking, status, conflict_table, creation_script,
> conflict_script, article_resolver,
> ins_conflict_proc, insert_proc, update_proc, select_proc,
> schema_option, destination_object,
> resolver_clsid, subset_filterclause, missing_col_count, missing_cols,
> columns, resolver_info,
> view_sel_proc, gen_cur, excluded_cols, excluded_col_count,
> vertical_partition, identity_support,
> destination_owner, before_image_objid, before_view_objid,
> verify_resolver_signature,
> allow_interactive_resolver, fast_multicol_updateproc,
> check_permissions, maxversion_at_cleanup,
> published_in_tran_pub
> from sysmergearticles
> union all
> select name, type, objid, NULL, NULL, artid, description,
> pre_creation_command, pubid,
> NULL, NULL, status, NULL, creation_script, NULL, NULL,
> NULL, NULL, NULL, NULL, schema_option, destination_object,
> NULL, NULL, NULL, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL,
> destination_owner, NULL, NULL, NULL,
> 0, 0, 0, NULL, 0
> from sysmergeschemaarticles
> go')
> if @@error <> 0
> goto Error
>
> exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview
>
> if not exists (select * from sysobjects where name =
> 'sysmergesubscriptions')
> begin
>
> raiserror('Creating table sysmergesubscriptions',0,1)
>
>
> create table dbo.sysmergesubscriptions
> (
> subid uniqueidentifier NOT NULL,
> partnerid uniqueidentifier NOT NULL,
> datasource_type int NOT NULL,
> datasource_path nvarchar(255) NULL,
> srvid int NOT NULL,
> db_name sysname NOT NULL
> constraint unique_pubsrvdb unique nonclustered (pubid, srvid,
> db_name),
> pubid uniqueidentifier NULL,
> status tinyint NOT NULL,
> subscriber_type int NOT NULL,
> subscription_type int NOT NULL,
> priority real NOT NULL,
> sync_type tinyint NOT NULL, -- 1 = automatic 2 = no sync
> description nvarchar(255) NULL,
> login_name sysname NOT NULL,
> last_validated datetime NULL,
> subscriber_server sysname NULL,
> use_interactive_resolver bit NOT NULL default 0,
> publication sysname NULL,
> distributor sysname NULL,
> validation_level int not NULL default 0,
> resync_gen int not NULL default -1,
> attempted_validate datetime NULL,
> last_sync_date datetime NULL,
> last_sync_status int NULL,
> last_sync_summary sysname NULL
> )
>
> if @@error<>0
> goto Error
> else
> begin
> create unique clustered index uc1sysmergesubscriptions
> on sysmergesubscriptions (subid)
> if @@ERROR<>0
> goto Error
>
> create index nc2sysmergesubscriptions on sysmergesubscriptions (srvid,
> db_name)
> if @@ERROR<>0
> goto Error
> end
> exec dbo.sp_MS_marksystemobject sysmergesubscriptions
> if @@ERROR <> 0
> goto Error
> --grant select on sysmergesubscriptions to public
>
> end
>
> if not exists (select * from sysobjects where name = 'MSmerge_replinfo')
> begin
> raiserror('Creating table MSmerge_replinfo',0,1)
>
> create table dbo.MSmerge_replinfo
> (
> repid uniqueidentifier NOT NULL,
> replnickname int NOT NULL,
> recgen int NULL,
> recguid uniqueidentifier NULL,
> sentgen int NULL,
> sentguid uniqueidentifier NULL,
> schemaversion int NULL,
> schemaguid uniqueidentifier NULL,
> merge_jobid binary(16) NULL,
> snapshot_jobid binary(16) NULL
> )
>
> if @@ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index uc1MSmerge_replinfo
> on MSmerge_replinfo (repid)
> if @@ERROR <> 0
> goto Error
> end
> exec dbo.sp_MS_marksystemobject MSmerge_replinfo
> if @@ERROR <> 0
> goto Error
> --grant select on MSmerge_replinfo to public
>
> end
>
> if not exists (select * from sysobjects where name = 'MSmerge_tombstone')
> begin
> raiserror('Creating table MSmerge_tombstone',0,1)
>
> create table dbo.MSmerge_tombstone
> (
> rowguid uniqueidentifier rowguidcol NOT NULL,
> tablenick int NOT NULL,
> type tinyint NOT NULL,
> lineage varbinary(249) NOT NULL,
> generation int NOT NULL,
> reason nvarchar(255) NOT NULL,
> )
>
> if @@ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index uc1MSmerge_tombstone
> on MSmerge_tombstone (tablenick DESC, rowguid)
> if @@ERROR <> 0 goto Error
>
> create index nc2MSmerge_tombstone
> on MSmerge_tombstone (generation)
> if @@ERROR <> 0 goto Error
> end
>
> exec dbo.sp_MS_marksystemobject MSmerge_tombstone
> if @@ERROR <> 0
> goto Error
> --grant select on MSmerge_tombstone to public
>
> end
>
> if not exists (select * from sysobjects where name = 'MSmerge_contents')
> begin
> raiserror('Creating table MSmerge_contents',0,1)
>
> create table dbo.MSmerge_contents
> (
> tablenick int NOT NULL,
> rowguid uniqueidentifier rowguidcol NOT NULL,
> generation int NOT NULL,
> partchangegen int NULL,
> joinchangegen int NULL,
> lineage varbinary(249) NOT NULL,
> colv1 varbinary(2048) NULL,
> )
>
> if @@ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index uc1SycContents on
> MSmerge_contents(tablenick, rowguid)
> if @@ERROR <> 0 goto Error
> create index nc2MSmerge_contents on MSmerge_contents(generation)
> if @@ERROR <> 0 goto Error
> create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
> if @@ERROR <> 0 goto Error
> create index nc4MSmerge_contents on MSmerge_contents(rowguid)
> if @@ERROR <> 0 goto Error
> end
> exec dbo.sp_MS_marksystemobject MSmerge_contents
> if @@ERROR <> 0
> goto Error
> --grant select on MSmerge_contents to public
>
> end
>
> if not exists (select * from sysobjects where name = 'MSmerge_genhistory')
> begin
> raiserror('Creating table MSmerge_genhistory',0,1)
>
> create table dbo.MSmerge_genhistory
> (
> guidsrc uniqueidentifier NOT NULL,
> guidlocal uniqueidentifier NOT NULL,
> pubid uniqueidentifier NULL,
> generation int NOT NULL,
> art_nick int NULL,
> nicknames varbinary(1000) NOT NULL,
> coldate datetime NOT NULL
> )
>
> if @@ERROR <> 0
> goto Error
>
> create clustered index c1MSmerge_genhistory on
> MSmerge_genhistory(generation)
> if @@ERROR <> 0
> goto Error
>
> create unique index unc1MSmerge_genhistory on MSmerge_genhistory(guidsrc,
> pubid)
> if @@ERROR <> 0
> goto Error
>
> create index nc2MSmerge_genhistory on MSmerge_genhistory(guidlocal)
> if @@ERROR <> 0
> goto Error
>
> CREATE INDEX nc4MSmerge_genhistory ON MSmerge_genhistory(coldate)
> if @@ERROR <> 0
> goto Error
>
> exec dbo.sp_MS_marksystemobject MSmerge_genhistory
> if @@ERROR <> 0
> goto Error
> --grant select on MSmerge_genhistory to public
>
> end
>
> if not exists (select * from sysobjects where name =
> 'MSmerge_delete_conflicts')
> begin
>
> raiserror('Creating table MSmerge_delete_conflicts',0,1)
>
> create table dbo.MSmerge_delete_conflicts
> (
> tablenick int NOT NULL,
> rowguid uniqueidentifier rowguidcol NOT NULL,
> origin_datasource nvarchar(255) NULL,
> conflict_type int NULL,
> reason_code int NULL,
> reason_text nvarchar(720) NULL,
> pubid uniqueidentifier NULL,
> create_time datetime not null default getdate()
> )
>
> if @@ERROR <> 0
> goto Error
> else
> begin
> create clustered index uc1MSmerge_delete_conflicts on
> MSmerge_delete_conflicts(tablenick, rowguid)
> if @@ERROR <> 0
> goto Error
> end
>
> CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_delete_conflicts
> ON MSmerge_delete_conflicts(tablenick, rowguid, origin_datasource)
> if @@ERROR <> 0
> goto Error
>
> exec dbo.sp_MS_marksystemobject MSmerge_delete_conflicts
> if @@ERROR <> 0
> goto Error
> --grant select on MSmerge_delete_conflicts to public
>
> end
>
> if not exists (select * from sysobjects where name =
> 'sysmergeschemachange')
>
> begin
>
> raiserror('Creating table sysmergeschemachange',0,1)
>
> create table dbo.sysmergeschemachange
> (
> pubid uniqueidentifier NOT NULL,
> artid uniqueidentifier NULL,
> schemaversion int NOT NULL,
> schemaguid uniqueidentifier NOT NULL,
> schematype int NOT NULL,
> schematext nvarchar(2000) NOT NULL
> )
>
> if @@ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index schemachangeversion on
> sysmergeschemachange(schemaversion, pubid)
> if @@ERROR <> 0
> goto Error
> end
> exec dbo.sp_MS_marksystemobject sysmergeschemachange
> if @@ERROR <> 0
> goto Error
> --grant select on sysmergeschemachange to public
>
> end
>
> if not exists (select * from sysobjects where name =
> 'sysmergesubsetfilters')
> begin
> raiserror('Creating table sysmergesubsetfilters',0,1)
>
>
> create table dbo.sysmergesubsetfilters (
> filtername sysname NOT NULL,
> join_filterid int identity NOT NULL,
> pubid uniqueidentifier NOT NULL,
> artid uniqueidentifier NOT NULL,
> art_nickname int NOT NULL,
> join_articlename sysname NOT NULL,
> join_nickname int NOT NULL,
> join_unique_key int NOT NULL,
> expand_proc sysname NULL,
> join_filterclause nvarchar(1000) NULL
> )
> if @@ERROR <> 0
> goto Error
>
> CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters
> ON sysmergesubsetfilters(join_filterid, pubid)
> if @@ERROR <> 0
> goto Error
>
> exec dbo.sp_MS_marksystemobject sysmergesubsetfilters
> if @@ERROR <> 0
> goto Error
> --grant select on sysmergesubsetfilters to public
>
> end
>
> if @@error <> 0
> goto Error
>
> if not exists (select * from sysobjects where name =
> 'MSdynamicsnapshotviews')
> begin
> raiserror('Creating table MSdynamicsnapshotviews',0,1)
>
> create table dbo.MSdynamicsnapshotviews (
> dynamic_snapshot_view_name sysname primary key
> )
> if @@ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews
> end
>
> if not exists (select * from sysobjects where name =
> 'MSdynamicsnapshotjobs')
> begin
> raiserror('Creating table MSdynamicsnapshotjobs',0,1)
>
> create table dbo.MSdynamicsnapshotjobs (
> id int identity,
> name sysname not null unique,
> pubid uniqueidentifier not null,
> job_id uniqueidentifier not null,
> dynamic_filter_login sysname null,
> dynamic_filter_hostname sysname null,
> dynamic_snapshot_location nvarchar(255) not null
> )
> if @@ERROR <> 0
> goto Error
>
> exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs
>
> create unique clustered index uciMSdynamicsnapshotjobs on
> dbo.MSdynamicsnapshotjobs(job_id, pubid)
>
> if @@ERROR <> 0
> goto Error
>
>
> end
>
> if @@error <> 0
> goto Error
>
> if not exists (select * from sysobjects where name =
> 'MSmerge_altsyncpartners')
> begin
> raiserror('Creating table MSmerge_altsyncpartners',0,1)
>
> create table dbo.MSmerge_altsyncpartners (
> subid uniqueidentifier not null,
> alternate_subid uniqueidentifier not null,
> description nvarchar(255) NULL
>
> )
> if @@ERROR <> 0
> goto Error
>
> exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners
>
> create unique clustered index uciMSmerge_altsyncpartners on
> dbo.MSmerge_altsyncpartners(subid, alternate_subid)
>
> if @@ERROR <> 0
> goto Error
>
>
> end
>
>
> if exists (select * from master..sysobjects where type='P' and
> name='sp_MScreatedebuginfrastructure')
> begin
> declare @retval int
> exec @retval= dbo.sp_MScreatedebuginfrastructure
> if @@ERROR <> 0 or @retval <> 0 goto Error
> end
>
> commit transaction
> return (0)
> Error:
> if @@trancount > 0
> begin
> ROLLBACK TRANSACTION MScreate_mergesystables
> COMMIT TRANSACTION
> end
> RAISERROR (20008, 16, -1)
> return (1)


From: Heather on 12 Jan 2006 11:15
No, this was just a test/develope server so I can set it back up by
reinstalling SQL. But this is the second time it's happened, I can
duplicate it easily, I would just like to find out what's causing it so that
it doesn't happen on our production server.
From: Michael Hotek on 13 Jan 2006 15:18
Then definitely post something to reproduce it. I've hit this same thing
several times in the past and hav never been able to reliably reproduce it.
I can get it to reoccur, but not reproduce when I want it to. It is related
to doing many cycles of putting replication in and taking it back out. I've
always fixed this by going in and explicitly recreating the table(s). BOL
has all of the column definitions listed.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

"Heather B." <hbaker(a)pro-merchnospam.com> wrote in message
news:0a44768a00d845729724b964d5f84646(a)ureader.com...
> No, this was just a test/develope server so I can set it back up by
> reinstalling SQL. But this is the second time it's happened, I can
> duplicate it easily, I would just like to find out what's causing it so
> that
> it doesn't happen on our production server.


First  |  Prev  | 
Pages: 1 2
Prev: how to tutorial for Replication
Next: SQL2005 - Snapshot agent won't start