create procedure sys.sp_MScreate_sub_tables_internal
(
@tran_sub_table bit = 0,
@property_table bit = 1,
@sqlqueue_table bit = 0,
@subscription_articles_table bit = 0,
@p2p_table bit = 0
)
as
BEGIN
set nocount on
declare @retcode int
-- for transactional subscriptions
IF @tran_sub_table = 1
BEGIN
-- MSreplication_subscriptions
if object_id(N'dbo.MSreplication_subscriptions', 'U') is NULL
begin
-- table does not exist - create the table
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
CREATE TABLE dbo.MSreplication_subscriptions
(
publisher sysname NOT NULL,
publisher_db sysname NULL,
publication sysname NULL,
independent_agent bit NOT NULL,
subscription_type int NOT NULL,
distribution_agent sysname NULL,
time smalldatetime NOT NULL,
description nvarchar(255) NULL,
transaction_timestamp varbinary(16) NOT NULL,
update_mode tinyint NOT NULL,
agent_id binary(16) NULL,
subscription_guid binary(16) NULL,
subid binary(16) NULL,
immediate_sync bit NOT NULL default 1, -- sync_mode with a default of 1
)
IF @@ERROR <> 0
GOTO UNDO
CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON
MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type, transaction_timestamp)
IF @@ERROR <> 0
GOTO UNDO
exec dbo.sp_MS_marksystemobject 'MSreplication_subscriptions'
IF @@ERROR <> 0
GOTO UNDO
COMMIT TRAN sp_MScreate_sub_tables
end
else
begin
-- table exists - add new columns
if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1
BEGIN
alter table dbo.MSreplication_subscriptions alter column distribution_agent sysname null
end
if exists (select * from sys.indexes where object_id = object_id('MSreplication_subscriptionss') and name = 'uc1MSReplication_subscriptions'
and is_unique = 'True' )
begin
if not exists (select * from sys.indexes SI
join sys.index_columns SIC on SI.object_id = SIC.object_id and SI.index_id = SIC.index_id
join sys.columns SC on SI.object_id = SC.object_id and SC.column_id = SIC.column_id
where SI.object_id = object_id('MSreplication_subscriptions') and is_unique = 'True' and SC.name = 'transaction_timestamp')
begin
drop index MSreplication_subscriptions.uc1MSReplication_subscriptions
CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON
MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type, transaction_timestamp)
end
end
end -- MSreplication_subscriptions
-- MSsubscription_agents
IF object_id(N'dbo.MSsubscription_agents', 'U') is NULL
BEGIN
-- table does not exist - create the table
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
CREATE TABLE dbo.MSsubscription_agents
(
id int identity,
publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,
subscription_type int NOT NULL,
queue_id sysname NULL,
update_mode tinyint default 0 not null, -- 0 = read only, 1 = sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued failover
failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
spid int NOT NULL,
login_time datetime NOT NULL,
allow_subscription_copy bit default 0 not null,
attach_state int default 0 not null, -- 0: not attached 1 attached but not processed 2 attached and processed.
attach_version binary(16) default newid() not null,
last_sync_status int NULL, -- allow null for upgrade
last_sync_summary sysname NULL, -- allow null for upgrade
last_sync_time datetime NULL, -- allow null for upgrade
queue_server sysname NULL -- only used for MSMQ based updating subscribers
)
IF @@ERROR <> 0
GOTO UNDO
CREATE unique CLUSTERED INDEX ucMSsubscription_agents ON dbo.MSsubscription_agents
(publication, publisher_db, publisher, subscription_type)
CREATE INDEX ucMSsubscription_agents_id ON dbo.MSsubscription_agents
(id)
exec dbo.sp_MS_marksystemobject 'MSsubscription_agents'
IF @@ERROR <> 0
GOTO UNDO
grant select on dbo.MSsubscription_agents to public
IF @@ERROR <> 0
GOTO UNDO
COMMIT TRAN sp_MScreate_sub_tables
END
ELSE
BEGIN
-- table exists - add new columns
if not exists (select * from sys.columns where
object_id = object_id(N'dbo.MSsubscription_agents') and
name = 'queue_server')
begin
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
alter table dbo.MSsubscription_agents add queue_server sysname NULL
if @@error != 0
goto UNDO
exec @retcode = sys.sp_MSupdate_mqserver_subdb
if @retcode != 0 or @@error != 0
goto UNDO
COMMIT TRAN sp_MScreate_sub_tables
end
END -- MSsubscription_agents
-- MSreplication_objects
IF object_id(N'dbo.MSreplication_objects', 'U') is NULL
BEGIN
-- table does not exist - create the table
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
CREATE TABLE dbo.MSreplication_objects
(
publisher sysname NULL,
publisher_db sysname NULL,
publication sysname NULL,
object_name sysname NOT NULL,
object_type char(2) NOT NULL
,article sysname NULL
)
IF @@ERROR <> 0
GOTO UNDO
CREATE CLUSTERED INDEX ucMSreplication_objects ON dbo.MSreplication_objects(object_name)
exec dbo.sp_MS_marksystemobject 'dbo.MSreplication_objects'
IF @@ERROR <> 0
GOTO UNDO
COMMIT TRAN sp_MScreate_sub_tables
END -- MSreplication_objects
ELSE
BEGIN
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
if exists (select * from sys.indexes where name = N'ucMSreplication_objects'
and object_id = OBJECT_ID(N'dbo.MSreplication_objects')
and is_unique = 1)
begin --remove uniqueness constraint
drop index ucMSreplication_objects on dbo.MSreplication_objects
CREATE CLUSTERED INDEX ucMSreplication_objects ON dbo.MSreplication_objects(object_name)
end
-- table exists - add new columns
if not exists (select * from sys.columns where
object_id = object_id(N'dbo.MSreplication_objects') and
name = 'article')
begin
alter table dbo.MSreplication_objects add article sysname NULL
if @@error != 0
goto UNDO
end
COMMIT TRAN sp_MScreate_sub_tables
END -- MSreplication_objects
END -- @tran_sub_table = 1
-- Is property table flag enabled
if (@property_table = 1)
begin
-- MSsubscription_properties
if object_id(N'dbo.MSsubscription_properties', 'U') is NULL
BEGIN
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
exec @retcode= sys.sp_MScreate_subscription_properties_table
if @@error <> 0 or @retcode <> 0 goto UNDO
COMMIT TRAN sp_MScreate_sub_tables
END -- MSsubscription_agents
else
begin
-- NOTE : TEMPORARY - remove this block
-- after the builds have stabilized
-- this code is already in sp_vupgrade_MSsubscription_properties
-- table exists - add new columns
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
if not exists (select * from sys.columns where
object_id = object_id(N'dbo.MSsubscription_properties') and
name = 'publisherlink')
begin
alter table dbo.MSsubscription_properties add publisherlink sysname NULL
if @@error != 0
goto UNDO
end
if not exists (select * from sys.columns where
object_id = object_id(N'dbo.MSsubscription_properties') and
name = 'publisherlinkuser')
begin
alter table dbo.MSsubscription_properties add publisherlinkuser sysname NULL
if @@error != 0
goto UNDO
end
COMMIT TRAN sp_MScreate_sub_tables
end
end -- @property_table = 1
-- SQL Queue related tables
IF @sqlqueue_table = 1
BEGIN
declare @folddata bit
-- MSreplication_queue
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
if object_id('MSreplication_queue', 'U') is not NULL
BEGIN
-- table exists - check if we need to add columns
if not exists (select * from sys.columns where
object_id = object_id(N'dbo.MSreplication_queue') and
name = 'cmdstate')
BEGIN
ALTER TABLE dbo.MSreplication_queue ADD cmdstate bit DEFAULT 0 NOT NULL
IF @@ERROR <> 0
GOTO UNDO
END
-- change data column from text to varbinary(8000)
-- SPECIAL CASE : since a simple ALTER does not work here
-- we create a temp table to save the existing data and then
-- recreate the table
if exists (select * from sys.columns
where object_id = object_id(N'dbo.MSreplication_queue') and
name = 'data' and system_type_id = 34)
begin
-- save existing column data
if exists (select * from dbo.MSreplication_queue)
begin
select @folddata = 1
create table #olddata (
publisher sysname collate database_default not null ,
publisher_db sysname collate database_default not null ,
publication sysname collate database_default not null ,
tranid sysname collate database_default not null ,
data varbinary(8000) NULL ,
datalen int,
commandtype int,
insertdate datetime ,
orderkey bigint,
cmdstate bit)
insert into #olddata
select publisher, publisher_db, publication, tranid, CAST(data as varbinary(8000)),
datalen, commandtype, insertdate, orderkey, cmdstate
from dbo.MSreplication_queue
if @@error != 0
goto UNDO
end
-- drop table
DROP TABLE dbo.MSreplication_queue
IF @@ERROR <> 0
GOTO UNDO
end
END
-- Create table if it does not exist
if object_id(N'dbo.MSreplication_queue', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSreplication_queue (
publisher sysname NOT NULL ,
publisher_db sysname NOT NULL ,
publication sysname NOT NULL ,
tranid sysname NOT NULL ,
data varbinary(8000) NULL ,
datalen int DEFAULT 0 ,
commandtype int NULL ,
insertdate datetime DEFAULT GETDATE(),
orderkey bigint IDENTITY(1,1) PRIMARY KEY,
cmdstate bit DEFAULT 0 NOT NULL
)
IF @@ERROR <> 0
GOTO UNDO
CREATE NONCLUSTERED INDEX nc1MSreplication_queue ON
MSreplication_queue(publisher, publisher_db, publication, tranid)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject 'MSreplication_queue'
if @retcode <> 0 or @@error <> 0
GOTO UNDO
-- Do we need to restore old data
if (@folddata = 1)
begin
insert dbo.MSreplication_queue (publisher, publisher_db, publication, tranid, data,
datalen, commandtype, insertdate, cmdstate)
select publisher, publisher_db, publication, tranid, data,
datalen, commandtype, insertdate, cmdstate
from #olddata
order by orderkey
if @@error != 0
goto UNDO
drop table #olddata
if @@error != 0
goto UNDO
end
END
-- Table MSrepl_queuedtraninfo
-- Create table if it does not exist
if object_id(N'dbo.MSrepl_queuedtraninfo', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSrepl_queuedtraninfo (
publisher sysname NOT NULL ,
publisher_db sysname NOT NULL ,
publication sysname NOT NULL ,
tranid sysname NOT NULL ,
maxorderkey bigint NOT NULL,
commandcount bigint NOT NULL
)
IF @@ERROR <> 0
GOTO UNDO
CREATE CLUSTERED INDEX nc1MSrepl_queuedtraninfo ON
MSrepl_queuedtraninfo(publisher,publisher_db,publication,tranid)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject 'MSrepl_queuedtraninfo'
if @retcode <> 0 or @@error <> 0
GOTO UNDO
-- Populate this table as necessary
exec @retcode = sp_populateqtraninfo
if @retcode <> 0 or @@error <> 0
GOTO UNDO
END
IF object_id(N'dbo.MSsubscription_articlecolumns', 'U') is NULL
BEGIN
create table dbo.MSsubscription_articlecolumns
(
agent_id int NOT NULL, -- related entry in MSsubscription_agents, can't add FK since source is not PK
artid int NOT NULL,
colid int NOT NULL
)
if @@error <> 0
GOTO UNDO
create unique clustered index idx_MSsubscription_articlecolumns
on MSsubscription_articlecolumns (agent_id, artid, colid)
if @@error <> 0
GOTO UNDO
exec @retcode = dbo.sp_MS_marksystemobject 'MSsubscription_articlecolumns'
if @retcode <> 0 or @@error <> 0
GOTO UNDO
END
else
begin
if exists (select * from sys.columns sc inner join sys.types st on sc.system_type_id = st.system_type_id
where object_id = object_id('dbo.MSsubscription_articlecolumns', 'U') and sc.name = N'colid' and st.name = N'smallint')
begin
if exists (select * from sysindexes where id = object_id('dbo.MSsubscription_articlecolumns') and name ='idx_MSsubscription_articlecolumns')
begin
drop index MSsubscription_articlecolumns.idx_MSsubscription_articlecolumns
if @@error <> 0 return 1
end
alter table dbo.MSsubscription_articlecolumns alter column colid int NOT null
if @@error <> 0 return 1
create unique clustered index idx_MSsubscription_articlecolumns on MSsubscription_articlecolumns (agent_id, artid, colid)
if @@error <> 0 return 1
end
end
COMMIT TRAN sp_MScreate_sub_tables
END -- @sqlqueue_table = 1
IF @subscription_articles_table = 1
or @p2p_table = 1
BEGIN
BEGIN TRAN sp_MScreate_sub_tables
SAVE TRAN sp_MScreate_sub_tables
-- Create system table MSsubscription_articles if it does not exist
IF object_id(N'dbo.MSsubscription_articles', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSsubscription_articles
(
agent_id int NOT NULL, -- related entry in MSsubscription_agents
artid int NOT NULL, -- article id
article sysname, -- article name
dest_table sysname, -- destination table
owner sysname, -- destination owner
cft_table sysname NULL -- conflict table
)
IF @@ERROR <> 0
GOTO UNDO
CREATE UNIQUE CLUSTERED INDEX ucMSsubscription_articles ON dbo.MSsubscription_articles(agent_id, artid)
IF @@ERROR <> 0
GOTO UNDO
exec @retcode = dbo.sp_MS_marksystemobject 'MSsubscription_articles'
if @retcode <> 0 or @@error <> 0
GOTO UNDO
END
COMMIT TRAN sp_MScreate_sub_tables
END
-- setup the subscriber tables for PeerToPeer replication
IF @p2p_table = 1
BEGIN
-- the proc wraps all table creation with a BEGIN TRAN and will
-- roll it back internally if a failure occurs. That's why all
-- we do here is return 1 if an error occurs instead of an "UNDO"
exec @retcode = sys.sp_MScreate_peer_tables
if @retcode <> 0 or @@error <> 0
return 1
END
-- All done - we have already committed any
-- open transactions if we have reached here
return(0)
UNDO:
-- Rollback to the savepoint and commit
-- this way we can undo the operations
-- contained in this SP and return error
ROLLBACK TRAN sp_MScreate_sub_tables
COMMIT TRAN sp_MScreate_sub_tables
return(1)
END
