create proc sys.sp_MScheck_subscription_count_internal
(
@mode tinyint = 1 -- 0 = tran, 1 = merge
,@publisher sysname
,@publisher_engine_edition int=null
,@about_to_insert_new_subscription bit
)
as
begin
set nocount on
declare @publisher_id int
,@subscription_count int
,@count2 int
,@max_allowed int
,@modestr sysname
if (@mode not in (0,1))
return 1
select @subscription_count = 0
,@max_allowed = case when (@mode = 0) then 5 else 25 end
,@modestr = case when (@mode = 0) then N'Transactional' else N'Merge' end
,@publisher_id = srvid
from master.dbo.sysservers where UPPER(srvname) = UPPER(@publisher)
if @publisher_id is NULL
return 0
-- Get default publisher edition if not supplied
if @publisher_engine_edition is null
begin
select top 1 @publisher_engine_edition = publisher_engine_edition
from dbo.MSpublisher_databases
where publisher_id = @publisher_id
and publisher_engine_edition is not null
-- If we do not have publisher edition
-- no need to do anything
if @publisher_engine_edition is null
return 0
end
-- Check for workgroup edition - the value defined in sys.fn_MSrepl_editionid()
if @publisher_engine_edition = 21
begin
-- if caller calls in transaction, it is good to do repeatable read so that we can ensure
-- that counts can be controlled correctly in concurrent scenarios as well.
if (@mode = 0)
begin
-- check the count of transactional logbased subscriptions
-- Find the active non anonymous subscriptions (this will list separate count for
-- subscriptions which have shared distribution agent
-- separate the common expression from previous statement by semicolon
;
with activesubscriptionperpublication (publication_id, subscriber_id, subscriber_db, subscription_type) as
(
select distinct s.publication_id, s.subscriber_id, s.subscriber_db, s.subscription_type
from dbo.MSsubscriptions as s with (repeatableread)
join dbo.MSpublications as p with (repeatableread)
on s.publication_id = p.publication_id
and s.publisher_id = p.publisher_id
and s.publisher_db = p.publisher_db
where s.subscriber_id >= 0
and s.publisher_id = @publisher_id
and p.publication_type = 0 -- log based publication
)
select @subscription_count = count(*) from activesubscriptionperpublication
-- Also check the count of distribution agents for logbased publications
-- this will list the anonymous subscriptions separately
select @count2 = count(*)
from dbo.MSdistribution_agents as da with (repeatableread)
join dbo.MSpublications as p with (repeatableread)
on da.publication = p.publication
and da.publisher_id = p.publisher_id
and da.publisher_db = p.publisher_db
where da.subscriber_id >= 0
and da.publisher_id = @publisher_id
and p.publication_type = 0 -- log based publication
-- choose the greater count
select @subscription_count = case when (@count2 > @subscription_count)
then @count2 else @subscription_count end
end
else
begin
-- check the count of merge subscriptions
select @subscription_count = count(*) from dbo.MSmerge_agents with (repeatableread)
where publisher_id = @publisher_id
end
-- check if the count exceeds the restriction
if (@about_to_insert_new_subscription = 1 and @subscription_count >= @max_allowed) or
(@about_to_insert_new_subscription = 0 and @subscription_count > @max_allowed)
begin
raiserror (22577, 16, -1, @modestr, @publisher, @max_allowed)
return 1
end
end
-- all done
return 0
end