April 13, 2012

sp_addmergefilter (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_addmergefilter(nvarchar @publication
, nvarchar @article
, nvarchar @filtername
, nvarchar @join_articlename
, nvarchar @join_filterclause
, int @join_unique_key
, bit @force_invalidate_snapshot
, bit @force_reinit_subscription
, tinyint @filter_type)

MetaData:

 create procedure sys.sp_addmergefilter(  
@publication sysname, -- publication name --
@article sysname, -- article name --
@filtername sysname, -- join filter name --
@join_articlename sysname, -- Name of the table being joined to the base table --
@join_filterclause nvarchar(1000), -- filter clause qualifying the join --
@join_unique_key int = 0,
@force_invalidate_snapshot bit = 0, -- Force invalidate existing snapshot --
@force_reinit_subscription bit = 0, -- Force reinit subscription --
@filter_type tinyint = 1 -- 1=join filter only, 2=logical relation only, 3=both --
)AS

set nocount on

declare @snapshot_ready int
declare @db_name sysname
declare @pubid uniqueidentifier
declare @artid uniqueidentifier
declare @art_nickname int
declare @join_nickname int
declare @db sysname
declare @qual_object nvarchar(517)
declare @unqual_object nvarchar(258)
declare @qual_join_object nvarchar(517)
declare @unqual_join_object nvarchar(258)
declare @qual_object_view nvarchar(517)
declare @qual_join_object_view nvarchar(517)

declare @object sysname
declare @vertical int
declare @join_vertical int
declare @join_object nvarchar(140)
declare @object_view nvarchar(140)
declare @join_object_view nvarchar(140)
declare @owner nvarchar(258)
declare @join_object_owner nvarchar(258)
declare @retcode int
declare @join_objid int
declare @objid int
declare @art_status int
declare @joinart_status int
declare @column_list nvarchar(max)
declare @inactive int
declare @new_active int
declare @new_inactive int
declare @reinit bit
declare @allow_anonymous bit
declare @compatlevel int
declare @automatic_reinitialization_policy bit
declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0

-- article status below
select @inactive = 0
select @new_inactive = 5
select @new_active = 6

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

--
-- Security Check.
-- Only the System Administrator (SA) or the Database Owner (dbo) can
-- add an article to a publication.
--
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)

--
-- Parameter Check: @filtername.
-- The join_filter_name cannot be NULL
--
if @filtername is NULL
begin
raiserror (14043, 11, -1, @filtername, 'sp_addmergefilter')
return (1)
end

if @join_filterclause is NULL or @join_filterclause = ''
begin
raiserror (14043, 11, -1, '@join_filterclause', 'sp_addmergefilter')
return (1)
end

if @filter_type not in (1,2,3) or @filter_type is null
begin
raiserror (21575, 16, -1)
goto FAILURE
end

--
-- Parameter Check: @publication.
-- The @publication id cannot be NULL and must conform to the rules
-- for identifiers.
--
if @publication is NULL
begin
raiserror (14003, 16, -1)
return (1)
end


--
-- Get the pubid and make sure the publication exists
--
select @pubid = pubid,
@snapshot_ready = snapshot_ready,
@allow_anonymous = allow_anonymous,
@compatlevel = backward_comp_level,
@automatic_reinitialization_policy = automatic_reinitialization_policy
from dbo.sysmergepublications
where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication)
return (1)
end

--
-- Check to make sure that the filtername does not already exist in the
-- given publication
--
if exists (select * from sysmergesubsetfilters where filtername = @filtername and pubid = @pubid)
begin
raiserror (20626, 16, -1, @filtername, @publication)
return (1)
end

if @article = @join_articlename
begin
raiserror(21599, 16, -1)
return (1)
end

select @db_name = db_name from dbo.sysmergesubscriptions
where (pubid=@pubid) and (subid=@pubid)
IF @db_name <> db_name()
BEGIN
RAISERROR (20047, 16, -1)
RETURN (1)
END
--
-- Parameter Check: @article.
-- Check to see that the @article is valid and does exist
--
if @article is NULL
begin
raiserror (20045, 16, -1)
return (1)
end

