create procedure sys.sp_MSaddmergeschemaarticle
@pubid uniqueidentifier,
@article sysname,
@source_object sysname,
@type tinyint,
@description nvarchar(255),
@status nvarchar(10),
@pre_creation_command int,
@creation_script nvarchar(255),
@source_owner sysname,
@destination_owner sysname,
@schema_option binary(8),
@destination_object sysname,
@qualified_name nvarchar(270),
@publication sysname,
@snapshot_ready int,
@force_invalidate_snapshot bit,
@processing_order int
AS
SET NOCOUNT ON
DECLARE @retcode int
DECLARE @objid int
DECLARE @artid uniqueidentifier
DECLARE @bInTran bit
DECLARE @valid_schema_options int
DECLARE @bump_to_80 bit
DECLARE @qualname nvarchar(517)
declare @backward_comp_level int
SELECT @bInTran = 0
SELECT @objid = OBJECT_ID(@qualified_name)
-- We are going to bump the compatibility level anyway
-- since we are adding schema only articles
-- check if we need to bump up first
select @backward_comp_level = backward_comp_level from dbo.sysmergepublications where pubid = @pubid
if @backward_comp_level < 40
begin
SELECT @bump_to_80 = 1
RAISERROR(21391, 10, -1, @publication)
end
/*
** Parameter check: Source object type must match
** specified article type.
**
*/
-- Type has to be one of 0x20, 0x40, 0xA0 or 0x80 by the time
-- this sp is called
IF @type = 0x20
BEGIN
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = @objid
AND type IN ('P','PC'))
BEGIN
RAISERROR(21219, 16, -1)
RETURN (1)
END
END
ELSE IF @type = 0x40
BEGIN
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = @objid
AND type = 'V ')
BEGIN
RAISERROR(21221, 16, -1)
RETURN (1)
END
END
ELSE IF @type = 0x80
BEGIN
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = @objid
AND type IN ('FN','TF','IF','FS'))
BEGIN
RAISERROR(21228, 16, -1)
RETURN (1)
END
END
ELSE IF @type = 0xA0
BEGIN
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = @objid
AND type = 'SN')
BEGIN
RAISERROR(20812, 16, -1)
RETURN (1)
END
END
-- Since only the lower 32 bits of @schema_option are
-- currently used, the following check is sufficient.
-- Note that @schema_option should have been padded by now.
DECLARE @schema_option_lodword int
SELECT @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
IF @type = 0x40
BEGIN
SELECT @valid_schema_options = 0xC9F43151
IF (@schema_option_lodword & ~@valid_schema_options) <> 0
BEGIN
RAISERROR (21229, 16, -1)
RETURN (1)
END
END
ELSE
BEGIN
SELECT @valid_schema_options = 0xFAC03001
IF (@schema_option_lodword & ~@valid_schema_options) <> 0
BEGIN
RAISERROR (21222, 16, -1)
RETURN (1)
END
END
/*
** Parameter check: @pre_creation_command must be
** 'drop' (id = 1) or 'none' (id = 0)
*/
IF @pre_creation_command NOT IN (0, 1)
BEGIN
RAISERROR(21223, 16, -1)
RETURN (1)
END
-- Reuse @artid from articles publishing the same object so we can
-- link these articles together during the reinit/republishing scenario
SELECT @artid = NULL
SELECT @artid = artid
FROM dbo.sysmergeschemaarticles
WHERE objid = @objid
IF @artid IS NULL
BEGIN
SELECT @artid = newid()
END
BEGIN TRAN
SAVE TRANSACTION sp_MSaddmergeschemaarticle
SELECT @bInTran = 1
IF @snapshot_ready > 0
BEGIN
IF @force_invalidate_snapshot = 0 and @snapshot_ready = 1
BEGIN
RAISERROR(21364, 16, -1, @article)
GOTO FAILURE
END
if @backward_comp_level < 40
begin
RAISERROR(21360, 10, -1, @publication)
SELECT @bump_to_80 = 1
end
UPDATE dbo.sysmergepublications
SET snapshot_ready=2
WHERE pubid=@pubid
IF @@ERROR<>0
GOTO FAILURE
END
INSERT dbo.sysmergeschemaarticles (name, type, objid, artid, description,
pre_creation_command, pubid, status, creation_script, schema_option,
destination_object, destination_owner, processing_order)
VALUES (@article, @type, @objid, @artid, @description, @pre_creation_command,
@pubid, 1, @creation_script, @schema_option, @destination_object,
@destination_owner, @processing_order)
IF @@ERROR <> 0
GOTO FAILURE
EXEC sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
IF @qualname IS NULL
GOTO FAILURE
EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
--EXEC %%Object(MultiName = @qualname).LockExclusiveMatchID(ID = @objid)
IF @@ERROR <> 0
GOTO FAILURE
-- Make a bit in replinfo to prevent the source object from
-- being dropped
EXEC %%Object(ID = @objid).SetSchemaPublished(Value = 1)
IF @@ERROR <> 0
GOTO FAILURE
IF @bump_to_80=1
BEGIN
EXEC @retcode = sys.sp_MSBumpupCompLevel @pubid, 40
IF @@ERROR<>0 or @retcode<>0
GOTO FAILURE
END
COMMIT TRANSACTION
RETURN (0)
FAILURE:
IF @bInTran = 1
BEGIN
ROLLBACK TRANSACTION sp_MSaddmergeschemaarticle
COMMIT TRANSACTION
END
RETURN (1)