May 21, 2012

sp_MSinit_subscription_agent (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_MSinit_subscription_agent(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @subscription_type)

MetaData:

 create procedure sys.sp_MSinit_subscription_agent  
@publisher sysname, -- publishing server name
@publisher_db sysname, -- publishing database name. If NULL then same as current db
@publication sysname, -- publication name,
@subscription_type int
AS
set nocount on
declare @retcode int
declare @login_time datetime

-- Security Check
EXEC @retcode = sys.sp_MSreplcheck_subscribe
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)

-- For non independent agent publications
if @publication is null or @publication = ''
set @publication = 'ALL'

select @login_time = login_time from sys.sysprocesses where spid = @@spid

if not exists (select * from MSsubscription_agents where
UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and subscription_type = @subscription_type)
begin
INSERT INTO MSsubscription_agents
(publisher, publisher_db, publication, subscription_type,
queue_id, update_mode, failover_mode, spid, login_time )
values (UPPER(@publisher), @publisher_db, @publication, @subscription_type,
null, 1, 0, @@spid, @login_time)
end
else
begin
-- It is possible that 2 instance of the distribution agent do this update at
-- the same time. One will fail later at the instance check at the distributor
-- side. We no longer use the spid and login_time column anywhere else.
update MSsubscription_agents set
spid = @@spid,
login_time = @login_time
where UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and subscription_type = @subscription_type
end

No comments:

Post a Comment

Total Pageviews