May 21, 2012

sp_MSinvalidate_snapshot (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_MSinvalidate_snapshot(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

 CREATE PROCEDURE sys.sp_MSinvalidate_snapshot  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname
)
as
begin
set nocount on
declare @publisher_id smallint
declare @automatic tinyint
declare @virtual smallint
declare @retcode int
declare @active tinyint
, @initiated tinyint
, @subscribed tinyint
declare @publication_id int

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

select @automatic = 1
select @virtual = - 1
select @active = 2
, @initiated = 3
, @subscribed = 1

-- 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

-- Make sure publication exists
select @publication_id = publication_id
from dbo.MSpublications where publication = @publication and
publisher_id = @publisher_id and publisher_db = @publisher_db

-- Set the virtual subscription status to be 'subscribed' so that
-- new subscription will wait for the next snapshot.
update dbo.MSsubscriptions set status = @subscribed where
publication_id = @publication_id and
sync_type = @automatic and
status in (@active, @initiated) and
subscriber_id = @virtual

return (0)
end

No comments:

Post a Comment

Total Pageviews