May 29, 2012

sp_MSset_snapshot_xact_seqno (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_MSset_snapshot_xact_seqno(int @publisher_id
, nvarchar @publisher_db
, int @article_id
, varbinary @xact_seqno
, bit @reset
, nvarchar @publication
, varbinary @publisher_seqno
, varbinary @ss_cplt_seqno)

MetaData:

 CREATE PROCEDURE sys.sp_MSset_snapshot_xact_seqno  
(
@publisher_id int,
@publisher_db sysname,
@article_id int,
@xact_seqno varbinary(16),
@reset bit = 0, -- @reset = 1 is used for Scheduled Snapshot publications by snapshot --
@publication sysname = NULL,
@publisher_seqno varbinary(16) = 0x00,
@ss_cplt_seqno varbinary(16) = NULL
--
-- Required for 6x publishers!
--
)
AS
begin
DECLARE @virtual smallint -- const: virtual subscriber id --
DECLARE @virtual_anonymous smallint -- const: virtual anonymous subscriber id --
DECLARE @old_xact_seqno varbinary(16)
DECLARE @old_publisher_seqno varbinary(16)
DECLARE @subscribed tinyint
DECLARE @automatic tinyint
DECLARE @old_snapshot_seqno_flag bit
DECLARE @publication_id int
DECLARE @initiated tinyint
DECLARE @publisher_database_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 @publisher_database_id = id
FROM dbo.MSpublisher_databases
WHERE publisher_db = @publisher_db
AND publisher_id = @publisher_id

SELECT @virtual = -1
SELECT @virtual_anonymous = -2
SELECT @subscribed = 1
SELECT @automatic = 1
SELECT @initiated = 3

if @ss_cplt_seqno is null
begin
select @ss_cplt_seqno = @xact_seqno;
end

-- 6.5 only!!! @publication is not null only if the publisher is 6.5 sever!
-- Set the publication_id and sync_type in dbo.MSsubscriptions.
-- It will be used in sp_MSupdate_subscriptions
IF @publication IS NOT NULL
BEGIN
-- Get the publication id
SELECT @publication_id = publication_id FROM dbo.MSpublications
WHERE publisher_id = @publisher_id AND
publisher_db = @publisher_db AND
publication = @publication

-- Set the pubid and the sync_type
-- Avoid update rows with no change to reduce update locks.

UPDATE dbo.MSsubscriptions SET publication_id = @publication_id
FROM dbo.MSsubscriptions with (index(iMSsubscriptions))
WHERE publisher_id = @publisher_id AND
publisher_database_id = @publisher_database_id AND
article_id = @article_id and
status = @subscribed and
publication_id <> @publication_id

-- Have to do this to avoid no sync subs from 6.5 publisher being
-- updated.
UPDATE dbo.MSsubscriptions SET sync_type = @automatic
FROM dbo.MSsubscriptions with (index(iMSsubscriptions))
WHERE publisher_id = @publisher_id AND
publisher_database_id = @publisher_database_id AND
article_id = @article_id and
status = @subscribed and
sync_type <> @automatic
END

begin tran
save TRANSACTION MSset_snapshot_xact_seqno

--
-- Set snapshot_xact_seqno for all new subscriptions,
-- plus the virtual subscription or all subscriptions if @reset = 1
-- Note virtual anonymous subscription will not be set
-- (2 virtual subscriptions of anonymous publication will be activated
-- immediately without snapshot
--
-- @reset = 1 is used for Scheduled Snapshot publications by snapshot
--
UPDATE dbo.MSsubscriptions SET subscription_seqno = @xact_seqno,
publisher_seqno = @publisher_seqno,
snapshot_seqno_flag = 1,
subscription_time = getdate(),
ss_cplt_seqno = @ss_cplt_seqno
FROM dbo.MSsubscriptions with (index(iMSsubscriptions))
WHERE
dbo.MSsubscriptions.publisher_id = @publisher_id and
dbo.MSsubscriptions.publisher_database_id = @publisher_database_id and
dbo.MSsubscriptions.article_id = @article_id and
-- virtual subscriptions are automatic sync type --
dbo.MSsubscriptions.sync_type = @automatic and
(MSsubscriptions.status in(@subscribed,@initiated) or
dbo.MSsubscriptions.subscriber_id = @virtual or
-- Set for virtual anonymous account if snapshot_seqno_flag
-- is 0.
-- The virtual anonymous account is activated immediately at subscription
-- time for no init option for anonymous agent.
(MSsubscriptions.subscriber_id = @virtual_anonymous and
(MSsubscriptions.snapshot_seqno_flag = 0 or
dbo.MSsubscriptions.status in(@subscribed,@initiated))) or
@reset = 1)

IF @@ERROR <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION MSset_snapshot_xact_seqno
commit tran
end
RETURN (1)
END

COMMIT TRANSACTION
end

No comments:

Post a Comment

Total Pageviews