create procedure sys.sp_MSmerge_create_sub_table as
if object_id('dbo.sysmergesubscriptions', 'U') is null
begin
--raiserror('Creating table sysmergesubscriptions',0,1)
create table dbo.sysmergesubscriptions
(
subscriber_server sysname NULL,
db_name sysname NOT NULL,
pubid uniqueidentifier NULL,
constraint unique_pubsrvdb unique nonclustered (pubid, subscriber_server, db_name),
datasource_type int NOT NULL default 0,
subid uniqueidentifier NOT NULL,
replnickname binary(6) NOT NULL,
replicastate uniqueidentifier NOT NULL,
status tinyint NOT NULL,
subscriber_type int NOT NULL,
subscription_type int NOT NULL,
sync_type tinyint NOT NULL, -- 1 = automatic 2 = no sync
description nvarchar(255) NULL,
priority real NULL, -- only valid when subscriber_type is global
recgen bigint NULL,
recguid uniqueidentifier NULL,
sentgen bigint NULL,
sentguid uniqueidentifier NULL,
schemaversion int NULL,
schemaguid uniqueidentifier NULL,
last_validated datetime NULL,
attempted_validate datetime NULL,
last_sync_date datetime NULL,
last_sync_status int NULL,
last_sync_summary sysname NULL,
metadatacleanuptime datetime not NULL default getdate(),
partition_id int NULL,
cleanedup_unsent_changes bit NOT NULL default 0,
replica_version int NOT NULL default 60, -- 60 = shiloh sp3 and below, 90=Yukon
supportability_mode int NOT NULL default 0, -- central control of log files. Off by default.
application_name sysname NULL,
subscriber_number int identity not NULL,
last_makegeneration_datetime datetime NULL
-- whenever adding a column here also add it to sp_MScreatenewreplnick
)
if @@error<>0
goto Error
else
begin
create unique clustered index uc1sysmergesubscriptions on dbo.sysmergesubscriptions (subid)
if @@ERROR<>0
goto Error
create index nc2sysmergesubscriptions on dbo.sysmergesubscriptions (subscriber_server, db_name)
if @@ERROR<>0
goto Error
create index nc3sysmergesubscriptions on dbo.sysmergesubscriptions (replnickname)
if @@ERROR<>0
goto Error
end
exec dbo.sp_MS_marksystemobject sysmergesubscriptions
if @@ERROR <> 0
goto Error
end
exec sys.sp_MScreate_common_dist_tables @subside=1
if object_id('dbo.MSmerge_agent_parameters', 'U') is null
begin
--raiserror('Creating table MSmerge_agent_parameters',0,1)
create table dbo.MSmerge_agent_parameters
(
profile_name sysname NOT NULL,
parameter_name sysname NOT NULL,
value nvarchar(255) NOT NULL
)
if @@ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSmerge_agent_parameters
if @@ERROR <> 0
goto Error
end
if object_id('dbo.MSmerge_replinfo', 'U') is null
begin
--raiserror('Creating table MSmerge_replinfo',0,1)
create table dbo.MSmerge_replinfo
(
repid uniqueidentifier NOT NULL,
use_interactive_resolver bit NOT NULL default 0,
validation_level int not NULL default 0,
resync_gen bigint not NULL default -1,
login_name sysname not NULL default suser_sname(suser_sid()),
hostname sysname NULL,
merge_jobid binary(16) NULL,
sync_info int identity not NULL -- used only by lightweight subscribers
)
if @@ERROR <> 0
goto Error
else
begin
create unique clustered index uc1MSmerge_replinfo
on dbo.MSmerge_replinfo (repid)
if @@ERROR <> 0
goto Error
end
exec dbo.sp_MS_marksystemobject MSmerge_replinfo
if @@ERROR <> 0
goto Error
end
if object_id('dbo.sysmergearticles', 'U') is null
begin
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,
metadata_select_proc sysname NULL,
delete_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,
deleted_cols varbinary(128) NULL default 0x0,
resolver_info nvarchar(517) NULL,
view_sel_proc nvarchar(290) NULL,
gen_cur bigint 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 default 1,
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,
processing_order int NOT NULL default 0,
upload_options tinyint NOT NULL default 0, -- 0 = Default case. Changes on subscriber replicate to publisher.
-- 1 = Changes on subscriber are allowed but not uploaded.
-- 2 = Changes on subscriber are not allowed.
-- 3 = Out of partition changes are not allowed on subscriber.
published_in_tran_pub bit NOT NULL default 0,
lightweight bit not null default 0,
procname_postfix nchar(32) null, -- for lightweight
well_partitioned_lightweight bit null,
before_upd_view_objid int NULL, -- only used by makegeneration because users of other publications may not have access to bi object of this publication
delete_tracking bit default 1, -- when set to false the delete triggers do not update tombstone tables and hence deletes are not replicated.
compensate_for_errors bit not null default 0,
pub_range bigint NULL, -- publisher identity range
range bigint NULL, -- subscriber identity range
threshold int NULL, -- in percentage, set by sp_addmergearticle
stream_blob_columns bit NOT NULL default 0, -- controls whether blob optimization is used or not.
preserve_rowguidcol bit not null default 1 -- 0=rg created by replication
-- Note: Please update sysmergeextendedarticlesview whenever
-- there is a schema change in sysmergearticles
)
if @@error<>0 goto Error
create unique clustered index uc1sysmergearticles on dbo.sysmergearticles(artid, pubid)
if @@ERROR <> 0 goto Error
if not exists (select * from sys.indexes where name = 'nc1sysmergearticles')
begin
create nonclustered index nc1sysmergearticles on dbo.sysmergearticles(nickname)
if @@ERROR <> 0 goto Error
end
if not exists (select * from sys.indexes where name = 'nc2sysmergearticles')
begin
create nonclustered index nc2sysmergearticles on dbo.sysmergearticles(processing_order)
if @@ERROR <> 0 goto Error
end
if not exists (select * from sys.indexes where name = 'nc3sysmergearticles')
begin
create unique nonclustered index nc3sysmergearticles on dbo.sysmergearticles(objid, pubid)
if @@ERROR <> 0 goto Error
end
exec dbo.sp_MS_marksystemobject sysmergearticles
if @@ERROR <> 0 goto Error
grant select(nickname, maxversion_at_cleanup, objid) on dbo.sysmergearticles to public
if @@ERROR <> 0 goto Error
end
if object_id('dbo.MSmerge_conflicts_info', 'U') is null
begin
--raiserror('Creating table MSmerge_conflicts_info',0,1)
create table dbo.MSmerge_conflicts_info
(
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,
MSrepl_create_time datetime not null default getdate(),
origin_datasource_id uniqueidentifier NULL
)
if @@ERROR <> 0
goto Error
create clustered index uc1MSmerge_conflicts_info on MSmerge_conflicts_info(tablenick, rowguid)
if @@ERROR <> 0
goto Error
CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_conflicts_info
ON MSmerge_conflicts_info(tablenick, rowguid, origin_datasource, conflict_type)
if @@ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSmerge_conflicts_info
if @@ERROR <> 0
goto Error
end
if object_id('dbo.MSmerge_metadataaction_request', 'U') is null
begin
create table dbo.MSmerge_metadataaction_request
(
tablenick int not null,
rowguid uniqueidentifier not null,
action tinyint not null,
generation bigint null, -- for hws cleanup
changed int null -- for lws cleanup
)
if @@ERROR <> 0 goto Error
create clustered index ucMSmerge_metadataaction_request on MSmerge_metadataaction_request(tablenick, rowguid)
if @@ERROR <> 0 goto Error
exec dbo.sp_MS_marksystemobject MSmerge_metadataaction_request
if @@ERROR <> 0 goto Error
end
if object_id('MSmerge_errorlineage') is NULL
begin
--raiserror('Creating table MSmerge_errorlineage',0,1)
create table dbo.MSmerge_errorlineage (
tablenick int NOT NULL,
rowguid uniqueidentifier NOT NULL,
lineage varbinary(311)
)
if @@ERROR <> 0 goto Error
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
end
return 0
Error:
return 1
