May 7, 2012

sp_MSchange_publication (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSchange_publication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @property
, nvarchar @value)

MetaData:

 CREATE PROCEDURE sys.sp_MSchange_publication   
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@property sysname,
@value nvarchar(255)
)
as
BEGIN
set nocount on

declare @publisher_id smallint
,@publication_type int
,@retcode int
,@max_distretention int
,@retention_value int
,@cmd nvarchar(4000)
,@cmd2 nvarchar(4000)
,@cmd3 nvarchar(4000)
,@retention_period_unit tinyint

declare @setvalue int
--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSchange_publication', 'distribution')
return (1)
end
-- Check if publisher is a defined as a distribution publisher in the current database
exec @retcode = sys.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
if @retcode <> 0
begin
return(1)
end

-- Charater properties --

begin tran
save tran sp_MSchange_publication

IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) ='description'
BEGIN
UPDATE dbo.MSpublications SET description = @value
WHERE publisher_id = @publisher_id AND
publisher_db = @publisher_db AND
publication = @publication
IF @@ERROR <> 0
goto UNDO
END
ELSE if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('retention')
BEGIN
select @retention_value = convert(int, @value)
select @publication_type = publication_type
from dbo.MSpublications
WHERE publisher_id = @publisher_id AND
publisher_db = @publisher_db AND
publication = @publication
UPDATE dbo.MSpublications set retention=@retention_value
WHERE publisher_id = @publisher_id AND
publisher_db = @publisher_db AND
publication = @publication
if @@ERROR<>0
goto UNDO
END
ELSE if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('retention_period_unit')
BEGIN
select @retention_period_unit = convert(tinyint, @value)
UPDATE dbo.MSpublications set retention_period_unit=@retention_period_unit
WHERE publisher_id = @publisher_id AND
publisher_db = @publisher_db AND
publication = @publication
if @@ERROR<>0
goto UNDO
END
ELSE if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = ('queue_type')
BEGIN
--
-- Value could be 1 or 2
--
if (convert(int, @value) = 1)
begin
--
-- Changing to MSMQ (for pre Yukon publishers)
-- Distributor needs to support MSMQ 2.0 - Just check that
-- Now we use xp_MSver to detect NT OS version
-- MSMQ subscription only allowed for platforms that support MSMQ 2.0
-- version 5.0.2195 or higher
--
create table #tosversion ( propid int, propname sysname collate database_default, value int, charvalue nvarchar(255) collate database_default)
insert into #tosversion (propid, propname, value, charvalue)
exec master.dbo.xp_msver N'WindowsVersion'

declare @vervalue int
,@lobyte tinyint
,@hibyte tinyint
,@loword smallint
,@hiword smallint

--
-- low order byte of low order word = OSmajor, high order byte of low order word = OSminor
-- high order word = OSbuild
--
select @vervalue = value from #tosversion where propname = N'WindowsVersion'
select @loword = (@vervalue & 0xffff)
,@hiword = (@vervalue / 0x10000) & 0xffff
select @lobyte = @loword & 0xff
,@hibyte = (@loword / 100) & 0xff
drop table #tosversion
--
-- check for OS major version
--
if (@lobyte < 5)
begin
raiserror(21334, 16, 6, '2.0')
goto UNDO
end
--
-- check for OS build version
--
if (@lobyte = 5 and @hiword < 2195)
begin
raiserror(21334, 16, 7, '2.0')
goto UNDO
end
end
else if (convert(int, @value) = 2)
begin
--
-- Changing to SQL (for upgrade)
-- cleanup the MSMQ created for this subscriptions that are active
-- and switch to SQL for distribution agents
--
exec @retcode = sp_MSrefreshmqtosql @publisher ,@publisher_db, @publication
IF @@ERROR <> 0
goto UNDO
end
END
ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'options'
BEGIN
UPDATE dbo.MSpublications
SET options = CONVERT(int, @value)
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
AND publication = @publication
IF @@ERROR <> 0
GOTO UNDO
END
ELSE if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = ('min_autonosync_lsn')
BEGIN
UPDATE dbo.MSpublications
set min_autonosync_lsn = case when @value is null then NULL else CONVERT(varbinary(16), @value) end
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
AND publication = @publication
if @@error <> 0
GOTO UNDO
END
ELSE
BEGIN
SELECT @cmd = N''
SELECT @cmd = @cmd + N'UPDATE dbo.MSpublications '
SELECT @cmd = @cmd + N' SET ' + LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) + N' = '
-- @value can be 255 nchars, so don't append it --
SELECT @cmd2 = N' WHERE publisher_id = ' + STR(@publisher_id)
SELECT @cmd2 = @cmd2 + N' AND publisher_db = N' + quotename(@publisher_db, N'''')
SELECT @cmd2 = @cmd2 + N' AND publication = N' + quotename(@publication, N'''')
EXECUTE (@cmd + @value + @cmd2)
IF @@ERROR <> 0
goto UNDO
END

COMMIT TRAN
RETURN(0)

UNDO:
IF (@@TRANCOUNT > 0)
begin
ROLLBACK TRAN sp_MSchange_publication
COMMIT TRAN
end
RETURN (1)
END

No comments:

Post a Comment

Total Pageviews