C# and I

May 25, 2012

sp_MSremoveoffloadparameter (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_MSremoveoffloadparameter(varbinary @job_id
, nvarchar @agenttype)

MetaData:

 create procedure sys.sp_MSremoveoffloadparameter (  
    @job_id    VARBINARY(16),  
    @agenttype NVARCHAR(20)   
    ) AS  
    SET NOCOUNT ON  
      
    --   
    --  Declarations  
     --   
      
    DECLARE @paramstart    INT  
    DECLARE @paramend      INT  
    DECLARE @paramlen      INT  
    DECLARE @paramend2     INT  
    DECLARE @command       NVARCHAR(3200)   
    DECLARE @commandtail   NVARCHAR(3200)  
    DECLARE @pattern       NVARCHAR(100)  
  
    SELECT @pattern = N'%-[Oo][Ff][Ff][Ll][Oo][Aa][Dd]%'  
    SELECT @paramlen = LEN(N'-offload')  
    SELECT @command = NULL  
  
    --   
    --  Security Check: require sysadmin  
     --   
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)  
    BEGIN  
        RAISERROR(21089,16,-1)   
        RETURN (1)  
    END  
  
    --  use the job_id and the agent type to retrieve the agent command  
    --  line, only one agent command step is allowed in a job  
  
    SELECT @command = command FROM msdb.dbo.sysjobsteps   
     WHERE job_id = @job_id AND LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = LOWER(@agenttype collate SQL_Latin1_General_CP1_CS_AS)  
  
    --  purge all instances of '-offload servername' from the agent command line  
    SELECT @paramstart = PATINDEX(@pattern, @command)  
    WHILE ((@command IS NOT NULL) AND (@paramstart <> 0))  
    BEGIN         
        --  extract the part of the command line after the -Offload parameter  
        SELECT @commandtail = SUBSTRING(@command, @paramstart + @paramlen,   
                                        LEN(@command) -   
                                        (@paramstart + @paramlen) + 1)  
  
        --  search for the beginning of the server name that follows -offload  
        --  in the command tail; note that the empty spaces in the square  
        --  bracket of the pattern are actually a space and a tab  
  
        SELECT @paramend = PATINDEX(N'%[^     ]%', @commandtail)  
  
        --  search for the end of the server name that follows -offload  
        IF (@paramend <> 0)   
        BEGIN  
            SELECT @commandtail = SUBSTRING(@commandtail, @paramend,   
                                            LEN(@commandtail) - @paramend + 1)  
            SELECT @paramend2 = PATINDEX(N'%[     ]%', @commandtail)   
            IF (@paramend2 <> 0)  
            BEGIN  
                SELECT @paramend = @paramend + @paramend2 - 1  
            END  
            ELSE  
            BEGIN  
                --  reaching the end of the command line  
                SELECT @paramend = @paramend + LEN(@commandtail) - 1  
                IF @paramstart > 1   
                BEGIN  
                    SELECT @paramstart = @paramstart - 1  
                    SELECT @paramend = @paramend + 1  
                END  
            END  
          
        END                  
        --  Remove the -Offload parameter from the command line  
        SELECT @command = STUFF(@command, @paramstart, @paramlen + @paramend, N'')  
  
        SELECT @paramstart = PATINDEX(@pattern, @command)  
    END  
      
    --  update the agent command line with all the -offload's removed  
    UPDATE msdb.dbo.sysjobsteps   
       SET command = @command  
     WHERE job_id = @job_id AND LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = LOWER(@agenttype collate SQL_Latin1_General_CP1_CS_AS)     
      
    RETURN 0

