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_MScreateglobalreplica(uniqueidentifier @pubid, uniqueidentifier @subid
, uniqueidentifier @replicastate
, nvarchar @replica_server
, nvarchar @replica_db
, real @replica_priority
, tinyint @subscriber_type
, int @subscription_type
, int @datasource_type
, nvarchar @datasource_path
, varbinary @replnick
, int @status
, tinyint @sync_type
, nvarchar @publication
, nvarchar @distributor
, int @replica_version
, int @compatlevel)
MetaData:
create procedure sys.sp_MScreateglobalreplica( @pubid uniqueidentifier = NULL, -- Publication ID -- @subid uniqueidentifier, -- Replica ID -- @replicastate uniqueidentifier, @replica_server sysname, -- Replica server -- @replica_db sysname, -- Replica database -- @replica_priority real, -- Replica priority -- @subscriber_type tinyint = 0, -- Replica's subscriber type - global, hub -- @subscription_type int = 0, -- Replica's subscription type - push or pull -- @datasource_type int = 0, @datasource_path nvarchar(255) = NULL, @replnick varbinary(6), -- Replica nickname -- @status int, -- Replica status -- @sync_type tinyint = 2, -- Replica sync type 1 = no sync, 2 = automatic -- @publication sysname = NULL, -- Replica publication -- @distributor sysname = NULL, -- Replica's distributor -- @replica_version int = 60, -- 60=shiloh sp3 or lower, 90 = yukon -- @compatlevel int = 80 -- backward compatibility level, default=Sphinx -- ) AS SET NOCOUNT ON DECLARE @pubnickname int declare @retcode int declare @backward_comp_level int -- Check to see if current publication has permission exec @retcode = sys.sp_MSrepl_PAL_rolecheck if (@retcode <> 0) or (@@error <> 0) return 1 -- NOTE select @replica_db = RTRIM(@replica_db) select @backward_comp_level= sys.fn_MSgetmaxbackcompatlevel () -- Replnick in 80 was int and in 90 is binary(6). The proc does a implicit conversion from int to varbinary(6) but we still need to reverse the binary value to get a corresponding 90 replnick -- if @compatlevel < 90 set @replnick= cast(reverse(substring(@replnick,1,4)) as binary(4))+ 0x0000 BEGIN TRAN MScreateglobalreplica -- -- Populate the local copy of dbo.sysmergesubscriptions -- if exists (select * from dbo.sysmergesubscriptions where subid = @subid) begin update dbo.sysmergesubscriptions SET datasource_type = @datasource_type, db_name = @replica_db, status = @status, subscriber_type = @subscriber_type, subscription_type = @subscription_type, priority = @replica_priority, sync_type = @sync_type, subscriber_server = @replica_server, replica_version = @replica_version where subid = @subid and pubid = @pubid IF @@ERROR <> 0 goto FAILURE -- if the replica is a publications loop back global subscription -- add an entry to sysmergepublications if (@subid = @pubid) begin if exists (select * from dbo.sysmergepublications where pubid = @pubid) begin update dbo.sysmergepublications set distributor = @distributor where pubid = @pubid IF @@ERROR <> 0 goto FAILURE end else begin -- added this to avoid problem of having too low a backward_comp_level insert dbo.sysmergepublications(publisher, publisher_db, pubid, name, distributor, backward_comp_level) values (@replica_server, @replica_db, @pubid, @publication, @distributor, @backward_comp_level) IF @@ERROR <> 0 goto FAILURE end end end else begin -- -- If attempting to tell the current replica about another replica whose pubid IS NULL -- ignore the insert because current replica has more current info. -- if exists (select * from dbo.sysmergesubscriptions where subid = @subid and @pubid IS NULL) goto SUCCESS IF EXISTS (SELECT * FROM dbo.sysmergesubscriptions WHERE UPPER(subscriber_server) collate database_default = UPPER(@replica_server) collate database_default AND db_name = @replica_db and pubid = @pubid) goto SUCCESS -- if this is an attempt to tell this replica about itself ignore it since this replica will always have the most -- recent information about itself. if (@subid=@pubid) and exists (select 1 from dbo.sysmergepublications where UPPER(publisher) = UPPER(@replica_server) and publisher_db = @replica_db and pubid <> @pubid) and UPPER(@replica_server) = UPPER(publishingservername()) and @replica_db = db_name() goto SUCCESS -- now check if this is a publication's loopback global replica entry. If so we need to do something different. if (@subid = @pubid) begin -- There is a possibility that the subscriber has a subscription to this publication and that subscription -- has not gone through a first sync yet and was added using addmergepullsubscription. In that case even though -- the replica entry is there it would have the wrong pubid. By creating a global replica here we would be duplicating -- the sysmergepublications and sysmergesubscriptions entries for the publications loop back replica. Hence just -- do an update here instead of an insert. declare @pubid_local uniqueidentifier -- now check if there is a replica entry to this publication with a different pubid select @pubid_local = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@replica_server) and publisher_db=@replica_db if @pubid_local is not NULL begin if exists (select * from dbo.sysmergesubscriptions where pubid = @pubid_local and subid = pubid) begin update dbo.sysmergesubscriptions SET pubid = @pubid, subid = @subid, datasource_type = @datasource_type, db_name = @replica_db, status = @status, subscriber_type = @subscriber_type, subscription_type = @subscription_type, priority = @replica_priority, sync_type = @sync_type, subscriber_server = @replica_server, replica_version = @replica_version where subid = pubid and pubid = @pubid_local IF @@ERROR <> 0 goto FAILURE end else begin -- this case happens when create global replica is called after addinitial subscription insert dbo.sysmergesubscriptions(subid, replicastate, datasource_type, db_name, pubid, status, subscriber_type, subscription_type, priority, sync_type, subscriber_server, replnickname, replica_version) values (@subid, @replicastate, @datasource_type, @replica_db, @pubid, @status, @subscriber_type, @subscription_type, @replica_priority, @sync_type, @replica_server, @replnick, @replica_version) IF @@ERROR <> 0 goto FAILURE end update dbo.sysmergepublications set pubid = @pubid, distributor = @distributor where pubid = @pubid_local IF @@ERROR <> 0 goto FAILURE -- In some instances, there can be entries from deleted publications in -- sysmergesubscriptions that have the same db_name and subscriber_server -- as a current publication. -- The below update needs to be restricted to not reassign the pubid on -- old entries where a new entry already exists as this will violate the unique -- constraint across pubid, subscriber_server, and db_name. -- See VSTS 82551 and 161794. update dbo.sysmergesubscriptions set pubid = @pubid where pubid = @pubid_local and subid not in (select sms1.subid from dbo.sysmergesubscriptions sms1 join dbo.sysmergesubscriptions sms2 on sms1.subscriber_server = sms2.subscriber_server and sms1.db_name = sms2.db_name where sms1.pubid = @pubid_local and sms2.pubid = @pubid) IF @@ERROR <> 0 goto FAILURE update dbo.sysmergearticles set pubid = @pubid where pubid = @pubid_local IF @@ERROR <> 0 goto FAILURE end else begin -- if we get here it means that we do not already know about this replica and so do the inserts insert dbo.sysmergesubscriptions(subid, replicastate, datasource_type, db_name, pubid, status, subscriber_type, subscription_type, priority, sync_type, subscriber_server, replnickname, replica_version) values (@subid, @replicastate, @datasource_type, @replica_db, @pubid, @status, @subscriber_type, @subscription_type, @replica_priority, @sync_type, @replica_server, @replnick, @replica_version) IF @@ERROR <> 0 goto FAILURE insert dbo.sysmergepublications(publisher, publisher_db, pubid, name, distributor, backward_comp_level) values (@replica_server, @replica_db, @pubid, @publication, @distributor, @backward_comp_level) IF @@ERROR <> 0 goto FAILURE end end else begin insert dbo.sysmergesubscriptions(subid, replicastate, datasource_type, db_name, pubid, status, subscriber_type, subscription_type, priority, sync_type, subscriber_server, replnickname, replica_version) values (@subid, @replicastate, @datasource_type, @replica_db, @pubid, @status, @subscriber_type, @subscription_type, @replica_priority, @sync_type, @replica_server, @replnick, @replica_version) IF @@ERROR <> 0 goto FAILURE end end -- 9.0 publications use 6 byte replnicks. If we did sp_addmergepullsubscription for such -- a publication, we currently have a 4 byte nickname only for our own replica. -- If we do not already have a used nickname for the replica, we now change -- that nickname to 6 bytes. select @backward_comp_level= backward_comp_level from dbo.sysmergepublications where pubid = @pubid if @backward_comp_level >= 90 begin declare @newsubnick binary(6) declare @srcguid uniqueidentifier set @srcguid= newid() exec sys.sp_MSgenreplnickname @srcguid= @srcguid, @replnick= @newsubnick output, @compatlevel= @backward_comp_level update sub set sub.replnickname= @newsubnick from dbo.sysmergesubscriptions as sub where -- only update if not already a real 6 byte nickname substring(sub.replnickname, 5, 2) = 0x0000 and -- only update that specific nickname sub.pubid = @pubid and sub.subid <> @pubid and sub.db_name = db_name() and -- only update if the nickname not already exists for another subscription 2 > (select count(*) from dbo.sysmergesubscriptions where replnickname = sub.replnickname) end SUCCESS: COMMIT TRAN RETURN 0 FAILURE: -- UNDONE : This code is specific to 6.X nested transaction semantics -- if @@TRANCOUNT = 1 ROLLBACK TRANSACTION MScreateglobalreplica else COMMIT TRANSACTION RAISERROR (14057, 16, -1) RETURN 1
No comments:
Post a Comment