May 7, 2012

sp_MSchange_snapshot_agent_properties (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_snapshot_agent_properties(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @frequency_type
, int @frequency_interval
, int @frequency_subday
, int @frequency_subday_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @active_start_date
, int @active_end_date
, int @active_start_time_of_day
, int @active_end_time_of_day
, nvarchar @snapshot_job_name
, int @publisher_security_mode
, nvarchar @publisher_login
, nvarchar @publisher_password
, nvarchar @job_login
, nvarchar @job_password
, nvarchar @publisher_type)

MetaData:

 create procedure sys.sp_MSchange_snapshot_agent_properties  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@frequency_type int,
@frequency_interval int,
@frequency_subday int,
@frequency_subday_interval int,
@frequency_relative_interval int,
@frequency_recurrence_factor int,
@active_start_date int,
@active_end_date int,
@active_start_time_of_day int,
@active_end_time_of_day int,
@snapshot_job_name nvarchar(100),
@publisher_security_mode int,
@publisher_login sysname,
@publisher_password nvarchar(524),
@job_login nvarchar(257),
@job_password sysname,
@publisher_type sysname
)
as
begin
declare @retcode bit,
@publisher_id int,
@agent_id int,
@agent_exists bit,
@job_id uniqueidentifier,
@job_step_uid uniqueidentifier,
@pubsecmode int,
@proxy_id int

-- security: Has to be executed by SA of dist db
if is_srvrolemember('sysadmin') != 1
begin
-- You do not have the required permissions to complete the operation.
raiserror (20604, 16, -1)
return 1
end

select @proxy_id = NULL

begin transaction tran_sp_MSchange_snapshot
save transaction tran_sp_MSchange_snapshot

-- retrieve the publisher id
select @publisher_id = server_id
from sys.servers
where upper(name) = upper(@publisher)
if @publisher_id is null
begin
-- Publisher @publisher does not exist.
raiserror(21618, 16, -1, @publisher)
goto FAILED
end

if @publisher_security_mode is not NULL
or @publisher_login is not NULL
or @publisher_password is not NULL
begin

-- if WINDOWS authentication then clear out the login/password
if @publisher_security_mode = 1
begin
select @publisher_login = '',
@publisher_password = newid()
end

-- Encrypt the password before storing
exec @retcode = sys.sp_MSreplencrypt @publisher_password OUTPUT
if @@error <> 0 or @retcode <> 0
goto FAILED

update MSsnapshot_agents
set publisher_security_mode = isnull(@publisher_security_mode, publisher_security_mode),
publisher_login = isnull(@publisher_login, publisher_login),
publisher_password = isnull(@publisher_password, publisher_password)
where publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication = @publication
if @@error <> 0
goto FAILED
end

if @job_login is not NULL
or @job_password is not NULL
or @snapshot_job_name is not NULL
or @frequency_type is not NULL
or @frequency_interval is not NULL
or @frequency_subday is not NULL
or @frequency_subday_interval is not NULL
or @frequency_relative_interval is not NULL
or @frequency_recurrence_factor is not NULL
or @active_start_date is not NULL
or @active_end_date is not NULL
or @active_start_time_of_day is not NULL
or @active_end_time_of_day is not NULL
begin
-- HETERO check only
if @publisher_type != N'MSSQLSERVER'
begin
-- we can only allow @job_login change for Hetero when
-- the publisher_security_mode is standard security...
select @pubsecmode = security_mode
from msdb..MSdistpublishers
where name = @publisher
and distribution_db = db_name()

if @pubsecmode is not NULL
AND @pubsecmode != 0
AND @job_login is not NULL
begin
-- "@job_login can only be specified/changed for heterogeneous publications when the publisher security_mode (for sp_adddistpublisher) is set to 0."
RAISERROR(21842, 16, -1, '@job_login', 'the publisher security_mode (for sp_adddistpublisher)', '0')
goto FAILED
end
end