select @artid = artid, @objid=objid, @vertical=vertical_partition, @art_nickname = nickname, @art_status = status from dbo.sysmergearticles where name = @article and pubid = @pubid
if @artid is NULL
begin
raiserror (20027, 16, -1, @article)
return (1)
end

--
-- Get the id of the @join_articlename
--
select @join_objid = objid, @join_vertical= vertical_partition, @joinart_status = status from dbo.sysmergearticles where name=@join_articlename and pubid = @pubid

IF @join_objid is NULL
BEGIN
raiserror (14027, 11, -1, @join_articlename)
return (1)
END

-- don't reinit out if this is a new article and we are not adding a logical record relationship
if (@filter_type & 2) = 2 or (@art_status <> @inactive and @art_status <> @new_inactive)
select @reinit = 1
else
select @reinit = 0

if @snapshot_ready>0
begin
--
-- make sure we know we really want to do this.
--
if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
begin
raiserror(21366, 16, -1, @filtername)
return 1
end

if @force_reinit_subscription = 0 and @reinit = 1
begin
-- check to see if there are any subscriptions to this publication before erroring out
if (@allow_anonymous = 1 and @compatlevel < 90) or
exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>pubid and status=1)
begin
raiserror(21367, 16, -1, @filtername)
return 1
end
end
end

-- check the validity of join_filterclause --
select @object = object_name(@objid)
select @owner = quotename(SCHEMA_NAME(schema_id)) from sys.objects where object_id = @objid

select @join_object = object_name(@join_objid)
select @join_object_owner = quotename(SCHEMA_NAME(schema_id)) from sys.objects where object_id = @join_objid

select @object_view = @object
select @join_object_view = @join_object


-- is current article vertically partitioned --
if @vertical=1
begin
select @object_view = 'MSmerge_OBJECT_VIEW_' + @object

select @qual_object_view=@owner + '.' + QUOTENAME(@object_view)
select @qual_object= @owner + '.' + QUOTENAME(@object)
select @unqual_object = QUOTENAME(@object)

exec @retcode = sys.sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @objid
exec ('create view ' + @qual_object_view + ' as select ' + @column_list + ' from ' + @qual_object + ' ' + @unqual_object)
if @@ERROR<>0
return 1
end

-- is join article vertically partitioned --
if @join_vertical=1
begin
select @join_object_view = 'MSmerge_JOIN_OBJECT_VIEW_' + @join_object
select @qual_join_object_view = @join_object_owner + '.' + QUOTENAME(@join_object_view)
select @qual_join_object= @join_object_owner + '.' + QUOTENAME(@join_object)
select @unqual_join_object = QUOTENAME(@join_object)

exec @retcode = sys.sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @join_objid
exec ('create view ' + @qual_join_object_view + ' as select ' + @column_list + ' from ' + @qual_join_object+ ' ' + @unqual_join_object)
if @@ERROR<>0
begin
if 1=@vertical
exec('drop view ' + @qual_object_view)
return 1
end
end

select @qual_object_view=@owner + '.' + QUOTENAME(@object_view)
select @qual_join_object_view = @join_object_owner + '.' + QUOTENAME(@join_object_view)
select @qual_join_object= QUOTENAME(@join_object)
select @qual_object= QUOTENAME(@object)

exec ('declare @test int select @test=1 from ' + @qual_object_view + ' ' + @qual_object + ', ' + @qual_join_object_view + ' ' + @qual_join_object + ' where ' + @join_filterclause)
if @@ERROR<>0
begin
if @vertical=1
exec ('drop view ' + @qual_object_view)
if @join_vertical=1
exec ('drop view ' + @qual_join_object_view)
raiserror(21256, 16, -1, @join_filterclause, @article)
return (1)
end

if @vertical=1
exec ('drop view ' + @qual_object_view)
if @join_vertical=1
exec ('drop view ' + @qual_join_object_view)

