create procedure sys.sp_MSrepl_articlecolumn
(
@publication sysname,
@article sysname,
@column sysname = NULL,
@operation nvarchar(5) = N'add',
-- synctran
@refresh_synctran_procs int = 1, --note this param is bit in sp_articlecolumn
--make it int here so we can overload it to deal with timestamp col
--0 not to refresh sync proc
--1 general purpose refresh
--2 force refresh (only used by DDL trigger)
@ignore_distributor bit = 0,
-- DDL
@change_active int = 0, -- 0: non-ddl, 2: ddl
@force_invalidate_snapshot bit = 0,
@force_reinit_subscription bit = 0,
-- Peer-To-Peer
@internal bit = 0
)
AS
BEGIN
/*
** Declarations.
*/
DECLARE @bit tinyint /* Bit offset */
,@cnt tinyint, @idx tinyint /* Loop counter, index */
,@pubid int /* Publication identification number */
,@retcode int /* Return code for stored procedures */
,@artid int
,@active tinyint
,@objid int /* Article base table id */
,@tablename sysname
,@fSynctranColChanged bit
,@pkkey sysname
,@indid int
,@index_cnt int
,@allow_initialize_from_backup bit
,@autogen_sync_procs_id bit
,@sync_pubid int
,@allow_queued_tran bit
,@allow_sync_tran bit
,@options int
,@OPT_ENABLED_FOR_P2P int
,@status int
,@view_name nvarchar(386)
,@filter_clause nvarchar(4000)
,@sync_objid int
,@art_type tinyint
,@schema_option bigint
,@is_udt bit
,@is_xml bit
,@is_max bit
,@is_filestream bit
,@sync_method tinyint
select @active = 2
,@OPT_ENABLED_FOR_P2P = 0x1
,@fSynctranColChanged = 0
,@allow_initialize_from_backup = 0
,@operation = lower(@operation)
/*
** Security Check
*/
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)
/*
** Check to see if the database has been activated for publication.
** Do not check if @ignore_distributor indicates brute force cleanup.
*/
IF ( (SELECT category & 1
FROM master.dbo.sysdatabases
WHERE name = DB_NAME() collate database_default) = 0 ) and ( @ignore_distributor = 0 )
BEGIN
RAISERROR (14013, 16, -1)
RETURN (1)
END
/*
** Parameter Check: @publication.
** Make sure that the publication exists and that it conforms to the
** rules for identifiers.
*/
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, N'@publication', 'sp_MSrepl_articlecolumn')
RETURN (1)
END
EXECUTE @retcode = sys.sp_validname @publication
IF @retcode <> 0
RETURN (1)
-- get publication metadata
SELECT @pubid = pubid,
@allow_sync_tran = allow_sync_tran,
@allow_queued_tran = allow_queued_tran,
@allow_initialize_from_backup = allow_initialize_from_backup,
@autogen_sync_procs_id = autogen_sync_procs,
@sync_pubid = pubid,
@sync_method = sync_method,
@options = options
FROM syspublications WHERE name = @publication
IF @pubid IS NULL
BEGIN
RAISERROR (20026, 11, -1, @publication)
RETURN (1)
END
/*
** Parameter Check: @article.
** Check to make sure that the article exists in the publication.
*/
IF @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, N'@article', 'sp_MSrepl_articlecolumn')
RETURN (1)
END
/*
Enforce PeerToPeer restrictions
If it is P2P and not from an internal call then it must be called
by a user attempting to filter.
*/
IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
AND @internal = 0
BEGIN
-- Peer-To-Peer publications do not support filtering. Please change the '@publication' parameter value.
RAISERROR(20646, 16, -1, 'filtering', '@publication')
RETURN (1)
END
/*
** Make sure the article exists.
*/
select @artid = sa.artid
,@status = sa.status
,@sync_objid = sa.sync_objid
,@filter_clause = sa.filter_clause
,@art_type = sa.type
,@schema_option = convert(bigint, sa.schema_option)
,@objid = objid
,@tablename = OBJECT_NAME(objid)
FROM sysarticles sa JOIN syspublications sp ON sa.pubid = sp.pubid
WHERE sa.name = @article
AND sp.name = @publication
IF @artid IS NULL
BEGIN
RAISERROR (20027, 11, -1, @article)
RETURN (1)
END
/*
** Error out if this is a not a table based article
*/
IF ((@art_type & 1) = 0 )
BEGIN
RAISERROR (14112, 11, -1 )
RETURN (1)
END
/*
** Parameter Check: @operation.
** The operation can be either 'add' or 'drop'.
*/
IF (@operation NOT IN (N'add', N'drop', N'alter'))
BEGIN
RAISERROR (14019, 16, -1)
RETURN (1)
END
if exists (select * from sys.columns where object_id = @objid and is_column_set = 1)
begin
-- ErrorFormat: Can not publish article '%s' or add add ColumnSet column to its base table '%s' because Replication does not support ColumnSet.
RAISERROR (21864, 16, -1, @article, @tablename)
RETURN (1)
end
if @column is not null
begin
declare @colid int
select @colid=column_id from sys.columns where object_id=@objid and name=@column
if @colid is null
begin
RAISERROR (14020, 16, -1)
RETURN (1)
end
if (@operation = 'drop')
begin
-- Vertical partition is only allowed on table-based article, not IV->table
IF OBJECTPROPERTY(@objid, 'IsTable') <> 1
BEGIN
RAISERROR (14112, 11, -1 )
RETURN (1)
END
-- PK column has to be included in vertical partition
select @indid = indid
,@index_cnt = 1
from sysindexes
where id = @objid and (status & 2048) <> 0 /* PK index */
while (@index_cnt <= 16)
begin
select @pkkey = INDEX_COL(@tablename, @indid, @index_cnt)
if @pkkey is NULL
break
if @pkkey=@column
begin
raiserror(21250, 16, -1, @column)
return (1)
end
select @index_cnt = @index_cnt + 1
end
-- for updating subscribers
if (@allow_sync_tran = 1 or @allow_queued_tran = 1)
begin
-- we cannot drop the versioning column from the partition.
if N'msrepl_tran_version' = @column
begin
RAISERROR (21080, 16, -1)
RETURN (1)
end
-- Only columns that have default values can be outside the partition
-- Note: do check error if it is schema change.
if @change_active = 0 and ColumnProperty(@objid, @column, N'IsIdentity') <> 1 and
-- 189 is timestamp.
not exists (select * from sys.columns where object_id = @objid and
name=@column and (is_nullable=1 or system_type_id = 189)) and
not exists (select * from sysconstraints where id=@objid and
colid=@colid and OBJECTPROPERTY ( constid , 'IsDefaultCnst' ) = 1)
BEGIN
RAISERROR(21165, 16, -1, @column)
return (1)
END
end -- if (@allow_sync_tran = 1 or @allow_queued_tran = 1)
end --if (@operation = 'drop')
end --if @column is not null
-- @ignore_distributor is set to 1 when removing replication forcefully. In that
-- case, no need to check or reinit
if @ignore_distributor = 0
begin
-- Check if there are snapshot or subscriptions and raiserror if needed.
EXECUTE @retcode = sys.sp_MSreinit_article
@publication = @publication,
@article = @article,
@need_new_snapshot = 1,
@need_reinit_subscription = 1
,@force_invalidate_snapshot = @force_invalidate_snapshot /* Force invalidate existing snapshot */
,@force_reinit_subscription = @force_reinit_subscription /* Force reinit subscription */
,@check_only = 1
IF @@ERROR <> 0 OR @retcode <> 0
return (1)
end
begin tran
save TRANSACTION articlecolumn
/*
** If no columns are specified, or if NULL is specified, set all
** the bits in the 'columns' column so all columns will be included.
*/
DECLARE @columnid smallint /* Columnid-1 = bit to set */
if @column IS NULL and @change_active = 2
begin
DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR
SELECT colid FROM #tran_columnstable
end
else IF @column IS NULL
BEGIN
DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR
SELECT column_id FROM sys.columns where
object_id = @objid
END
ELSE
BEGIN
DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR
SELECT column_id
FROM sys.columns
WHERE object_id = @objid AND name = @column
END
OPEN hCartcolumn
FETCH hCartcolumn INTO @columnid
if(@@fetch_status = 0 and @change_active = 2) -- post article column partition info for schema replication
begin
exec sys.sp_replpostcmd 0, @objid, @artid, 51, @objid
end
WHILE (@@fetch_status <> -1)
BEGIN
/*
** Get the column id for this column. We'll use the column id
** to determine the bit in the 'columns' column. The bit we want
** is equal to the columnid - 1.
*/
SELECT @column = col.column_id
,@is_udt = case typ.system_type_id when 240 then 1 else 0 end
,@is_xml = case typ.name when N'xml' then 1 else 0 end
,@is_max = case when typ.name in (N'varchar', N'nvarchar', N'varbinary') and col.max_length = -1 then 1 else 0 end
,@is_filestream = col.is_filestream
FROM sys.columns col
join sys.types typ on typ.user_type_id = col.system_type_id or (typ.system_type_id = 240 and typ.user_type_id = col.user_type_id)
WHERE col.object_id = @objid AND col.column_id = @columnid
IF ((@@error <> 0) OR (@columnid IS NULL))
BEGIN
RAISERROR (14020, 16, -1)
goto UNDO
END
IF (@is_filestream = 1 AND @sync_method in (5,6) and @operation in (N'add', N'alter'))
BEGIN
RAISERROR (21862, 16, -1)
goto UNDO
END
-- add/drop the column to partition
if (@status & 32) > 0 and
exists (select * from sys.columns WHERE object_id = @objid and system_type_id = 189 and column_id = @columnid)
begin
-- adding timestamp column as timestamp on subscribers
-- do not include in partition when adding and set explicit column name list
-- just unmark status when dropping
update sysarticles set
status = case
when (@operation = N'add') then (status | 8) -- add
when (@operation = N'drop') then (status & ~32) -- drop
else status end
where artid = @artid
if @@error <> 0
goto UNDO
-- Always try to remove any timestamp columns if status & 32 != 0
delete dbo.sysarticlecolumns
where artid = @artid
and colid = @columnid
if @@error <> 0
goto UNDO
-- mark for synctran proc refresh
select @fSynctranColChanged = 1
end
else
begin -- not replicating timestamp as timestamp
IF @operation = N'add'
begin
if not exists (select artid, colid from dbo.sysarticlecolumns where artid = @artid and colid = @columnid)
begin
insert dbo.sysarticlecolumns (artid, colid, is_udt, is_xml, is_max) values (@artid, @columnid, @is_udt, @is_xml, @is_max)
select @fSynctranColChanged = 1
end
end
else IF @operation = N'drop'
begin
if exists (select artid, colid from dbo.sysarticlecolumns where artid = @artid and colid = @columnid)
begin
/* Update column published status - we must call this before deleting from dbo.sysarticlecolumns */
/* see bug 335014 */
EXECUTE @retcode = sys.sp_MSarticlecol @artid, @columnid,
N'publish', @operation
IF (@@error <> 0 OR @retcode <> 0)
BEGIN
RAISERROR (14021, 16, -1)
goto UNDO
END
delete from dbo.sysarticlecolumns where artid = @artid and colid = @columnid
select @fSynctranColChanged = 1
end
end
else IF @operation = N'alter'
begin
if exists (select artid, colid from dbo.sysarticlecolumns where artid = @artid and colid = @columnid)
begin
select @fSynctranColChanged = 1
end
end
IF @@error <> 0
BEGIN
RAISERROR (14021, 16, -1)
goto UNDO
END
/*
** if the status has changed, call sp_MSarticlecol to update the publication
** status as appropriate.
*/
if(@fSynctranColChanged = 1 and @operation <> N'drop')
begin
/* Update column published status */
EXECUTE @retcode = sys.sp_MSarticlecol @artid, @columnid,
N'publish', @operation
IF (@@error <> 0 OR @retcode <> 0)
BEGIN
RAISERROR (14021, 16, -1)
goto UNDO
END
end
end -- not replicating timestamp as timestamp
-- fetch the next column
FETCH hCartcolumn INTO @columnid
END -- end of while block
--once we are here, article partition has been updated up to current point, now check on UDT dependency
--exclude DDL case as we need to check in DDL trigger for adding check constraint anyway
if (@change_active != 2) and
(@schema_option & 0x000000B000000020 <> 0) and
exists (select * from sys.columns where object_id = @objid and system_type_id = 240)
begin
if(sys.fn_MSrepl_dependUDT(@objid, @schema_option, @artid) = 1)
begin
raiserror (21839, 16, 1, @article)
goto UNDO
end
end
-- active article fixups
if @change_active<> 0 and @fSynctranColChanged = 1 or
-- Besides schema change, we automatically refresh article view if there are
-- subscriptions. We don't refresh the article view otherwise to avoid the view
-- being dropped and recreated when adding columns into the partition during
-- the creation of the article.
exists (select * from syssubscriptions where artid = @artid and
srvid >= 0)
BEGIN
-- regenerate the article view
-- Only invoke sp_articleview if not manual view and not manual filter
if ( @art_type & 0x4 <> 4 and @art_type & 0x2 <> 2 )
begin
select @view_name = object_name( @sync_objid )
exec @retcode = sys.sp_articleview @publication = @publication,
@article = @article,
@view_name = @view_name,
@filter_clause = @filter_clause,
@change_active = @change_active,
@force_invalidate_snapshot = @force_invalidate_snapshot,
@force_reinit_subscription = @force_reinit_subscription,
@refreshsynctranprocs = 0,
@internal = @internal
IF @@ERROR <> 0 OR @retcode <> 0
goto UNDO
end
END
-- If publication is enabled for Synctran and sprocs are auto-generated.
-- Do the synctran proc generation after article view is regenerated
if @autogen_sync_procs_id = 1 and
(@refresh_synctran_procs = 1 and @fSynctranColChanged = 1 or
@refresh_synctran_procs = 2 -- this is to special case for drop timestamp col in queued article, since @fSynctranColChanged won't be set
)
begin
exec @retcode = sys.sp_articlesynctranprocs @publication, @article, @autogen_sync_procs_id, 0
-- UNDO change to 1 when nvarchar(max) support on xml data is in
-- exec @retcode = sys.sp_articlesynctranprocs @publication, @article, @autogen_sync_procs_id, 1
IF @@ERROR <> 0 OR @retcode <> 0
goto UNDO
end
-- schema replication used @change_active = 2 to prepare, don't invalidate or reinitialize
if @change_active <> 2 and
-- @ignore_distributor is set to 1 when removing replication forcefully. In that
-- case, no need to check or reinit
@ignore_distributor = 0
begin
-- Have to call this stored procedure to invalidate existing snapshot or reint
-- subscriptions if needed
EXECUTE @retcode = sys.sp_MSreinit_article
@publication = @publication,
@article = @article,
@need_new_snapshot = 1,
@need_reinit_subscription = 1
,@force_invalidate_snapshot = @force_invalidate_snapshot /* Force invalidate existing snapshot */
,@force_reinit_subscription = @force_reinit_subscription /* Force reinit subscription */
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
end
/*
** Force the article cache to be refreshed with the new definition.
** Nothing to flush if brute force cleanup.
*/
if ( @ignore_distributor = 0 )
EXECUTE sys.sp_replflush
if ( @allow_initialize_from_backup = 1)
begin
exec @retcode = sys.sp_MSincrementpublicationminautonosynclsn @publication = @publication
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto UNDO end
end
COMMIT TRANSACTION
END
return (0)
UNDO:
if @@trancount > 0
begin
ROLLBACK TRANSACTION articlecolumn
commit tran
end
RETURN (1)