sp_MSremove_mergereplcommand (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSremove_mergereplcommand(nvarchar @publication
, nvarchar @article
, int @schematype)

MetaData:

   
--  Remove an article command of a specific tyep from dbo.sysmergeschemachange- Used by snapshot  --   
create procedure sys.sp_MSremove_mergereplcommand (  
    @publication        sysname,  
    @article            sysname,  
    @schematype         int  
    ) AS  
    set nocount on  
    declare @pubid              uniqueidentifier  
    declare @artid              uniqueidentifier  
    declare @retcode            int      
  
    --  added for client requested snapshot to work.  
    --  when a non-dbo user comes in only do all checking and subsequent removing if the command exists in the first place.  
    exec @retcode=sys.sp_MSrepl_PAL_rolecheck @publication = @publication  
    if @retcode<>0 or @@ERROR<>0 return (1)  
  
    select @pubid = pubid FROM dbo.sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()  
    select @artid = artid FROM dbo.sysmergeextendedarticlesview WHERE name = @article and pubid=@pubid  
    if not exists(select * from dbo.sysmergeschemachange where pubid = @pubid and artid = @artid and schematype = @schematype)  
        return 0  
  
    exec @retcode=sys.sp_MSreplcheck_publish  
    if @retcode<>0 or @@ERROR<>0 return (1)  
      
    if @publication IS NULL  
    begin  
        raiserror (14003, 16, -1)  
        return (1)  
    end  
      
    if @article IS NULL  
    begin  
        raiserror (20045, 16, -1)  
        return (1)  
    end  
  
    delete dbo.sysmergeschemachange where pubid = @pubid  
                                  and artid = @artid  
                             and schematype = @schematype

sp_MSreleasesnapshotdeliverysessionlock (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSreleasesnapshotdeliverysessionlock()

MetaData:

 --   
--  Name: sp_MSreleasesnapshotdeliverysessionlock  
--   
--  Description: This procedure is used by the distribution/merge agent to  
--               release the application lock acquired through   
--               sp_MSacquiresnapshotdeliverysessionlock.  
--   
--  Returns: 0 - succeeded  
--           1 - failed  
--   
--  Notes: i) This procedure should be executed by the distribution/merge agent  
--         at the subscription database.  
--   
--  Security: Execute permission of this procedure is granted to public;  
--            procedural security check will be performed to make sure  
--            that the caller is either a db_owner of the current database  
--            or a sysadmin.   
--   
create procedure sys.sp_MSreleasesnapshotdeliverysessionlock  
as  
begin  
    set nocount on  
    declare @retcode int  
    declare @resource nvarchar(255)  
    select @retcode = 0  
    exec @retcode = sys.sp_MSreplcheck_subscribe  
    if @retcode <> 0 or @@error <> 0  
    begin  
        select @retcode = 1  
        goto FAILURE  
    end  
    select @resource = N'snapshot_delivery_in_progress_' +   
                       db_name() collate database_default  
    exec @retcode = sys.sp_releaseapplock @Resource = @resource,  
                                          @LockOwner = 'Session',  
                                          @DbPrincipal = N'db_owner'  
  
    if @retcode < 0 or @@error <> 0  
    begin  
        select @retcode = 1  
        goto FAILURE  
    end  
    select @retcode = 0  
FAILURE:  
    return @retcode  
end

sp_MSreleaseSlotLock (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSreleaseSlotLock(nvarchar @process_name
, nvarchar @DbPrincipal)

MetaData:

   
--  this stored procedure is called by merge agent to release the application lock  
--  acquired by sp_MSacquireSlotLock  
  
create procedure sys.sp_MSreleaseSlotLock  
@process_name   sysname,  
@DbPrincipal    sysname = N'public'  
AS  
declare @retcode int  
  
--  Security Checking   
--  sysadmin or db_owner or replication agent have access  
  
exec @retcode = sys.sp_MSrepl_PAL_rolecheck   
if (@retcode <> 0) or (@@error <> 0)  
begin  
        RAISERROR (14126, 11, -1)  
       return 1  
end  
  
exec @retcode=sys.sp_releaseapplock @process_name,@LockOwner=N'Session',@DbPrincipal=@DbPrincipal  
IF (@retcode <> 0)  
    BEGIN  
        RAISERROR(21415, 16, -1)  
        return (1)  
    END  
return @retcode

May 24, 2012

sp_MSquerysubtype (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSquerysubtype(uniqueidentifier @pubid
, nvarchar @subscriber
, nvarchar @subscriber_db)

MetaData:

   
create procedure sys.sp_MSquerysubtype (@pubid uniqueidentifier,@subscriber sysname, @subscriber_db sysname)  
as  
    --  Security check  
    if (1 <> {fn ISPALUSER(@pubid)} and  
        1 <> is_member('db_owner'))  
    begin      
        RAISERROR (15247, 11, -1)  
        return (1)  
    end  
    declare @subtype    int  
    declare @deleted     int  
  
    set @deleted = 2  
      
    select @subtype = NULL  
    select @subtype = subscription_type from dbo.sysmergesubscriptions where pubid=@pubid   
        and db_name=@subscriber_db   
        and UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default   
        and status <> @deleted  
  
    if @subtype is NULL  
        select @subtype=2  
    select @subtype

sp_MSreinitoverlappingmergepublications (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSreinitoverlappingmergepublications(uniqueidentifier @pubid
, bit @upload_before_reinit)

MetaData:

 create procedure sys.sp_MSreinitoverlappingmergepublications  
    @pubid uniqueidentifier,  
    @upload_before_reinit bit  
as  
    declare @publisher sysname  
    declare @publisher_db sysname  
    declare @publication_current sysname  
    declare @pubid_current uniqueidentifier  
    declare @pubid_previous uniqueidentifier  
    declare @retcode int  
  
    exec @retcode = sys.sp_MSreplcheck_publish  
    if @@ERROR <> 0 or @retcode <> 0  
        return(1)  
  
    set @publisher= publishingservername()  
    set @publisher_db= db_name()  
    set @retcode= 0  
  
    while 1=1  
    begin  
        set @pubid_previous= @pubid_current  
        set @pubid_current= null  
          
        select top 1 @publication_current= repub_smp.name,  
                     @pubid_current= repub_smp.pubid  
            from dbo.sysmergearticles as pub_sma   
                 inner join   
                 dbo.sysmergearticles as repub_sma on pub_sma.artid=repub_sma.artid  
                 inner join  
                 dbo.sysmergepublications as repub_smp on repub_sma.pubid=repub_smp.pubid  
            where pub_sma.pubid=@pubid and   
                  pub_sma.pubid<>repub_sma.pubid and  
                  repub_smp.publisher collate database_default=@publisher collate database_default and  
                  repub_smp.publisher_db=@publisher_db and  
                  (repub_smp.pubid > @pubid_previous or @pubid_previous is null)  
            order by repub_smp.pubid asc  
  
        if @pubid_current is null  
        begin  
            break  
        end  
  
        exec @retcode= sys.sp_MSreinitmergepublication  
                                @publication= @publication_current,  
                                @upload_first= @upload_before_reinit  
  
        if @@error<>0 or @retcode<>0 return 1  
  
    end  
  
    return @retcode

 create procedure sys.sp_MSreleasemergeadminapplock  
    @lockowner nvarchar(32) = N'Session'  
as  
begin  
    set nocount on  
  
    declare @retcode smallint  
    declare @lock_resource nvarchar(255)  
    declare @publisher sysname  
    declare @publisher_db sysname  
    declare @DbPrincipal sysname   
  
    select @publisher = publishingservername()   
    select @publisher_db = DB_NAME(DB_ID())   
  
    --    
    --  Security Check.  
     --   
    exec @retcode = sys.sp_MSreplcheck_publish  
    if @retcode<>0 or @@error<>0  
        return 1  
          
    select @retcode = 0  
      
    --  use the dbowner role  
    select @DbPrincipal = N'db_owner'  
    select @lock_resource = N'MSinternal_repl_merge_admin_' + convert(nvarchar(6), db_id())   
  
    exec @retcode = sp_releaseapplock @Resource = @lock_resource, @LockOwner = @lockowner, @DbPrincipal = @DbPrincipal  
    if @@error<>0 or @retcode<0  
        return 1  
    else  
        return 0  
end

 create procedure sys.sp_MSreenable_check @objname sysname, @objowner sysname = NULL  
as  
    set nocount on  
      
    declare @cnstname sysname  
    declare @cnstid int  
    declare @objid int  
    declare @enable_cmd nvarchar(4000)  
    declare @dest nvarchar(514)  
  
    declare @retcode int  
    IF @objname IS NULL  
    BEGIN  
        RAISERROR (14043, 16, -1, '@objname', 'sp_MSreenable_check')  
        RETURN (1)  
    END  
  
    if(@objowner is not null)  
        select @dest = quotename(@objowner) + N'.' + quotename(@objname)  
    else  
        select @dest = quotename(@objname)  
  
    EXEC @retcode = sys.sp_MSreplcheck_publish  
    IF @@ERROR <> 0 or @retcode <> 0  
            return (1)  
  
    select @objid = object_id(@objname)  
      
    declare ms_crs_cnst cursor local static for  
    select name, object_id   
        from sys.objects   
        where parent_object_id = @objid   
            and OBJECTPROPERTY(object_id, 'CnstIsDisabled') = 1   
            and OBJECTPROPERTY(object_id, 'CnstIsNotRepl') = 0   
            and (OBJECTPROPERTY(object_id, 'IsCheckCnst') = 1   
                or OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)  
    union  
    select name, object_id  
        from sys.triggers  
        where parent_id = @objid   
            and OBJECTPROPERTY(object_id, 'IsTrigger') = 1   
            and OBJECTPROPERTY(object_id, 'ExecIsTriggerNotForRepl') = 0   
            and OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled') = 1  
    for read only  
  
    open ms_crs_cnst  
    fetch ms_crs_cnst into @cnstname, @cnstid  
    while @@fetch_status >= 0  
    begin  
        if(ObjectProperty(@cnstid, 'IsTrigger') = 1)  
            select @enable_cmd = N'alter table ' + @dest + N' enable trigger ' + quotename(@cnstname)  
        else  
            select @enable_cmd = N'alter table ' + @dest + N' check constraint ' + quotename(@cnstname)  
  
        execute(@enable_cmd)  
        fetch ms_crs_cnst into @cnstname, @cnstid  
    end     -- of major loop  
    deallocate ms_crs_cnst  
    return 0

   
create procedure sys.sp_MSreleasemakegenerationapplock  
    as  
    set nocount on  
  
    declare @retcode smallint  
    declare @lock_resource nvarchar(255)  
    declare @DbPrincipal sysname  
  
    --  Security check  
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck   
    if (@retcode <> 0) or (@@error <> 0)  
            return 1  
      
    select @retcode = 0  
    select @lock_resource = N'MSinternal_makegeneration_inprogress' +   
                convert(nvarchar(11), db_id())  
                  
    if exists (select * from sys.database_principals where name=N'MSmerge_PAL_role' and type = 'R')  
        select @DbPrincipal = N'MSmerge_PAL_role'  
    else  
        select @DbPrincipal = N'db_owner'  
                  
       exec @retcode = sp_releaseapplock @Resource = @lock_resource, @LockOwner = N'Session', @DbPrincipal = @DbPrincipal  
       if @@error <> 0 or @retcode < 0  
        return (1)      
    else  
        return (0)

 create procedure sys.sp_MSreleasedynamicsnapshotapplock(  
    @publication sysname,  
    @partition_id int)  
as  
begin  
    set nocount on  
  
    declare @retcode smallint  
    declare @lock_resource nvarchar(255)  
    declare @pubnumber smallint  
    declare @publisher sysname  
    declare @publisher_db sysname  
    declare @pubid uniqueidentifier  
    declare @PALRole sysname         
  
    select @publisher = publishingservername()   
    select @publisher_db = DB_NAME(DB_ID())   
  
    --    
    --  Security Check.  
     --   
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck @publication = @publication  
    if @retcode<>0 or @@error<>0  
        return 1  
          
    --  partition id can be >= 0   
    if @partition_id = -1  
        return 0  
          
        select @retcode = 0  
    select @pubnumber = publication_number, @pubid = pubid from dbo.sysmergepublications    
        where name=@publication and upper(publisher)=upper(@publisher) and publisher_db=@publisher_db  
    if @pubnumber is NULL or @pubnumber = 0   
    begin  
        RAISERROR (20026, 16, -1, @publication)  
        return 1  
    end  
  
    select @lock_resource = N'MSinternal_dynamic_snapshot_' +   
                    convert(nvarchar(6), db_id()) + '_' +  
                    convert(nvarchar(6), @pubnumber) + '_' +  
                    convert(nvarchar(11), @partition_id)  
  
    --  Since we have gone through the PAL check already, the PAL role  
    --  should have been set. The following call to fn_MSmerge_GetPALRole   
    --  is nothing more than a way to retrieve the PAL role name  
    select @PALRole = NULL  
    select @PALRole = sys.fn_MSmerge_GetPALRole(@pubid)  
    if @PALRole is NULL  
        return 1  
  
    exec @retcode = sp_releaseapplock @Resource = @lock_resource, @LockOwner = N'Session', @DbPrincipal = @PALRole  
    if @@error <> 0 or @retcode < 0  
        return 1  
    else  
        return 0  
end

C# and I

May 25, 2012

sp_MSremoveoffloadparameter (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSremove_mergereplcommand (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreleasesnapshotdeliverysessionlock (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreleaseSlotLock (Transact-SQL MetaData) Definition

Definition:

MetaData:

May 24, 2012

sp_MSquerysubtype (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreinitoverlappingmergepublications (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreleasemergeadminapplock (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreenable_check (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreleasemakegenerationapplock (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreleasedynamicsnapshotapplock (Transact-SQL MetaData) Definition

Definition:

MetaData:

Total Pageviews

Where i go...