May 29, 2012

sp_MSset_sub_guid (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_MSset_sub_guid(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @subscription_type
, binary @subscription_guid
, nvarchar @queue_id
, nvarchar @queue_server)


 create procedure sys.sp_MSset_sub_guid (  
@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,
@subscription_guid binary(16),
@queue_id sysname,
@queue_server sysname = NULL
set nocount on
declare @retcode int
,@independent_agent bit
,@subqueue_id sysname

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

-- For non independent agent publications
if @publication is null or @publication = ''
select @publication = 'ALL'
,@independent_agent = 0
select @independent_agent = 1

if @queue_id = N''
select @queue_id = NULL

if @queue_server = N''
select @queue_server = NULL
-- Special processing when we are dealing with pull SQL queue updating subscription
if ((@subscription_type = 1) and substring(@queue_id, 1, 10) = N'mssqlqueue')
-- For SQL Queue case - Check the current queue_id on the table
select @subqueue_id = queue_id
from dbo.MSsubscription_agents
where UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and subscription_type = @subscription_type
if (@subqueue_id is not NULL and substring(@subqueue_id, 1, 10) = N'mssqlqueue')
-- Use the local Queue ID on subscriber as this contains the queue version.
-- We are attaching an initialized subscription and do not want to change the
-- queue version stored in queue_id for SQL Queues. The value passed by Distribution
-- Agent is from the distributor metadata and does not contain the queue version information.
-- This is specific to SQL Queues only.
select @queue_id = @subqueue_id
-- Update metadata in MSsubscription_agents
update MSsubscription_agents set
queue_id = @queue_id
,queue_server = @queue_server
where UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and subscription_type = @subscription_type

update MSreplication_subscriptions set
[time] = getdate(),
subscription_guid = @subscription_guid
where UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and (@independent_agent = 0 or publication = @publication)
and subscription_type = @subscription_type
and independent_agent = @independent_agent

No comments:

Post a Comment

Total Pageviews