-- check if the join filter clause contains any column of type that is not supported in
-- a join filter.

if exists (
select * from sys.columns
where object_id = @objid and
(
-- (sys.fn_IsTypeBlob(sc.system_type_id,sc.max_length) = 1) -- Blob type text,ntext,xml
(system_type_id in (type_id('image'), type_id('text'), type_id('ntext'), type_id('xml')))
or max_length = -1
or system_type_id = 240 -- CLR-UDTs
)
and sys.fn_MSisfilteredcolumn(@join_filterclause, name, @objid) = 1
)
begin
raiserror(22518, 16, -1, @qual_object)
return(1)
end

select @join_nickname = nickname from dbo.sysmergearticles where pubid = @pubid AND objid = @join_objid
if @join_nickname is NULL
begin
raiserror (20001, 11, -1, @article, @publication)
return (1)
end

if (@filter_type & 2) = 2
begin
declare @pubname_lessthan90compat sysname
declare @owner_qualified_art_name nvarchar(400)
declare @owner_qualified_join_name nvarchar(400)

select @pubname_lessthan90compat = NULL

-- Only supported with publications that have 90 compatibility level.
select top 1 @pubname_lessthan90compat = name from dbo.sysmergepublications
where backward_comp_level < 90
and pubid in
(select pubid from dbo.sysmergearticles where nickname = @art_nickname)

if @pubname_lessthan90compat is not null
begin
raiserror(21574, 16, -1, @article, @pubname_lessthan90compat)
return 1
end

select @pubname_lessthan90compat = NULL

-- Only supported with publications that have 90 compatibility level.
select top 1 @pubname_lessthan90compat = name from dbo.sysmergepublications
where backward_comp_level < 90
and pubid in
(select pubid from dbo.sysmergearticles where nickname = @join_nickname)

if @pubname_lessthan90compat is not null
begin
raiserror(21574, 16, -1, @join_articlename, @pubname_lessthan90compat)
return 1
end

if @join_unique_key = 0
begin
-- logical record relation only supported with join_unique_key=1
raiserror(21539, 16, -1)
return 1
end

-- Cannot use Logical records and BusinessLogicResolvers at the same time.
if exists (select * from dbo.sysmergearticles where ((nickname = @art_nickname) or
(nickname = @join_nickname)) and
(resolver_clsid = '00000000-0000-0000-0000-000000000000'))
begin
raiserror(20708, 16, -1)
return 1
end

if exists (select * from dbo.sysmergepublications where pubid = @pubid and allow_web_synchronization = 1)
begin
raiserror(22545, 16, -1, @publication)
return 1
end

if exists (select * from dbo.sysmergepublications where pubid = @pubid and sync_mode = 1)
begin
raiserror(22541, 16, -1, @publication)
return 1
end

if exists (select * from dbo.sysmergesubsetfilters
where art_nickname = @art_nickname
and join_nickname <> @join_nickname
and (filter_type & 2) = 2)
begin
-- there cannot be two parents of a child in a logical record relationship
raiserror (21538, 16, -1, @object, @join_object)
return 1
end

if @filter_type & 1 = 1 and exists (select * from dbo.sysmergepublications
where pubid = @pubid and use_partition_groups <= 0)
begin
raiserror(21571, 16, -1, @publication)
return 1
end


-- We do not allow logical records to be used if the articles involved in a join filter
-- have cascading constraints defined on them
if exists (select * from sys.foreign_keys fks
where fks.parent_object_id in (@join_objid, @objid) and
(ObjectProperty(fks.object_id, 'CnstIsDeleteCascade') = 1
or ObjectProperty(fks.object_id, 'CnstIsUpdateCascade') = 1
or fks.delete_referential_action = 2 -- on delete set null
or fks.delete_referential_action = 3 -- on delete set default
or fks.update_referential_action = 2 -- on update set null
or fks.update_referential_action = 3 -- on update set default
)
)
begin
select @owner_qualified_art_name = quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name)
from sys.objects
where object_id = @objid

