create procedure sys.sp_MSarticlecol
(
@artid int,
@colid int = NULL,
@type nvarchar(10), -- 'publish', 'nonsqlsub'
@operation nvarchar(5) -- 'add', 'drop'
)
AS
begin
set nocount on
-- Declarations.
DECLARE @tabid int --Article base table id
,@retcode int
,@status bit
,@allow_dts bit
, @pubid int -- hydra compatible colid
,@colname sysname
,@objname nvarchar(517)
declare @coltable table(name sysname, column_id int, is_computed bit)
SELECT @tabid = sa.objid, @pubid = sa.pubid, @allow_dts = sp.allow_dts
FROM sysarticles as sa join syspublications as sp
on sa.pubid = sp.pubid
WHERE sa.artid = @artid
-- Get qualified object name for error logging
EXEC sys.sp_MSget_qualified_name @tabid, @objname output
IF @objname IS NULL
return 1
if (@colid is NULL)
begin
insert into @coltable (name, column_id, is_computed)
select sc.name, sc.column_id, sc.is_computed
from sys.columns as sc join sysarticlecolumns as sac
on sc.column_id = sac.colid
WHERE sc.object_id = @tabid
and sac.artid = @artid
-- Check if any columns computed
if exists (select * from sys.columns where object_id = @tabid and is_computed = 1)
begin
-- Check if any PK column is computed
if (sys.fn_ispkcomputed(@tabid) = 1)
begin
-- add base column for any primary computed keys
insert into @coltable (name, column_id, is_computed)
select sc.name, sc.column_id, sc.is_computed
from sys.columns as sc
join
(
select bc.column_id
from sys.fn_basecolsofcomputedpkcol(@tabid, NULL) as bc
where bc.column_id not in (select column_id from sysarticlecolumns where artid = @artid)
) as pkb
on sc.column_id = pkb.column_id
WHERE sc.object_id = @tabid
end -- PK has computed cols
end -- exists computed cols
end -- colid is null
else
begin
-- colid was specified
insert into @coltable (name, column_id, is_computed)
select sc.name, sc.column_id, sc.is_computed
from sys.columns as sc join sysarticlecolumns as sac
on sc.column_id = sac.colid
WHERE sc.object_id = @tabid
and sc.column_id = @colid
and sac.artid = @artid
if exists (select * from @coltable where is_computed = 1)
begin
-- add base column for this computed column if this is part of PK
insert into @coltable (name, column_id, is_computed)
select sc.name, sc.column_id, sc.is_computed
from sys.columns as sc
join
(
select bc.column_id
from sys.fn_basecolsofcomputedpkcol(@tabid, @colid) as bc
where bc.column_id not in (select column_id from sysarticlecolumns where artid = @artid)
) as pkb
on sc.column_id = pkb.column_id
where sc.object_id = @tabid
end
end
-- loop through the selected columns
DECLARE #hCartcol CURSOR LOCAL FAST_FORWARD FOR
SELECT name, column_id FROM @coltable
-- begin transaction
BEGIN TRANSACTION
save tran sp_MSarticlecol
-- open and enumerate the cursor
OPEN #hCartcol
FETCH #hCartcol INTO @colname, @colid
WHILE (@@fetch_status <> -1)
BEGIN
IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = N'add'
BEGIN
IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'publish'
BEGIN
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplicated(Value = 1)
IF @@ERROR <> 0
BEGIN
RAISERROR(15165, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
--for tables with has_replication_filter on (see sp_MSsetfilteredstatus),
--set all replicated column (except legacy blob) as part of PK so SE logs
--old value for deletes
--this is done inside sp_MSsetfilteredstatus anayway but set it here anyway
--so that we don't depand on the order of how sp_MSsetfilteredstatus and sp_articlecolumn gets called
if exists (select * from sys.tables where object_id = @tabid
and has_replication_filter = 1)
and not exists (select * from sys.columns where object_id = @tabid
and column_id = @colid
and (system_type_id in (34, 35, 99)
or user_type_id in (34, 35, 99))) -- image, text, ntext
begin
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetLogForRepl(Value = 1)
IF @@ERROR <> 0
BEGIN
RAISERROR(15052, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
end
END
ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'nonsqlsub'
BEGIN
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplNonSQLSub(Value = 1)
IF @@ERROR <> 0
BEGIN
RAISERROR(15052, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
IF @allow_dts = 1
BEGIN
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetDTSReplicated(Value = 1)
IF @@ERROR <> 0
BEGIN
RAISERROR(15052, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
END
END
END
ELSE /* drop */
BEGIN
/*
** Is there another non-sql server subscription on the column?
** Or another article publishing the column?
*/
EXEC @retcode = sys.sp_MSarticlecolstatus @artid = @artid, @tabid = @tabid, @colid = @colid, @type = @type, @status = @status OUTPUT
IF @@error <> 0 OR @retcode <> 0
BEGIN
GOTO ERROR_ABORT_EXIT
END
IF (@status = 0)
BEGIN
IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'publish'
BEGIN
/* Clear 'publish' bit */
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplicated(Value = 0)
IF @@ERROR <> 0
BEGIN
RAISERROR(15052, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetLogForRepl(Value = 0)
IF @@ERROR <> 0
BEGIN
RAISERROR(15052, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
END
ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'nonsqlsub'
BEGIN
/* Clear 'non-sql server subscription' bit */
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplNonSQLSub(Value = 0)
IF @@ERROR <> 0
BEGIN
RAISERROR(15052, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
if @allow_dts = 1
BEGIN
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetDTSReplicated(Value = 0)
IF @@ERROR <> 0
BEGIN
RAISERROR(15052, -1, -1, @objname)
GOTO ERROR_ABORT_EXIT
END
END
END
END
END
-- fetch next column
FETCH #hCartcol INTO @colname, @colid
END
CLOSE #hCartcol
DEALLOCATE #hCartcol
COMMIT TRANSACTION
RETURN (0)
ERROR_ABORT_EXIT:
ROLLBACK TRAN sp_MSarticlecol
COMMIT TRAN
RETURN (1)
end
