June 4, 2012

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


sys.sp_MSsetupnosyncsubwithlsnatdist_cleanup(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @article
, int @artid
, nvarchar @subscriber
, nvarchar @destination_db
, binary @next_valid_lsn)


-- Name: sp_MSsetupnosyncsubwithlsnatdist_cleanup
-- Description: try to revert the status change of MSsubscription during the execution
-- of MSsubscriptions for the article, and to remove the rows added for the subscription
-- in table MSnosyncsubsetup. The table itself will not be removed just in case it will be
-- used later soon. The cleanup process is in best-effor way, which does not run
-- under a transaction.
-- Security: Procedural security check is performed inside this procedure to
-- ensure that the caller is a member of sysadmin. Execute
-- permission of this procedure is granted to public. This procedure
-- is invoked via RPC
create procedure sys.sp_MSsetupnosyncsubwithlsnatdist_cleanup
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@article sysname,
@artid int,
@subscriber sysname,
@destination_db sysname,
@next_valid_lsn binary(10)
set nocount on
declare @publisherid int,
@subscriberid int,
@publisher_database_id int,
@publication_id int

-- Security check
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
raiserror(21089, 16, -1)
return 1

-- Obtain Publisher's server id
select @publisherid = srvid
from master.dbo.sysservers
where upper(srvname) = upper(@publisher) collate database_default
if @@error<>0 goto Failure

-- Obtain Subscriber's server id
select @subscriberid = srvid
from master.dbo.sysservers
where upper(srvname) = upper(@subscriber) collate database_default
if @@error<>0 goto Failure

-- Find out what the publisher database id is
select @publisher_database_id = id
from dbo.MSpublisher_databases
where publisher_db = @publisher_db
and publisher_id = @publisherid
if @@error<>0 goto Failure

-- Obtain the publication id
select @publication_id = publication_id
from dbo.MSpublications
where publisher_id = @publisherid
and publisher_db = @publisher_db
and publication = @publication
if @@error<>0 goto Failure

-- Deactivate subscriptions in dbo.MSsubscriptions
update dbo.MSsubscriptions
set status = 0 -- inactive status
where publisher_database_id = @publisher_database_id
and publisher_id = @publisherid
and publisher_db = @publisher_db
and publication_id = @publication_id
and subscriber_id = @subscriberid
and subscriber_db = @destination_db
and (@article = N'all' or article_id = @artid)
if @@error<>0 goto Failure

-- Delete the rows from MSnosyncsubsetup table regarding
-- the specified nonsync subscription; we do not remove
-- the table itself since it might be useful for future
-- non-sync subscription.
if object_id(N'dbo.MSnosyncsubsetup', 'U') is NOT NULL
delete dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @publication_id
and artid = @artid
and next_valid_lsn = @next_valid_lsn
if @@error<>0 goto Failure

return 0

return 1

No comments:

Post a Comment

Total Pageviews