select @owner_qualified_join_name = quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name)
from sys.objects
where object_id = @join_objid

raiserror(25019, 16, -1, @owner_qualified_art_name, @owner_qualified_join_name)
return 1
end


-- Enforce DRI relation
exec @retcode = sys.sp_MScheck_joinfilter_using_dri @publication,
@article,
@objid,
@join_objid,
1,
@join_filterclause
if @@error <> 0 or @retcode <> 0
return 1

-- based on usability feeback, we should set the allow_subscriber_initiated_snapshot
-- option to 1 rather than raise an error.
update dbo.sysmergepublications set allow_subscriber_initiated_snapshot = 1
where pubid = @pubid
end

IF NOT EXISTS (select * from dbo.sysmergearticles where pubid=@pubid AND nickname = @join_nickname)
BEGIN
RAISERROR (20046, 16, -1) -- Only the original publisher can do so --
RETURN (1)
END

--
-- Make sure that the table name specified is a table and not a view.
--

if NOT exists (select * from sys.objects
where object_id = @join_objid AND type = 'U')
begin
raiserror (14028, 16, -1)
return (1)
end

-- We do not allow cycles between join filters. When trying to make article a1 a child
-- of parent article a2, we must make sure that article a1 is not a parent of a2 in
-- our existing filters, either directly or indirectly. This test inserts the nodes
-- currently reachable by the proposed child into a temporary table,
-- and makes sure the proposed parent is not contained in that list.
create table #reachable (article int)
insert into #reachable values (@art_nickname)
while (@@ROWCOUNT > 0)
begin
insert into #reachable (article)
select distinct art_nickname from sysmergesubsetfilters where join_nickname in (select article from #reachable)
and art_nickname not in (select article from #reachable)
and pubid = @pubid
end
if exists (select null from #reachable where article = @join_nickname)
begin
drop table #reachable
raiserror(21869, 16, -1, @join_articlename, @article)
return 1
end

drop table #reachable

begin tran
save tran addmergefilter

exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(20713, 16, -1, 'sp_addmergefilter', @publication)
goto FAILURE
end

select @got_merge_admin_applock = 1

if @snapshot_ready>0
begin
update dbo.sysmergepublications set snapshot_ready=2,
use_partition_groups = case when use_partition_groups = 1 then 2 else use_partition_groups end
where pubid=@pubid
if @@ERROR<>0
goto FAILURE

if @reinit = 1
begin
exec @retcode = sys.sp_MSreinitmergepublication
@publication = @publication,
@upload_first = @automatic_reinitialization_policy
if @retcode<>0 or @@ERROR<>0
goto FAILURE
end
end

--
-- Add the join filter to dbo.sysmergesubsetfilters if it is not already there
--

IF exists (select * from dbo.sysmergesubsetfilters
where filtername=@filtername and pubid=@pubid and artid=@artid)
begin
raiserror (20002, 16, -1, @filtername, @article, @publication)
goto FAILURE
end

insert INTO dbo.sysmergesubsetfilters(filtername, pubid, artid, art_nickname, join_articlename, join_nickname, join_unique_key, join_filterclause, filter_type)
values(@filtername, @pubid, @artid, @art_nickname, @join_articlename, @join_nickname, @join_unique_key, @join_filterclause, @filter_type)
if @@error <> 0
begin
goto FAILURE
end

exec @retcode = sys.sp_MSvalidate_wellpartitioned_articles @publication
if @@error <> 0 or @retcode <> 0
goto FAILURE

execute @retcode = sys.sp_MSsubsetpublication @publication
if @@ERROR <> 0 or @retcode <>0
goto FAILURE

exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
commit tran

return (0)

FAILURE:
if @@trancount > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
rollback tran addmergefilter
commit tran
end
RAISERROR (20038, 16, -1, @article, @publication)
return (1)

No comments:

Post a Comment

Total Pageviews