C# and I: 06/13/12

June 13, 2012

sp_validate_replica_hosts_as_publishers (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_validate_replica_hosts_as_publishers(nvarchar @original_publisher
, nvarchar @publisher_db)

MetaData:

 --   
--  Name: sp_validate_replica_hosts_as_publishers  
--   
--  Descriptions: Retrieve the redirected publisher for the specified   
--                publisher database pair. If the publisher is not  
--                currently redirected, return NULL.   
--   
--                For redirected publishers, a connection is made to the  
--                redirected publisher to determine whether the target of  
--                redirection is an availability group VNN Name. If it is  
--                not, a message is returned indicating that the redirected  
--                publisher is not associated with an availability group.  
--   
--                  The availability group primary is then queried for the  
--                  names of the hosts of the member replicas. Each host in   
--                  turn is validated as a publisher for the database.  
--   
--                In general, the stored procedure will attempt to continue  
--                  after encountering errors, and will try to validate all  
--                  of the known replica hosts associated with the availability  
--                  group.  
--   
--  Parameters: as defined in create statement  
--   
--  Returns: 0 on success, 1 on failure; on failure errors are raised  
--   
--  Security: Public procedure invoked via RPC. check caller for db_owner   
--            or in the PAL of a publication of the named pubisher check  
--   
create procedure sys.sp_validate_replica_hosts_as_publishers  
(  
    @original_publisher sysname,  
    @publisher_db sysname,  
    @redirected_publisher sysname output  
)  
as  
begin  
  
   set nocount on  
  
   declare   
        @target_server sysname,  
        @publisher_linked_server sysname,  
        @dbname sysname,  
        @retcode int,  
        @use_caller_credentials bit  
          
   set @redirected_publisher = null  
   set @target_server = null  
   set @publisher_linked_server = null  
   set @dbname = db_name()  
   set @use_caller_credentials = 1  
     
    --  Has to be executed from a distribution database  
    --   
    if (sys.fn_MSrepl_isdistdb (@dbname) <> 1)  
    begin  
        raiserror(21874, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers', @dbname)  
        return 1  
    end      
          
    --  Verify input parameters are not NULL  
    --   
    if @original_publisher is null or  
        @publisher_db is null  
    begin  
        raiserror (21875, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers')  
        return 1  
    end  
      
    --  Security check  
    --   
    if is_member(N'db_owner') <> 1  
    begin  
        exec @retcode = sys.sp_MSrepl_DistDBPALAccess @original_publisher  
          
        if (@retcode <> 0) or (@@error <> 0)  
        begin  
            raiserror (21873, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers')  
            return 1  
        end  
    end  
          
    --  If the publisher of the database has not been redirected  
    --  return without performing any validation.  
    --   
    select @redirected_publisher = redirected_publisher  
    from MSredirected_publishers  
    where upper(original_publisher) = upper(rtrim(@original_publisher))  
    and publisher_db = rtrim(@publisher_db)  
          
    if @redirected_publisher is null  
    begin  
        raiserror(21871, 10, 0, @original_publisher, @publisher_db);  
        return 0  
    end  
          
    --  Verify that the original publisher is a SQL Server publisher of this   
    --  distributor.  
    --   
    if not exists (  
        select name from msdb.dbo.MSdistpublishers  
        where upper(rtrim(@original_publisher)) = upper(name) collate database_default  
        and N'MSSQLSERVER' = upper(publisher_type))   
    begin  
        raiserror (21876, 16, -1, @@servername, @original_publisher, @publisher_db)  
        return 1  
    end  
      
    --  Verify that there is a sysservers entry for the original publisher   
    --  at the distributor.  
    --   
    if not exists (  
        select srvid  
        from master..sysservers  
        where upper(srvname) collate database_default = upper(rtrim(@original_publisher)))  
    begin  
        raiserror (21877, 16, -1, @original_publisher, @publisher_db)  
        return 1  
    end  
                  
    --  Create a linked server to connect to the target of redirection.  
    --   
    exec @retcode = sys.sp_hadr_create_linked_server @original_publisher, @publisher_db,  
        @redirected_publisher, @publisher_linked_server output, @use_caller_credentials  
    if @retcode <> 0  
    begin  
        return 1  
    end  
      
    --  Query the availability group primary for the names of the member replicas associated  
    --  with the publisher database and validate the host server for each replica as a  
    --  replication publisher.   
    --   
    exec @retcode = sys.sp_hadr_validate_replica_hosts_as_publishers @original_publisher,   
        @redirected_publisher, @publisher_db, @publisher_linked_server   
      
    return @retcode  
end

sp_vupgrade_mergeobjects (Transact-SQL MetaData) Definition

Definition:

sys.sp_vupgrade_mergeobjects(nvarchar @login
, nvarchar @password
, bit @security_mode)

MetaData:

   
create procedure sys.sp_vupgrade_mergeobjects( @login sysname = NULL,  @password sysname = N'', @security_mode bit = 1)   
as  
begin  
    declare @qual_source_object nvarchar(540),  
                @artnick int,  
                @objid int,  
                @pubid uniqueidentifier,  
                @artid uniqueidentifier,  
                @retcode int,  
                @source_object sysname,  
                @source_owner sysname  
     
    declare @publication_number smallint  
    declare @partition_id_eval_proc sysname  
    declare @pubidstr sysname  
  
  
  
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
    --  verify input parameters (1,2.3)  
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
    --  1. don't upgrade system databases and distribution databases  
     if db_name() in (N'master' COLLATE DATABASE_DEFAULT,  
                                N'tempdb' COLLATE DATABASE_DEFAULT,  
                                N'msdb'   COLLATE DATABASE_DEFAULT,  
                                N'model'   COLLATE DATABASE_DEFAULT)  
        or sys.fn_MSrepl_isdistdb (db_name()) = 1  
        or databasepropertyex(db_name(), 'Updateability') <> 'READ_WRITE'                                  
        return 1  
  
    --  2. Security Check: require sysadmin  
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)  
    BEGIN  
        RAISERROR(21089,16,-1)   
        RETURN (1)  
    END  
  
    --  3. Check to ensure a login is provided if security mode is SQL Server authentication.  
    select @login = rtrim(ltrim(isnull(@login, '')))  
    if @security_mode = 0 and @login = ''  
    begin  
        --  '@login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'  
        raiserror(21694, 16, -1, '@login', '@security_mode')  
        return 1  
    end  
  
    -- 4. Only upgrade merge databases  
    if( object_id('dbo.sysmergearticles') is NULL)  
        return 1  
                     
    begin tran  
    save tran vupgrade_mergeobjects     
  
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --   
    --  Loop through each article in the database  
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --       
  
    --  regenerate procs that is publication-specific ( not on the article leve)  
    declare @pubs table ( pubid uniqueidentifier)  --  a list of publications that has been processed  
    declare @snapshot_ready tinyint      
      
    select @artnick = min(nickname) from dbo.sysmergearticles  
    while @artnick is not null  
    begin  
          
        select @objid = NULL  
        select @source_object = NULL  
        select top 1 @objid = objid, @artid = artid, @pubid = pubid from dbo.sysmergearticles where nickname = @artnick  
        select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid  
        if @objid is NULL or @source_object is NULL  
            goto error  
  
        --  don't regenerate objects if snapshot has not been run  
            select @snapshot_ready = snapshot_ready from dbo.sysmergepublications where pubid = @pubid  
            if @snapshot_ready=0  
                goto nextarticle          
  
        --  should we drop trigger before disable triggers?  
        exec @retcode = sys.sp_MSdroparticletriggers @source_object, @source_owner  
        if @retcode<>0 or @@error<>0  
            goto error  
  
        -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --   
        --  Step 1: disable DML for all articles in this database    
        -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --               
        exec sys.sp_MScreatedisabledmltrigger @source_object, @source_owner  
        if @retcode<>0 or @@error<>0  
            goto error  
  
        -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --   
        --  Step 2: regenerate triggers and procs  
        -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --       
  
        select @qual_source_object = QUOTENAME(@source_owner) + N'.' + QUOTENAME(@source_object)  
        exec sys.sp_MSResetTriggerProcs @qual_source_object, @pubid, 1, 1  -- article level reset, regenerate sub procs  
  
          
        --  if this publication has not been processed for regenerating the publication-level objects  
        if not exists (select pubid from @pubs where pubid = @pubid)  
        begin  
            declare @use_partition_groups smallint  
            select @use_partition_groups = use_partition_groups from dbo.sysmergepublications where pubid = @pubid  
            if (@use_partition_groups > 0) -- only do this if we use partition groups  
            begin          
                exec @retcode = sys.sp_MSsetup_publication_for_partition_groups @pubid  
  
                 --  regenerate the partitionid_eval proc, but don't change the table, by setting @upgrade = 1  
                exec @retcode = sys.sp_MSsetup_partition_groups_table @pubid, 1  
            end                  
  
            --  now insert this pubid into the tracking table  
            insert into @pubs(pubid) values( @pubid )  
        end -- if not exists (select pubid from @pubs where pubid = @pubid)  
  
        -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --   
        --  Step 3: enable DML for all articles in this database  
        -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --   
  
        exec sys.sp_MSdropdisabledmltrigger @source_object, @source_owner  
        if @retcode<>0 or @@error<>0  
            goto error  
  
nextarticle:  
        --  find next article          
        select @artnick = min(nickname) from dbo.sysmergearticles where nickname > @artnick  
    end --  end article while  
     
    commit tran  
              
    return (0)  
  
error:   
    rollback tran vupgrade_mergeobjects  
    commit tran  
    return (1)  
end

sp_views_rowset2 (Transact-SQL MetaData) Definition

Definition:

sys.sp_views_rowset2(nvarchar @view_schema)

MetaData:

   
create procedure sys.sp_views_rowset2  
(  
    @view_schema    sysname = null  
)  
as  
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
--  copy & pasted from version 1 of the SProc and removed checks for 1st parameter !  
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
    select  
        TABLE_CATALOG   = db_name(),  
        TABLE_SCHEMA    = schema_name(a_v.schema_id),  
        TABLE_NAME      = a_v.name,  
        VIEW_DEFINITION = convert(nvarchar(1),null),  
        CHECK_OPTION    = convert(bit, NULL),  
        IS_UPDATABLE    = convert(bit, NULL),  
        DESCRIPTION     = convert(nvarchar(1),null),  
        DATE_CREATED    = a_v.create_date,  
        DATE_MODIFIED   = convert(datetime,null)  
    from  
        sys.all_views a_v  
    where  
        (@view_schema is null or schema_id(@view_schema) = a_v.schema_id) and  
        has_perms_by_name(quotename(schema_name(a_v.schema_id)) + '.' + quotename(a_v.name),  
                          'object',  
                          'select') = 1  
    order by 1, 2, 3

sp_views_rowset (Transact-SQL MetaData) Definition

Definition:

sys.sp_views_rowset(nvarchar @view_name
, nvarchar @view_schema)

MetaData:

   
create procedure sys.sp_views_rowset  
(  
    @view_name      sysname,  
    @view_schema    sysname = null  
)  
as  
    select  
        TABLE_CATALOG   = db_name(),  
        TABLE_SCHEMA    = schema_name(a_v.schema_id),  
        TABLE_NAME      = a_v.name,  
        VIEW_DEFINITION = convert(nvarchar(1),null),  
        CHECK_OPTION    = convert(bit, NULL),  
        IS_UPDATABLE    = convert(bit, NULL),  
        DESCRIPTION     = convert(nvarchar(1),null),  
        DATE_CREATED    = a_v.create_date,  
        DATE_MODIFIED   = convert(datetime,null)  
    from  
        sys.all_views a_v  
    where  
        (  
            (@view_schema is null and a_v.name = @view_name) or  
            object_id(quotename(@view_schema) + '.' + quotename(@view_name)) = a_v.object_id  
        ) and  
        has_perms_by_name(quotename(schema_name(a_v.schema_id)) + '.' + quotename(@view_name),  
                          'object',  
                          'select') = 1  
    order by 1, 2, 3

sp_verifypublisher (Transact-SQL MetaData) Definition

Definition:

sys.sp_verifypublisher(nvarchar @publisher)

MetaData:

   
--   
--  Name:  
--         sp_verifypublisher  
--   
--  Description:  
--         Verify that publisher state is valid for HREPL  
--   
--  Returns:  
--         0      == Valid  
--         1      == Error in publisher state  
--   
--  Security:  
--         Internal  
--   
--  Notes:  
--         Used by any routine that wants to validate  
--         the state of the HREPL publisher before  
--         performing any actions against it.  
--         Errors are issued at warning level to allow  
--         the calling proc to decide how to handle it.  
--         For example, drop procedures would typically  
--         choose to ignore the errors since everything  
--         is being dropped.  Add procedures would tend  
--         to error out immediately.  
--   
  
CREATE PROCEDURE sys.sp_verifypublisher  
(  
    @publisher            sysname  
)  
AS  
BEGIN  
    DECLARE @retcode    int  
    DECLARE    @publisher_type    sysname  
    DECLARE @cmd        nvarchar(4000)  
      
    -- -- -- --  security check, db_owner  
    exec @retcode = dbo.sp_MSreplcheck_publish  
    if @@ERROR <> 0 or @retcode <> 0  
        return(1)  
  
    --  Get publisher information  
    EXEC @retcode = sys.sp_MSrepl_getpublisherinfo    @publisher        = @publisher,  
                                                    @publisher_type    = @publisher_type OUTPUT,  
                                                    @rpcheader        = @cmd OUTPUT  
      
    IF @retcode <> 0  
        RETURN (@retcode)  
  
    --  Execute provider-specific verification  
    IF UPPER(@publisher_type) IN ('ORACLE', 'ORACLE GATEWAY')  
    BEGIN  
        SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT  
        SELECT @cmd = @cmd + 'sys.sp_ORAverifypublisher'  
        EXEC @retcode = @cmd @publisher  
    END  
    ELSE  
    BEGIN  
        RAISERROR(21645, 16, -1, @publisher_type)  
        SET @retcode = 1  
    END  
  
    return @retcode  
END

sp_validname (Transact-SQL MetaData) Definition

Definition:

sys.sp_validname(bit @raise_error
, nvarchar @name)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- --  sp_validname -- -- -- -- -- -- -- -- -- -- -- -- -- -- --   
CREATE PROCEDURE sys.sp_validname  
    @name            sysname,  
    @raise_error    bit = 1  
AS  
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
    --     Check for valid SQL-Server identifiers: All non-binary-zero  
    --     characters are valid.  String must not be null or empty.  
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
    --  QuoteName() will be non-null only if no null-characters.  Also check length.  
    IF (quotename(@name) is not null AND datalength(@name) > 0)  
        RETURN (0)  
  
    --  Bad Name  
    if @raise_error = 1  
        raiserror (15004,-1,-1)  
    return (1) --  sp_validname

sp_validlang (Transact-SQL MetaData) Definition

Definition:

sys.sp_validlang(nvarchar @name)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- --  sp_validlang -- -- -- -- -- -- -- -- -- -- -- -- -- -- --   
CREATE PROCEDURE sys.sp_validlang  
    @name    sysname  
AS  
  
    --  Check to see if this language is in Syslanguages.  
    if exists (select * from sys.syslanguages where name = @name or alias = @name)  
        return(0)  
  
    raiserror(15033,-1,-1,@name)  
    return (1) --  sp_validlang

sp_validatemergesubscription (Transact-SQL MetaData) Definition

Definition:

sys.sp_validatemergesubscription(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, tinyint @level)

MetaData:

   
create procedure sys.sp_validatemergesubscription  
    (@publication            sysname,  
     @subscriber            sysname,  
     @subscriber_db            sysname,  
     @level                    tinyint  
     ) AS  
  
    set nocount on   
    declare @retcode                int  
    declare @pubid                    uniqueidentifier  
    declare @subid uniqueidentifier  
    --   
    --  Security Check  
     --   
    EXEC @retcode = sys.sp_MSreplcheck_publish  
    IF @@ERROR <> 0 or @retcode <> 0  
        return (1)  
  
    --  make sure current database is enabled for merge replication  --   
    exec @retcode=sys.sp_MSCheckmergereplication  
    if @@ERROR<>0 or @retcode<>0  
        return (1)  
  
    select @pubid = pubid 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  
    if @level <1 or @level > 3  
    begin  
        raiserror(21184, 16, -1, '@level', '1', '2','3')  
        return (1)  
    end  
  
    select @subid = NULL  
    select @subid = subid from dbo.sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and   
              UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default  
    if @subid is NULL  
    begin  
        raiserror(14055, 16, -1)  
        return (1)  
    end  
  
  
    update dbo.MSmerge_replinfo   
        set validation_level=@level   
        where repid = @subid  
    if @@ERROR<>0  
        return (1)  
    return (0)

sp_validatemergepullsubscription (Transact-SQL MetaData) Definition

Definition:

sys.sp_validatemergepullsubscription(nvarchar @publication
, nvarchar @publisher
, nvarchar @publisher_db
, tinyint @level)

MetaData:

 create procedure sys.sp_validatemergepullsubscription  
    (@publication            sysname,  
     @publisher                sysname,  
     @publisher_db            sysname,  
     @level                    tinyint  
     ) AS  
  
    set nocount on   
    declare @retcode                int  
    declare @pubid                    uniqueidentifier  
    declare @subscriber                sysname  
    declare @subscriber_db            sysname  
    declare @subid                  uniqueidentifier  
    --   
    --  Security Check  
     --   
    EXEC @retcode = sys.sp_MSreplcheck_publish  
    IF @@ERROR <> 0 or @retcode <> 0  
        return (1)  
  
    select @subscriber = @@SERVERNAME, @subscriber_db=db_name()  
    select @pubid = NULL  
    select @subid = NULL  
      
    select @pubid=pubid from dbo.sysmergepublications where LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db and name=@publication  
    if @pubid is NULL  
    begin  
        raiserror (20026, 16, -1, @publication)  
        return (1)  
    end  
      
    if @level <0 or @level > 3  
    begin  
        raiserror(21184, 16, -1, '@level', '1', '2','3')  
        return (1)  
    end  
  
    select @subid = subid from dbo.sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and LOWER(subscriber_server) = LOWER(@subscriber)  
    if @subid is NULL  
    begin  
        raiserror(14055, 16, -1)  
        return (1)  
    end  
  
    update dbo.MSmerge_replinfo   
        set validation_level=@level   
        where repid = @subid  
    if @@ERROR<>0  
        return (1)  
    return (0)

sp_validatemergepublication (Transact-SQL MetaData) Definition

Definition:

sys.sp_validatemergepublication(nvarchar @publication
, tinyint @level)

MetaData:

   
create procedure sys.sp_validatemergepublication  
    (@publication            sysname,  
     @level                    tinyint  
     ) AS  
       
    set nocount on   
    declare @artid                    uniqueidentifier  
    declare @schematype                int  
    declare @schemaversion            int  
    declare @schemaguid                uniqueidentifier  
    declare @schematext                nvarchar  
    declare @retcode                int  
    declare @pubid                    uniqueidentifier  
    declare @compatlevel int  
      
    --   
    --  Security Check  
     --   
    EXEC @retcode = sys.sp_MSreplcheck_publish  
    IF @@ERROR <> 0 or @retcode <> 0  
        return (1)  
  
    --  make sure current database is enabled for merge replication  --   
    exec @retcode=sys.sp_MSCheckmergereplication  
    if @@ERROR<>0 or @retcode<>0  
        return (1)  
  
    select @pubid = pubid, @compatlevel = backward_comp_level 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  
    if @level <1 or @level > 3  
    begin  
        raiserror(21184, 16, -1, '@level', '1', '2','3')  
        return (1)  
    end  
  
    if @compatlevel < 30  
    begin  
        raiserror(21356, 10, -1, @publication)  
        exec @retcode=sys.sp_MSBumpupCompLevel @pubid, 30   
        if @@ERROR<>0 or @retcode<>0          
            return (1)  
    end  
      
    select @schemaversion = schemaversion from dbo.sysmergeschemachange  
    if (@schemaversion is NULL)  
        set @schemaversion = 1  
    else  
        select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange  
        set @schemaguid = newid()  
        set @artid = newid()  
        set @schematype = 66 --  publication wide validation  --   
        select @schematext = convert(nvarchar, @level)          
        exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext  
        if @@ERROR<>0 or @retcode<>0  
            return (1)  
    return (0)

sp_validatelogins (Transact-SQL MetaData) Definition

Definition:

sys.sp_validatelogins()

MetaData:

 create procedure sys.sp_validatelogins  
AS  
    --  Must be securityadmin (or sysadmin) to execute  
    if is_srvrolemember('securityadmin') = 0 and is_srvrolemember('sysadmin') = 0  
    begin  
        raiserror(15247,-1,-1)  
        return 1  
    end  
  
    --  Use get_sid() to determine if nt name is still valid (builtin is only available from system procs!)  
    select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins  
        where isntname = 1 and get_sid(loginname) is null  
    return 0 --  sp_validatelogins

sp_validatecache (Transact-SQL MetaData) Definition

Definition:

sys.sp_validatecache(nvarchar @publisher
, nvarchar @publication
, nvarchar @article)

MetaData:

   
--   
--  Name:  
--         sp_validatecache  
--   
--  Description:  
--         Validate the distributor side Oracle meta data (Oracle specific)  
--   
--  Inputs:  
--         @publisher        == name of Oracle publisher  
--         @publication        == publication name (default '%')  
--         @article        == table name (defulat '%')   
--   
--  Returns:  
--         Return code (0 for success, 1 for failure)  
--   
--         Result set  
--   
--         column                description  
--   
--         publication            publication name  
--         article                article name  
--         column                column name  
--         index_or_constraint index or constraint name  
--         columnordinal        column ordinal  
--         type                column, index, or constraint type  
--         length                length  
--         prec                precision  
--         scale                scale  
--         isnullable            is nullable  
--       validate            primary key validated  
--       consstatus          constraint status  
--       idxstatus           index status   
--         description            'Column dropped'  
--                             'Column added'  
--                             'Column changed from'  
--                             'Column changed to'  
--                             'Index dropped'  
--                             'Index added'  
--                             'Index changed from'  
--                             'Index changed to'  
--                             'Constraint dropped'  
--                             'Constraint added'  
--                             'Constraint changed from'  
--                             'Constraint changed to'  
--                             'Index column dropped'  
--                             'Index column added'  
--                             'Constraint column dropped'  
--                             'Constraint column added'  
--   
--  Security:  
--         public --  caller must be sysadmin  
--   
--  Notes:  
--         This stored procedure is provided so that the administrator of Oracle  
--         publishing can determine whether the current meta data for published  
--         Oracle tables that is cached at the distributor, is still valid.  It is  
--         intended to assist in diagnosing problems resulting from a meta data  
--         mismatch between the Oracle database and the distributor meta data cache.  
--   
--         If the @publication parameter is not specified, all publications for the  
--         Oracle publisher are examined.  If the @article parameter is not specified,  
--         all articles for the given publication are examined.  If @article is non NULL,  
--         and @publication is NULL, articles of the specified name from all publications  
--         are examined.  
--   
--         If an empty rowset is returned, the distributor meta data is valid.  
--         If a non-empty rowset is returned, the distributor meta data has been invalidated  
--         by meta data changes at the Oracle publisher.    
  
CREATE PROCEDURE sys.sp_validatecache  
(  
    @publisher        sysname,  
    @publication    sysname = '%',  
    @article        sysname = '%'  
)  
AS  
BEGIN  
    SET NOCOUNT ON  
  
    DECLARE @cmd            nvarchar(4000)  
    DECLARE @retcode        int  
    DECLARE @publisher_type    sysname  
  
    --  Security Check: requires sysadmin, done in sp_MSrepl_getpublisherinfo  
  
    SET @retcode = 0  
  
    EXEC @retcode = sys.sp_MSrepl_getpublisherinfo    @publisher      = @publisher,  
                                                    @rpcheader      = @cmd OUTPUT,  
                                                    @publisher_type    = @publisher_type OUTPUT,  
                                                    @hreplOnly      = 1                              
      
    IF @retcode <> 0  
        RETURN (@retcode)  
  
    --  Error if the publisher is not an Oracle publisher  
    IF @publisher_type NOT LIKE 'ORACLE%'  
    BEGIN  
        RAISERROR (21687, 16, -1, @publisher, @publisher_type)  
        RETURN (1)  
    END  
  
    SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT  
    set @cmd = @cmd + N'sys.sp_MSrepl_validatecache'  
      
    EXEC @retcode = @cmd    @publisher,  
        @publication,  
        @article  
    RETURN (@retcode)  
END

sp_user_counter8 (Transact-SQL MetaData) Definition

Definition:

sys.sp_user_counter8(int @newvalue)

MetaData:

 create procedure sys.sp_user_counter8 @newvalue int as  
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 8', @newvalue)

 --   
--  Name: sp_validate_redirected_publisher  
--   
--  Descriptions: Retrieve the redirected publisher for the specified   
--                publisher database pair. If the publisher is not  
--                currently redirected, return NULL.   
--   
--                For redirected publishers, validation checks are performed  
--                to verify that the target of the redirection is a suitable  
--                host for the published database. Both informational and  
--                error messages may be raised.  
--   
--                NOTE: On error, all called stored procedures will first  
--                      return the temporary linked server and then raise  
--                      the error. No errors are caught here.    
--   
--  Parameters: as defined in create statement  
--   
--  Returns: 0 on success, 1 on failure; on failure errors are raised  
--   
--  Security: Public procedure invoked via RPC. check caller for db_owner   
--            or in the PAL of a publication of the named pubisher check  
--   
create procedure sys.sp_validate_redirected_publisher  
(  
    @original_publisher sysname,  
    @publisher_db sysname,  
    @redirected_publisher sysname output  
)  
as  
begin  
  
   set nocount on  
  
   declare   
        @target_server sysname,  
        @publisher_linked_server sysname,  
        @dbname sysname,  
        @retcode int  
          
   set @redirected_publisher = null  
   set @target_server = null  
   set @publisher_linked_server = null  
   set @dbname = db_name()  
     
    --  Has to be executed from a distribution database  
    --   
    if (sys.fn_MSrepl_isdistdb (@dbname) <> 1)  
    begin  
        raiserror(21874, 16, -1, 'sys.sp_validate_redirected_publisher', @dbname)  
        return 1  
    end      
          
    --  Verify input parameters are not NULL  
    --   
    if @original_publisher is null or  
        @publisher_db is null  
    begin  
        raiserror (21875, 16, -1, 'sys.sp_validate_redirected_publisher')  
        return 1  
    end  
      
    --  Security check  
    --   
    if is_member(N'db_owner') <> 1  
    begin  
        exec @retcode = sys.sp_MSrepl_DistDBPALAccess @original_publisher  
          
        if (@retcode <> 0) or (@@error <> 0)  
        begin  
            raiserror (21873, 16, -1, 'sys.sp_validate_redirected_publisher')  
            return 1  
        end  
    end  
          
    --  If the publisher of the database has not been redirected  
    --  return without performing any validation.  
    --   
    select @redirected_publisher = redirected_publisher  
    from MSredirected_publishers  
    where upper(original_publisher) = upper(rtrim(@original_publisher))  
    and publisher_db = rtrim(@publisher_db)  
          
    if @redirected_publisher is null  
    begin  
        return 0  
    end  
          
    --  Verify that the original publisher is a SQL Server publisher of this   
    --  distributor.  
    --   
    if not exists (  
        select name from msdb.dbo.MSdistpublishers  
        where upper(rtrim(@original_publisher)) = upper(name) collate database_default  
        and N'MSSQLSERVER' = upper(publisher_type))   
    begin  
        raiserror (21876, 16, -1, @@servername, @original_publisher, @publisher_db)  
        return 1   
    end  
      
    --  Verify that there is a sysservers entry for the original publisher   
    --  at the distributor.  
    --   
    if not exists (  
        select srvid  
        from master..sysservers  
        where upper(srvname) collate database_default = upper(rtrim(@original_publisher)))  
    begin  
        raiserror (21877, 16, -1, @original_publisher, @publisher_db)  
        return 1   
    end  
                  
    --  Create a linked server to connect to the target of redirection.  
    --   
    exec @retcode = sys.sp_hadr_create_linked_server @original_publisher, @publisher_db,  
        @redirected_publisher, @publisher_linked_server output  
    if @retcode <> 0  
        return 1      
  
    --  Get the name of the target host  
    --       
    exec @retcode = sys.sp_hadr_get_target_server @original_publisher, @publisher_db,  
        @redirected_publisher, @publisher_linked_server, @target_server output  
    if @retcode <> 0  
        return 1      
          
    --  Verify that the redirected publisher is configured for replication  
    --   
    exec @retcode = sys.sp_hadr_verify_configured_for_repl @target_server, @publisher_db,  
        @publisher_linked_server  
    if @retcode <> 0  
        return 1      
      
    --  Verify that the original publisher and redirected publisher both share the same  
    --  distributor   
    --   
    exec @retcode = sys.sp_hadr_verify_publisher_at_distributor @target_server, @publisher_db,  
        @publisher_linked_server  
    if @retcode <> 0  
        return 1  
          
    --  Verify that the redirected publisher is a replication publisher  
    --   
    exec @retcode = sys.sp_hadr_verify_replication_publisher @target_server, @publisher_db,  
        @publisher_linked_server  
    if @retcode <> 0  
        return 1            
                  
    --  Verify that the published database subscribers of the original publisher are remote  
    --  servers of the redirected publisher   
    --   
    exec @retcode = sys.sp_hadr_verify_subscribers_at_publisher @original_publisher, @target_server,   
        @publisher_db, @publisher_linked_server  
    if @retcode <> 0  
        return 1   
      
    --  Drop the temporary linked server without raising an error  
    --   
    exec sys.sp_hadr_drop_linked_server @publisher_linked_server  
      
    return 0  
end

   
create procedure sys.sp_usertypes_rowset2  
(  
    @type_schema    sysname = null  
)  
as  
    select  
        UDT_CATALOGNAME         = db_name(),  
        UDT_SCHEMANAME          = schema_name(u.schema_id),  
        UDT_NAME                = convert(sysname,u.name),  
        UDT_ASSEMBLY_TYPENAME   = u.assembly_qualified_name  
    from  
        sys.assemblies a inner join  
        sys.assembly_types u on  
            (  
                a.assembly_id = u.assembly_id  
            )  
    where  
        (@type_schema is null or @type_schema = schema_name(u.schema_id))  
    order by 1, 2, 3

   
create procedure sys.sp_usertypes_rowset_rmt  
(  
    @type_server       sysname,  
    @type_catalog      sysname = null,  
    @type_name  sysname = null,  
    @type_schema        sysname = null,  
    @assembly_id    int = null  
)  
as  
    select  
        UDT_CATALOG_NAME,  
        UDT_SCHEMA_NAME,  
        UDT_NAME,  
        UDT_ASSEMBLY_ID,  
        MAX_BYTE_SIZE,  
        IS_FIXED_LENGTH,  
        IS_BINARY_ORDERED,  
        BOUND_CLASS_NAME,  
        PROG_ID  
    from  
        --  ISSUE - below pseudo-function is not exposed by metadata code!  
        sys.fn_remote_usertypes (@type_server,  
                                 @type_catalog,  
                                 @type_name,  
                                 @type_schema,  
                                 @assembly_id)  
    order by 1, 2, 3

   
create procedure sys.sp_usertypes_rowset  
(  
    @type_name      sysname,  
    @type_schema    sysname = null  
)  
as  
    select  
        UDT_CATALOGNAME         = db_name(),  
        UDT_SCHEMANAME          = schema_name(u.schema_id),  
        UDT_NAME                = convert(sysname,u.name),  
        UDT_ASSEMBLY_TYPENAME   = u.assembly_qualified_name  
    from  
        sys.assemblies a inner join  
        sys.assembly_types u on  
            (  
                a.assembly_id = u.assembly_id  
            )  
    where  
        u.name = @type_name and  
        (@type_schema is null or @type_schema = schema_name(u.schema_id))  
    order by 1, 2, 3

 create procedure sys.sp_user_counter9 @newvalue int as  
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 9', @newvalue)

 create procedure sys.sp_user_counter7 @newvalue int as  
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 7', @newvalue)

June 13, 2012

Total Pageviews

Where i go...