DECLARE #cursorSnapAgents CURSOR LOCAL FAST_FORWARD FOR
SELECT mssa.name,
CAST(mssa.job_id as uniqueidentifier),
mssa.job_step_uid
FROM msdb.dbo.sysjobs_view sjv
JOIN MSsnapshot_agents mssa
ON sjv.job_id = CAST(mssa.job_id as uniqueidentifier)
JOIN msdb.dbo.sysjobsteps sjs
ON sjv.job_id = sjs.job_id
AND mssa.job_step_uid = sjs.step_uid
WHERE mssa.publisher_id = @publisher_id
AND mssa.publisher_db = @publisher_db
AND mssa.publication = @publication
FOR READ ONLY

OPEN #cursorSnapAgents

FETCH #cursorSnapAgents INTO @snapshot_job_name, @job_id, @job_step_uid

WHILE @@FETCH_STATUS <> -1
BEGIN
IF @job_id IS NOT NULL
AND @job_step_uid IS NULL
BEGIN
SELECT @job_step_uid = sjs.step_uid
FROM msdb.dbo.sysjobs_view as sjv
JOIN msdb.dbo.sysjobsteps as sjs
ON sjv.job_id = sjs.job_id
WHERE sjv.job_id = @job_id
AND sjv.master_server = 0
AND UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
AND sjv.category_id = 15
AND sjs.subsystem = N'Snapshot'
AND sjs.database_name = db_name()
END

exec @retcode = sys.sp_MSchange_repl_job @id = @job_id,
@step_uid = @job_step_uid,
@name = @snapshot_job_name,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@login = @job_login,
@password = @job_password,
@proxy_id = @proxy_id OUTPUT
if @@error <> 0 or @retcode <> 0
goto FAILED

FETCH #cursorSnapAgents INTO @snapshot_job_name, @job_id, @job_step_uid
END

CLOSE #cursorSnapAgents
DEALLOCATE #cursorSnapAgents
end

commit transaction tran_sp_MSchange_snapshot

return 0
FAILED:
rollback transaction tran_sp_MSchange_snapshot
commit transaction

return 1
end

sp_MScdc_cleanup_job (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_MScdc_cleanup_job()

MetaData:

 create procedure [sys].[sp_MScdc_cleanup_job]  
as
begin
declare @retcode int
,@db_name sysname
,@retention bigint
,@threshold bigint

set nocount on

set @db_name = db_name()

-- Verify caller is authorized to clean up database change tables
if (isnull(is_srvrolemember('sysadmin'),0) = 0) and (isnull(is_member('db_owner'),0) = 0)
begin
raiserror(22904, 16, -1)
return(1)
end

-- Verify database is enabled for change data capture
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
raiserror(22910, 16, -1, @db_name)
return(1)
end

-- get cleanup retention and threshold from msdb
exec @retcode = sp_cdc_get_cleanup_retention @retention output, @threshold output
if @retcode <> 0 or @@error <> 0
return(1)

-- If retention is negative or greater than 52594800 ( 100 years) fail
if (@retention is null) or (@retention <= 0) or (@retention > 52594800)
begin
raiserror(22994, 16, -1)
return(1)
end

-- If threshold is negative fail
if (@threshold is null) or (@threshold <= 0)
begin
raiserror(22850, 16, -1)
return(1)
end

-- Call internal stored procedure to do the work here.
-- Switch to database 'cdc' user to mitigate against malicious DML triggers.
execute as user = 'cdc'

exec @retcode = sys.sp_cdc_cleanup_job_internal @retention, @threshold
if @retcode <> 0 or @@error <> 0
begin
revert
return(1)
end

revert

return(0)
end

sp_MScdc_ddl_event (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_MScdc_ddl_event(xml @EventData)

MetaData:

 create procedure sys.sp_MScdc_ddl_event  
(
@EventData xml
)
as
begin
declare @retcode int
,@ddl_authorized bit

-- if CDC is not enabled for this db, don't do anything
if (sys.fn_cdc_is_db_enabled() != 1)
begin
return 0
end

-- Determine whether the caller is authorized before switching to dbo
-- Note: We don't want to prevent DDL that does not impact cdc behavior.
-- Since we can't determine immediately whether we are interested
-- in the DDL event or not, we simply retain the authorization
-- information so it can be checked later, if needed.
set @ddl_authorized = 1
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
and (isnull(is_member('db_owner'),0) = 0)
and (isnull(is_member('db_ddladmin'),0) = 0)
begin
set @ddl_authorized = 0
end

-- Execute logic using an internal stored procedure that executes as 'dbo'
exec @retcode = sys.sp_cdc_ddl_event_internal @EventData, @ddl_authorized

if (@@error <> 0) or (@retcode <> 0)
begin
return 1
end

return 0
end

sp_MSchange_mergepublication (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_mergepublication(uniqueidentifier @pubid
, nvarchar @property
, nvarchar @value)

MetaData:

 create procedure sys.sp_MSchange_mergepublication (  
@pubid uniqueidentifier,
@property sysname = NULL,
@value nvarchar(2000) = NULL
) AS

set nocount on

declare @value_numeric int
declare @value_bit bit
-- Security check
if 1 <> is_member('db_owner')
begin
RAISERROR (15247, 11, -1)
return (1)
end

--
-- Parameter Check: @property.
-- Check to make sure that @property is a valid property
--
if @property IS NULL OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT in
('allow_subscription_copy',
'centralized_conflicts',
'conflict_logging',
'generation_leveling_threshold',
'automatic_reinitialization_policy')
begin
raiserror (21053, 16, -1, @property)
return (1)
end

BEGIN TRAN MSchange_mergepublication
save tran MSchange_mergepublication

if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_subscription_copy'
begin
if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
select @value_bit = 1
else
select @value_bit = 0

UPDATE dbo.sysmergepublications
SET allow_subscription_copy = @value_bit
WHERE pubid = @pubid
if @@error <> 0
BEGIN
GOTO UNDO
END
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'centralized_conflicts'
begin
if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
select @value_numeric = 1
else
select @value_numeric = 0

UPDATE dbo.sysmergepublications
SET centralized_conflicts = @value_numeric,
decentralized_conflicts = case @value_numeric
when 1 then 0
else 0
end
WHERE pubid = @pubid
if @@error <> 0
BEGIN
GOTO UNDO
END
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'conflict_logging'
begin
if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'publisher'
select @value_numeric = 1
else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'both'
select @value_numeric = 2
else
select @value_numeric = 0

UPDATE dbo.sysmergepublications
SET centralized_conflicts = case @value_numeric
when 1 then 1
when 2 then 1
else 0
end,
decentralized_conflicts = case @value_numeric
when 1 then 0
when 2 then 1
else 0
end
WHERE pubid = @pubid
if @@error <> 0
BEGIN
GOTO UNDO
END
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'generation_leveling_threshold'
begin
select @value_numeric = convert(int, @value)

UPDATE dbo.sysmergepublications
SET generation_leveling_threshold = @value_numeric
WHERE pubid = @pubid
if @@error <> 0
BEGIN
GOTO UNDO
END
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'automatic_reinitialization_policy'
begin
UPDATE dbo.sysmergepublications
SET automatic_reinitialization_policy = @value
WHERE pubid = @pubid
if @@error <> 0 GOTO UNDO
end

COMMIT TRAN
return 0

UNDO:
if @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION MSchange_mergepublication
COMMIT TRANSACTION
end

sp_MSaddanonymousreplica (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_MSaddanonymousreplica(nvarchar @publication
, nvarchar @publisher
, nvarchar @publisherDB
, int @anonymous
, int @sync_type)

MetaData:

 create procedure sys.sp_MSaddanonymousreplica  
(@publication sysname,
@publisher sysname,
@publisherDB sysname,
@anonymous int,
@sync_type int = 1, -- sync type is automatic by default --
@preexists bit=0 OUTPUT
)
as
set nocount on
declare @retcode int
declare @subscription_type nvarchar(15)
declare @sync_typestr nvarchar(15)
declare @sub_typeid int
declare @pubid uniqueidentifier

--
-- need more than PAL to do this since this is run at the subscriber site
--
exec @retcode = sp_MSreplcheck_subscribe
if @@error<>0 or @retcode<>0
begin
RAISERROR (14126, 11, -1)
return 1
end

select @preexists = 0

select @subscription_type = 'default'

if @anonymous = 1
begin
select @subscription_type = 'anonymous'
select @sub_typeid = 2 -- subscription type value for anonymous
end
else if @anonymous = 2
begin
select @subscription_type = 'lightweight'
select @sub_typeid = 3 -- subscription type value for lightweight
end
else
begin
select @subscription_type = 'local'
select @sub_typeid = 1 -- subscription type value for well known pull
end

if @sync_type = 1 select @sync_typestr = 'automatic'
else select @sync_typestr = 'none'

-- this change is made so that we will try to add pull/anonymous subscriptions, even if
-- there is a already a subscription for that publication, however the subscription type
-- does not match. In this way we can prevent users from using incorrect subscription type
-- through command line or merge control.
if object_id('sysmergepublications') is not NULL
begin
select @pubid=pubid from dbo.sysmergepublications
where name=@publication and UPPER(publisher)=UPPER(@publisher) and publisher_db = @publisherDB

if @pubid is not NULL
begin
-- Check if theres is a subscriptions entry that matches the pubid and current subscription with the right type --
if exists (select * from dbo.sysmergesubscriptions where pubid =@pubid and UPPER(subscriber_server) = @@SERVERNAME and db_name = DB_NAME() and subscription_type=@sub_typeid)
begin
select @preexists = 1
return (0) -- replica exists.
end
--
-- Check if theres is a subscriptions entry that matches the pubid and current subscription -
-- If there is a match and the types are not the same, then return appropriate error.
--
else if exists (select * from dbo.sysmergesubscriptions where pubid =@pubid and UPPER(subscriber_server) = @@SERVERNAME and db_name = DB_NAME())
begin
RAISERROR (21500, 16, -1, @publication)
-- replica exists with wrong subscription type
select @preexists = 1

return (1)
end
end
end

-- Call this SP to add this replica
exec @retcode = sys.sp_addmergepullsubscription
@publication = @publication,
@publisher = @publisher,
@publisher_db=@publisherDB,
@subscriber_type =@subscription_type,
@sync_type = @sync_typestr
IF @retcode<>0 or @@ERROR<>0 return (1)
return (0)

sp_MSaddpeerlsn (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_MSaddpeerlsn(nvarchar @originator
, nvarchar @originator_db
, nvarchar @originator_publication
, int @originator_publication_id
, int @originator_db_version
, varbinary @originator_lsn
, int @originator_version
, int @originator_id)

MetaData:

 create procedure sys.sp_MSaddpeerlsn  
(
@originator sysname,
@originator_db sysname,
@originator_publication sysname,
@originator_publication_id int,
@originator_db_version int,
@originator_lsn varbinary(10),
@originator_version int = NULL,
@originator_id int = NULL
)
as
begin
declare @retcode bit

-- security check for subscriber
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
begin
return 1
end

begin transaction tran_sp_MSaddpeerlsn
save transaction tran_sp_MSaddpeerlsn

-- if the LSN entry already exists then we will just perform a NO-OP
if exists (select *
from MSpeer_lsns with (holdlock, updlock)
where originator = UPPER(@originator)
and originator_db = @originator_db
and originator_publication = @originator_publication
and originator_publication_id = @originator_publication_id
and originator_db_version = @originator_db_version)
begin
commit transaction tran_sp_MSaddpeerlsn
return 0
end

if @originator_id = 0
select @originator_id = NULL

insert into MSpeer_lsns
(
originator,
originator_db,
originator_publication,
originator_publication_id,
originator_db_version,
originator_lsn,
originator_version,
originator_id
)
values
(
UPPER(@originator),
@originator_db,
@originator_publication,
@originator_publication_id,
@originator_db_version,
@originator_lsn,
@originator_version,
@originator_id
)
if @@error <> 0
begin
-- The procedure sys.sp_MSaddpeerlsn failed to INSERT into the resource MSpeer_lsns. Server error = @@error.
raiserror (21499, 16, -1, 'sys.sp_MSaddpeerlsn', 'INSERT into', 'MSpeer_lsns.', @@error)
goto FAILURE
end


if object_id(N'dbo.MSpeer_originatorid_history', N'U') is not NULL
begin
if @originator_id is not NULL and not exists(select * from dbo.MSpeer_originatorid_history where originator_publication = @originator_publication
and originator_id = @originator_id
and UPPER(originator_node) = UPPER(@originator)
and originator_db = @originator_db
and originator_db_version = @originator_db_version)

begin
insert dbo.MSpeer_originatorid_history
(originator_publication, originator_id, originator_node, originator_db, originator_db_version, originator_version)
values(@originator_publication, @originator_id, UPPER(@originator), @originator_db, @originator_db_version, @originator_version)

if @@error <> 0
begin
-- The procedure sys.sp_MSaddpeerlsn failed to INSERT into the resource MSpeer_originatorid_history. Server error = @@error.
raiserror (21499, 16, -1, 'sys.sp_MSaddpeerlsn', 'INSERT into', 'MSpeer_originatorid_history.', @@error)
goto FAILURE
end
end
end

commit transaction tran_sp_MSaddpeerlsn

return 0

FAILURE:

rollback transaction tran_sp_MSaddpeerlsn
commit transaction tran_sp_MSaddpeerlsn

return 1
end

sp_MSbrowsesnapshotfolder (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_MSbrowsesnapshotfolder(nvarchar @publisher
, nvarchar @publisher_db
, int @article_id
, nvarchar @subscriber
, nvarchar @subscriber_db)

MetaData:

 create procedure sys.sp_MSbrowsesnapshotfolder   
(
@publisher sysname,
@publisher_db sysname,
@article_id int,
@subscriber sysname = NULL,
@subscriber_db sysname = NULL
)
AS
begin
SET NOCOUNT ON

DECLARE @alt_directory_type INT
DECLARE @directory_type INT
DECLARE @publisher_id INT
DECLARE @publisher_database_id INT
DECLARE @subscriber_id INT
DECLARE @snapshot_bit INT
DECLARE @snapshot_mask INT
DECLARE @xact_seqno VARBINARY(16)
DECLARE @sync_init INT
DECLARE @sync_done INT

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end

SELECT @alt_directory_type = 25
SELECT @directory_type = 7
SELECT @subscriber_id = NULL
SELECT @snapshot_bit = 0x80000000
SELECT @snapshot_mask = ~@snapshot_bit
SELECT @xact_seqno = NULL
SELECT @sync_init = 37
SELECT @sync_done = 38

SELECT @publisher_id = srvid
FROM master.dbo.sysservers
WHERE UPPER(@publisher) = UPPER(srvname)

SELECT @publisher_database_id = publisher_database_id
FROM dbo.MSsubscriptions
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
AND article_id = @article_id

-- @subscriber is null implies @subscriber_db is null also because
-- this sp can only be called by sp_browsesnapshotfolder
IF @subscriber IS NULL
BEGIN
SELECT @xact_seqno = MAX(xact_seqno)
FROM MSrepl_commands
WHERE publisher_database_id = @publisher_database_id
AND article_id = @article_id
AND (type & @snapshot_bit) <> 0
AND (type & @snapshot_mask) <> @sync_init
AND (type & @snapshot_mask) <> @sync_done
END
ELSE
BEGIN
-- Both @subscriber and @subscriber_db are non-null
SELECT @subscriber_id = srvid
FROM master.dbo.sysservers
WHERE UPPER(@subscriber) = UPPER(srvname)

IF @subscriber_id IS NULL
BEGIN
RAISERROR(21150, 16, -1)
RETURN 1
END

SELECT @xact_seqno = subscription_seqno
FROM dbo.MSsubscriptions
WHERE publisher_database_id = @publisher_database_id
AND publisher_id = @publisher_id
AND article_id = @article_id
AND subscriber_id = @subscriber_id
AND subscriber_db = @subscriber_db

IF @xact_seqno IS NULL
BEGIN
RAISERROR(14055, 16, -1)
RETURN 1
END
END

SELECT 'snapshot_folder' = CONVERT(NVARCHAR(255), command)
FROM MSrepl_commands
WHERE publisher_database_id = @publisher_database_id
AND (type & @snapshot_bit) <> 0
AND xact_seqno = @xact_seqno
AND (((type & ~@snapshot_bit) = @alt_directory_type) OR
((type & ~@snapshot_bit) = @directory_type))
ORDER BY command_id ASC
IF @@ERROR <> 0
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
end

sp_MSadd_subscriber_schedule (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_MSadd_subscriber_schedule(nvarchar @publisher
, nvarchar @subscriber
, smallint @agent_type
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_subscriber_schedule  
(
@publisher sysname,
@subscriber sysname,
@agent_type smallint = 0, -- 0 for distribution agent, 1 for merge agent
@frequency_type int = 4,
@frequency_interval int = 1,
@frequency_relative_interval int = 1,
@frequency_recurrence_factor int = 0,
@frequency_subday int = 4,
@frequency_subday_interval int = 5,
@active_start_time_of_day int = 0,
@active_end_time_of_day int = 235959,
@active_start_date int = 0,
@active_end_date int = 99991231
)
AS
begin
set nocount on
declare @retcode int

--
-- security check
-- only sysadmin can execute this
--
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
begin
raiserror(21089, 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_MSadd_subscriber_schedule', 'distribution')
return (1)
end

-- Add the subscriber to dbo.sysservers as a RPC server, if it does not
-- already exist.
--
if not exists (select * from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber))
begin
DECLARE @upper_subscriber sysname

SELECT @upper_subscriber = UPPER(@subscriber collate database_default)

exec @retcode = dbo.sp_addserver @upper_subscriber
if @retcode <> 0
return 1
end

-- only insert if it doesn't exist --
if not exists (select *
from MSsubscriber_schedule
where UPPER(publisher) = UPPER(@publisher)
and UPPER(subscriber) = UPPER(@subscriber)
and agent_type = @agent_type)
begin
insert MSsubscriber_schedule (publisher, subscriber, agent_type,
frequency_type, frequency_interval, frequency_relative_interval,
frequency_recurrence_factor, frequency_subday, frequency_subday_interval,
active_start_time_of_day, active_end_time_of_day, active_start_date,
active_end_date)

values (@publisher, @subscriber, @agent_type,
@frequency_type, @frequency_interval, @frequency_relative_interval,
@frequency_recurrence_factor, @frequency_subday, @frequency_subday_interval,
@active_start_time_of_day, @active_end_time_of_day, @active_start_date,
@active_end_date)
if @@error <> 0
return 1
end
end

sp_MSchange_subscription_dts_info (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_subscription_dts_info(varbinary @job_id
, nvarchar @dts_package_name
, nvarchar @dts_package_password
, int @dts_package_location
, bit @change_password)

MetaData:

 CREATE PROCEDURE sys.sp_MSchange_subscription_dts_info   
(
@job_id varbinary(16),
@dts_package_name sysname,
@dts_package_password nvarchar(524),
@dts_package_location int,
@change_password bit
)
AS
begin
--
-- Declarations.
--
declare @subscriber sysname
declare @publisher sysname
declare @subscriber_id smallint
declare @publisher_id smallint

--
-- 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_subscription_dts_info', 'distribution')
return (1)
end
--
-- Initializations.
--
SET NOCOUNT ON

select @subscriber_id = subscriber_id, @publisher_id = publisher_id
from MSdistribution_agents where
job_id = @job_id

select @subscriber = srvname from master.dbo.sysservers where srvid = @subscriber_id
select @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id

declare @oledb_subscriber tinyint
select @oledb_subscriber = 3

-- Only SQL Server and OLEDB subscriber support dts
if not exists (select * from MSsubscriber_info where
UPPER(publisher) = UPPER(@publisher) and
UPPER(subscriber) = UPPER(@subscriber) and
(type = 0 or type = @oledb_subscriber))
begin
raiserror(21170, 16, -1)
return 1
end


update MSdistribution_agents set
dts_package_name = case
when @dts_package_name is null then dts_package_name
when @dts_package_name = N'' then null
else @dts_package_name
end,
dts_package_password = case @change_password
when 0 then dts_package_password
else @dts_package_password
end,
dts_package_location = case
when @dts_package_location is null then dts_package_location
else @dts_package_location
end
where
job_id = @job_id

RETURN (0)
end

sp_MSchange_retention (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_retention(uniqueidentifier @pubid
, nvarchar @value)

MetaData:

   
create procedure sys.sp_MSchange_retention (@pubid uniqueidentifier, @value nvarchar(255))
as
declare @re_pubid uniqueidentifier
declare @artid uniqueidentifier
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @schematext nvarchar(2000)
declare @retcode int
declare @SCHEMA_TYPE_RETENTIONCHANGE int

-- Security check
exec @retcode = dbo.sp_MSreplcheck_subscribe
if @retcode <> 0 or @@error <> 0
return 1

set @SCHEMA_TYPE_RETENTIONCHANGE= 9

begin tran
save tran change_retention

update dbo.sysmergepublications set retention = convert(int, @value) where pubid = @pubid
if @@ERROR<>0
goto UNDO

-- Declare a cursor that iterates over all publications which originate at this node.
declare #change_retention CURSOR LOCAL FAST_FORWARD for
select pubid from dbo.sysmergearticles
where pubid<>@pubid and
nickname in (select nickname from dbo.sysmergearticles where pubid=@pubid) and
pubid in (select pubid from dbo.sysmergepublications
where upper(publisher) collate database_default = upper(publishingservername()) collate database_default and
publisher_db = db_name())

open #change_retention
fetch #change_retention into @re_pubid
while (@@fetch_status <> -1)
BEGIN
update dbo.sysmergepublications set retention = convert(int, @value) where pubid = @re_pubid
if @@ERROR<>0
goto UNDO
set @schematype= @SCHEMA_TYPE_RETENTIONCHANGE
set @artid = null
select @schematext = 'exec dbo.sp_MSchange_retention '+ '''' + convert(nchar(36),@re_pubid) + '''' + ',' + '''' + @value + ''''
select @schemaversion = schemaversion from dbo.sysmergeschemachange
if (@schemaversion is NULL)
set @schemaversion = 1
else
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
set @schemaguid = newid()
exec @retcode=sys.sp_MSinsertschemachange @re_pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0
goto UNDO
fetch #change_retention into @re_pubid
END
close #change_retention
deallocate #change_retention

COMMIT TRAN
return (0)
UNDO:
ROLLBACK tran change_retention
COMMIT TRAN
return(1)

sp_MSchange_retention_period_unit (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_retention_period_unit(uniqueidentifier @pubid
, tinyint @value)

MetaData:

 create procedure sys.sp_MSchange_retention_period_unit (@pubid uniqueidentifier, @value tinyint)  
as
declare @re_pubid uniqueidentifier
declare @artid uniqueidentifier
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @schematext nvarchar(2000)
declare @retcode int
declare @SCHEMA_TYPE_RETENTIONUNITCHANGE int

-- Security check
exec @retcode = dbo.sp_MSreplcheck_subscribe
if @retcode <> 0 or @@error <> 0
return 1

set @SCHEMA_TYPE_RETENTIONUNITCHANGE= 29

begin tran
save tran change_retention_pu

update dbo.sysmergepublications set retention_period_unit = @value where pubid = @pubid
if @@ERROR<>0
goto UNDO

-- Declare a cursor that iterates over all publications which originate at this node.
declare #change_retention_period_unit CURSOR LOCAL FAST_FORWARD for
select pubid from dbo.sysmergearticles
where pubid<>@pubid and
nickname in (select nickname from dbo.sysmergearticles where pubid=@pubid) and
pubid in (select pubid from dbo.sysmergepublications
where upper(publisher) collate database_default = upper(publishingservername()) collate database_default and
publisher_db = db_name())

open #change_retention_period_unit
fetch #change_retention_period_unit into @re_pubid
while (@@fetch_status <> -1)
BEGIN
update dbo.sysmergepublications set retention_period_unit = @value where pubid = @re_pubid
if @@ERROR<>0
goto UNDO
set @schematype= @SCHEMA_TYPE_RETENTIONUNITCHANGE
set @artid = null
select @schematext = 'exec dbo.sp_MSchange_retention_period_unit '+ '''' + convert(nchar(36),@re_pubid) + '''' + ',' + convert(nvarchar, @value)
select @schemaversion = schemaversion from dbo.sysmergeschemachange
if (@schemaversion is NULL)
set @schemaversion = 1
else
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
set @schemaguid = newid()
exec @retcode=sys.sp_MSinsertschemachange @re_pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0
goto UNDO
fetch #change_retention_period_unit into @re_pubid
END
close #change_retention_period_unit
deallocate #change_retention_period_unit

COMMIT TRAN
return (0)
UNDO:
ROLLBACK tran change_retention_pu
COMMIT TRAN
return(1)

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

Total Pageviews