Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_check_subset_filter

  No additional text.


Syntax

create procedure sys.sp_check_subset_filter @filtered_table nvarchar(400)
                                        , @subset_filterclause nvarchar(1000)
                                        , @has_dynamic_filters bit = NULL OUTPUT
                                        , @dynamic_filters_function_list nvarchar(500) = NULL OUTPUT
as
    set nocount on

    declare @retcode int
    , @owner_qualified_art_name nvarchar(517)
    , @art_name nvarchar(258)
    , @viewname nvarchar(258)
    , @objid int
    , @found_functions bit
    , @can_use_partition_groups bit
    , @uses_host_name bit
    , @uses_suser_sname bit
    , @passed_in_has_dynamic_filters bit


    /*
    ** Security Check
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    set @passed_in_has_dynamic_filters = @has_dynamic_filters

    -- Initialize the flags to such a value that the occurrence of some condition will
    -- set the value to the opposite value.
    set @can_use_partition_groups = 1
    set @has_dynamic_filters = 0
    set @uses_host_name = 0
    set @uses_suser_sname = 0
    set @dynamic_filters_function_list = NULL

    select @objid = object_id(@filtered_table)

    if @objid is null
    begin
        declare @dbname sysname
        select @dbname = db_name()
        raiserror(20507, 16, 1, @filtered_table, @dbname)
        return 1
    end

    select @owner_qualified_art_name = quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name)
            , @art_name = quotename(name)
    from sys.objects
    where object_id = @objid

    select @viewname = quotename('MSmerge_subset_filter_view_' + convert(nvarchar(40), newid()))

    begin tran
    save tran check_subset_filter

    exec ('create view dbo.' + @viewname + ' as select placeholder = 1 from ' + @owner_qualified_art_name + ' ' + @art_name +
            ' where ' + '(' + @subset_filterclause + ')')

    if @@error <> 0
        goto UNDO

    exec @retcode = sys.sp_MScheck_functions_directly_in_view    @viewname = @viewname
                                                            , @has_dynamic_filters = @has_dynamic_filters output
                                                            , @dynamic_filters_function_list = @dynamic_filters_function_list output
                                                            , @uses_host_name = @uses_host_name output
                                                            , @uses_suser_sname = @uses_suser_sname output

    if @@error <> 0 or @retcode <> 0
        goto UNDO

    exec @retcode = sys.sp_MScheck_functions_indirectly_in_view @viewname = @viewname
                                                            , @use_partition_groups = NULL
                                                            , @can_use_partition_groups = @can_use_partition_groups output
                                                            , @has_dynamic_filters = @found_functions output
                                                            , @dynamic_filters_function_list = @dynamic_filters_function_list output
                                                            , @uses_host_name = @uses_host_name output
                                                            , @uses_suser_sname = @uses_suser_sname output
    if @@error <> 0 or @retcode <> 0
        goto UNDO

    if @found_functions = 1
    begin
        set @can_use_partition_groups = 0
        set @has_dynamic_filters = 1
    end

    -- rollback tran so that the temp view gets dropped.
    rollback tran check_subset_filter
    commit tran

    if @passed_in_has_dynamic_filters is NULL
    begin
        select can_use_partition_groups = @can_use_partition_groups
        , has_dynamic_filters = @has_dynamic_filters
        , dynamic_filters_function_list = @dynamic_filters_function_list
        , uses_host_name = @uses_host_name
        , uses_suser_sname = @uses_suser_sname
    end
    return 0

UNDO:

    if @@trancount > 0
    begin
        rollback tran check_subset_filter
        commit tran
    end
    return 1

 
Last revision 2008RTM
See also

  sp_addmergearticle (Procedure)
sp_changemergearticle (Procedure)
sp_MSdrop_rladmin (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash