C# and I: 04/13/12

April 13, 2012

sp_addsubscriber_schedule (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_addsubscriber_schedule(nvarchar @subscriber
, smallint @agent_type
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_addsubscriber_schedule (  
    @subscriber sysname,  
    @agent_type smallint = 0,  
    @frequency_type int = 64,  
    @frequency_interval int = 1,  
    @frequency_relative_interval int = 1,  
    @frequency_recurrence_factor int = 0,  
    @frequency_subday int = 4,  
    @frequency_subday_interval int = 5,  
    @active_start_time_of_day int = 0,  
    @active_end_time_of_day int = 235959,  
    @active_start_date int = 0,  
    @active_end_date int = 99991231,  
    @publisher sysname = NULL  
        ) AS  
  
    DECLARE @distributor sysname  
    DECLARE @distribdb sysname  
    DECLARE @distproc nvarchar (300)  
    DECLARE @retcode int  
    DECLARE @msg nvarchar(255)  
  
    --  Heterogeneous publishers  
    DECLARE @publisher_local sysname  
      
    --   
    --  Security Check: require sysadmin  
     --   
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)  
    BEGIN  
        RAISERROR(21089,16,-1)   
        RETURN (1)  
    END  
  
    --   
    --  If @publisher is NULL, set @publisher_local to @@SERVERNAME  
    --  otherwise, set @publisher_local to @publisher  
     --   
    IF @publisher IS NULL  
    BEGIN  
        select @publisher_local = publishingservername()  
    END   
    ELSE  
    BEGIN  
    --  Check if publisher exists  --   
      EXEC @retcode = sys.sp_MSrepl_getpublisherinfo    @publisher        = @publisher      
    IF @retcode <> 0  
        RETURN (@retcode)  
    --  For heterogeneous publisher, the current database must be the  --   
    --  distribution db for the publisher  --   
  
        select @publisher_local = @publisher  
  
        IF NOT EXISTS ( select * from master.dbo.sysservers s, msdb..MSdistpublishers m  
        where UPPER(s.srvname collate database_default) = UPPER(m.name collate database_default)  
        and UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default  
        and UPPER(m.distribution_db collate database_default) = UPPER(DB_NAME()) collate database_default  
        )  
        RETURN (1)     
      
    IF @@ERROR <> 0   
        RETURN (1)  
      
    END   
  
    --   
    --  Parameter Check:  @subscriber.  
    --  Check to make sure that the subscriber doesn't already exist, and  
    --  that the name is a valid non-null identifier.  
     --   
  
    IF @subscriber IS NULL  
        BEGIN  
            RAISERROR (14043, 16, -1, '@subscriber', 'sp_addsubscriber_schedule')  
            RETURN (1)  
        END  
  
    EXECUTE @retcode = sys.sp_validname @subscriber  
  
    IF @@ERROR <> 0 OR @retcode <> 0  
    RETURN (1)  
  
    if LOWER(@subscriber) = 'all'  
        BEGIN  
            RAISERROR (14032, 16, -1, '@subscriber')  
            RETURN (1)  
        END  
  
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,  
                                                       @distribdb = @distribdb   OUTPUT,  
                                                       @publisher = @publisher  
  
    IF @@error <> 0  
        BEGIN  
         RAISERROR (14071, 16, -1)  
         RETURN (1)  
     END  
  
    IF @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL  
        BEGIN  
            RAISERROR (14071, 16, -1)  
            RETURN (1)  
        END  
      
    SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSadd_subscriber_schedule'  
    EXEC @retcode = @distproc  
         @publisher_local,  
         @subscriber,  
         @agent_type,  
         @frequency_type,  
         @frequency_interval,  
         @frequency_relative_interval,  
         @frequency_recurrence_factor,  
         @frequency_subday,  
         @frequency_subday_interval,  
         @active_start_time_of_day,  
         @active_end_time_of_day,  
         @active_start_date,  
         @active_end_date  
      
    IF @@error <> 0 OR @retcode <> 0  
        BEGIN  
        RAISERROR (14042, 16, -1)  
        RETURN (1)  
    END  
  
    RETURN (0)

sp_addsubscriber (Transact-SQL MetaData) Definition

Definition:

sys.sp_addsubscriber(nvarchar @subscriber
, tinyint @type
, nvarchar @login
, nvarchar @password
, int @commit_batch_size
, int @status_batch_size
, int @flush_frequency
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date
, nvarchar @description
, int @security_mode
, bit @encrypted_password
, nvarchar @publisher)

MetaData:

   
create procedure sys.sp_addsubscriber  
(  
    @subscriber                        sysname,  
    @type                            tinyint = 0,  
    @login                            sysname = NULL,  
    @password                        nvarchar(524) = NULL,  
    @commit_batch_size                int = NULL,  
    @status_batch_size                int = NULL,  
    @flush_frequency                int = NULL,  
    @frequency_type                    int = NULL,  
    @frequency_interval                int = NULL,  
    @frequency_relative_interval    int = NULL,  
    @frequency_recurrence_factor    int = NULL,  
    @frequency_subday                int = NULL,  
    @frequency_subday_interval        int = NULL,  
    @active_start_time_of_day        int = NULL,  
    @active_end_time_of_day            int = NULL,  
    @active_start_date                int = NULL,  
    @active_end_date                int = NULL,  
    @description                    nvarchar (255) = NULL,  
    @security_mode                    int = NULL,  --  backward compatible  --  --  0 standard; 1 integrated  --   
    @encrypted_password                bit = NULL,  
    @publisher                        sysname = NULL  
)  
AS  
BEGIN  
    DECLARE @cmd            nvarchar(4000)  
    DECLARE @retcode        int  
    DECLARE @publisher_type    sysname  
  
    --   
    --  Security Check: require sysadmin  
     --   
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)  
    BEGIN  
        RAISERROR(21089,16,-1)   
        RETURN (1)  
    END  
  
    SET @retcode = 0  
      
    EXEC @retcode = sys.sp_MSrepl_getpublisherinfo    @publisher        = @publisher,  
                                                    @rpcheader        = @cmd            OUTPUT,  
                                                    @publisher_type    = @publisher_type OUTPUT  
  
                                                          
    IF @retcode <> 0  
        RETURN (@retcode)  
  
    -- when called on mirror server, this adds original publiser name   
    -- in distribution. (MSsubscriber_info and MSsubscriber_schedule)  
    IF @publisher IS NULL  
        SET @publisher = publishingservername()   
  
    --  Add sp  
    SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT  
    set @cmd = @cmd + N'sys.sp_MSrepl_addsubscriber'  
      
    EXEC @retcode = @cmd  
                    @subscriber,  
                    @type,  
                    @login,  
                    @password,  
                    @commit_batch_size,  
                    @status_batch_size,  
                    @flush_frequency,  
                    @frequency_type,  
                    @frequency_interval,  
                    @frequency_relative_interval,  
                    @frequency_recurrence_factor,  
                    @frequency_subday,  
                    @frequency_subday_interval,  
                    @active_start_time_of_day,  
                    @active_end_time_of_day,  
                    @active_start_date,  
                    @active_end_date,  
                    @description,  
                    @security_mode,  
                    @encrypted_password,  
                    @publisher,  
                    @publisher_type,  
                    N'PRE-YUKON'  
  
    RETURN (@retcode)  
END

sp_addsrvrolemember (Transact-SQL MetaData) Definition

Definition:

sys.sp_addsrvrolemember(nvarchar @loginame
, nvarchar @rolename)

MetaData:

 --  FOR BACKWARD COMPATIBILTY ONLY --   
create procedure sys.sp_addsrvrolemember  
    @loginame sysname,            --  login name  
    @rolename sysname = NULL    --  server role name  
as  
    --  SETUP RUNTIME OPTIONS / DECLARE VARIABLES --   
    set nocount on  
    declare @ret        int,    --  return value of sp call  
            @stmtR        nvarchar(4000)  
  
    --  Was a part of check to prevent inside a user Xact. Leave for backward compat.--   
    set implicit_transactions off  
  
    exec @ret = sys.sp_validname @rolename  
    if @ret <> 0  
        return(1)  
  
    exec @ret = sys.sp_validname @loginame  
    if @ret <> 0  
        return(1)  
  
    BEGIN TRANSACTION  
        --  LOCK LOGIN --   
        EXEC %%LocalLogin(Name = @loginame).Lock(Exclusive = 1)  
  
        --  ADD ROW FOR NT LOGIN IF NEEDED --   
        if @@error <> 0    --  not found  
        begin  
            execute @ret = sys.sp_MSaddlogin_implicit_ntlogin @loginame  
            if (@ret <> 0)  
            begin  
                ROLLBACK TRANSACTION  
                raiserror(15007,-1,-1,@loginame)  
                return (1)  
            end  
            --  login locked  
        end  
  
        set @stmtR = 'alter server role '  
        set @stmtR = @stmtR + quotename(@rolename, ']')  
        set @stmtR = @stmtR + ' add member '  
        set @stmtR = @stmtR + quotename(@loginame, ']')  
  
        --  issue DDL to create the role --   
        exec (@stmtR)      
        if @@error <> 0  
        begin  
            ROLLBACK TRANSACTION  
            return (1)  
        end  
  
    --  RETURN SUCCESS --   
    COMMIT TRANSACTION  
    return (0) --  sp_addsrvrolemember

sp_addserver (Transact-SQL MetaData) Definition

Definition:

sys.sp_addserver(nvarchar @server
, varchar @local
, varchar @duplicate_ok)

MetaData:

 create procedure sys.sp_addserver  
    @server            sysname,            --  server name  
    @local            varchar(10) = NULL,    --  NULL or 'local'  
    @duplicate_ok    varchar(13) = NULL    --  NULL or 'duplicate_ok'  
as  
    --  VARS  
    DECLARE @localentry    bit,  
            @dup_ok        bit,  
            @retcode    int  
    select @duplicate_ok = LOWER (@duplicate_ok collate Latin1_General_CI_AS)  
  
    --  VALIDATE PARAMETERS/OPTIONS  
    SELECT    @localentry = CASE WHEN @local IS NULL THEN 0  
                        WHEN lower(@local) = 'local' THEN 1  
                        ELSE NULL END,  
            @dup_ok = CASE WHEN @duplicate_ok IS NULL THEN 0  
                        WHEN @duplicate_ok = 'duplicate_ok' THEN 1  
                        ELSE NULL END  
    IF @localentry IS NULL OR @dup_ok IS NULL  
    BEGIN  
        raiserror(15600,-1,-1,'sys.sp_addserver')  
        return (1)  
    END  
  
    --  DISALLOW USER TRANSACTION  
    set implicit_transactions off  
    if @@trancount > 0  
    begin  
        raiserror(15002,-1,-1,'sys.sp_addlinkedserver')  
        return (1)  
    end  
  
    BEGIN TRANSACTION  
  
    --  ADD THE SERVER (CHECKS PERMISSIONS, ETC)  
    EXEC @retcode = sys.sp_MSaddserver_internal @server,  
                NULL, NULL, NULL, NULL, NULL, NULL, --  @srvproduct ... @catalog  
                0,            --  @linkedstyle  
                @localentry  
  
    if( @retcode = 0)  
    begin  
        if (@localentry = 1)  
        begin  
            --  EMDEventType(x_eet_Alter_Instance), EMDUniversalClass(x_eunc_Server), src major id, src minor id, src name  
            --  -1 means ignore target stuff, target major id, target minor id, target name,  
            --  # of parameters, 5 parameters  
            EXEC %%System().FireTrigger(ID = 214, ID = 100, ID = 0, ID = 0, Value = NULL,  
                ID = -1, ID = 0, ID = 0, Value = NULL,   
                ID = 3, Value = @server, Value = @local, Value = @duplicate_ok, Value = NULL, Value = NULL, Value = NULL, Value = NULL)  
        end  
        else  
        begin  
            --  EMDEventType(x_eet_Create_Remote_Server), EMDUniversalClass(x_eunc_Server), src major id, src minor id, src name  
            --  -1 means ignore target stuff, target major id, target minor id, target name,  
            --  # of parameters, 5 parameters  
            EXEC %%System().FireTrigger(ID = 230, ID = 100, ID = 0, ID = 0, Value = @server,  
                ID = -1, ID = 0, ID = 0, Value = NULL,   
                ID = 3, Value = @server, Value = @local, Value = @duplicate_ok, Value = NULL, Value = NULL, Value = NULL, Value = NULL)  
        end  
  
        COMMIT TRANSACTION  
        --  SUCCESS  
        return (0) --  sp_addserver  
    end  
    else if( @retcode = 2 )  
    begin  
        ROLLBACK  
  
        if @dup_ok = 1  
            return (0)  
        else  
            begin  
                raiserror(15028,-1,-1,@server);  
                return (1);  
            end;  
    end  
    else if( @retcode = 3 )  
    begin  
        ROLLBACK  
  
        raiserror(28401,16,20,'sp_addserver');  
        return (1)  
    end  
    else   
    begin  
        ROLLBACK  
        return (1)  
    end

sp_addscriptexec (Transact-SQL MetaData) Definition

Definition:

sys.sp_addscriptexec(nvarchar @publication
, nvarchar @scriptfile
, bit @skiperror
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_addscriptexec  
(  
    @publication    sysname,  
    @scriptfile        nvarchar (4000),  
    @skiperror        bit = 0,  
    @publisher        sysname = NULL  
)  
AS  
BEGIN  
    DECLARE @cmd        nvarchar(4000)  
    DECLARE @retcode    int  
  
    SET @retcode = 0  
      
    EXEC @retcode = sys.sp_MSrepl_getpublisherinfo    @publisher    = @publisher,  
                                                    @rpcheader    = @cmd OUTPUT  
      
    IF @retcode <> 0  
        RETURN (@retcode)  
  
    --  Add sp  
    SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT  
    set @cmd = @cmd + N'sys.sp_MSrepl_addscriptexec'  
  
    EXEC @retcode = @cmd  
                    @publication,  
                    @scriptfile,  
                    @skiperror  
  
    RETURN (@retcode)  
END

sp_addrolemember (Transact-SQL MetaData) Definition

Definition:

sys.sp_addrolemember(nvarchar @rolename
, nvarchar @membername)

MetaData:

 --  FOR BACKWARD COMPATIBILTY ONLY --   
create procedure sys.sp_addrolemember  
    @rolename       sysname,  
    @membername     sysname  
AS  
    --  SETUP RUNTIME OPTIONS / DECLARE VARIABLES --   
    set nocount on  
    declare @ret        int,    --  return value of sp call  
            @memuid     int,  
            @stmtR        nvarchar(4000)  
  
    --  Was a part of check to prevent inside a user Xact. Leave for backward compat.--   
    set implicit_transactions off  
  
    exec @ret = sys.sp_validname @rolename  
    if @ret <> 0  
        return(1)  
  
    exec @ret = sys.sp_validname @membername  
    if @ret <> 0  
        return(1)  
  
    BEGIN TRANSACTION  
  
        --  EXCL LOCK MEMBER --   
        EXEC %%Owner(Name = @membername).Lock(Exclusive = 1) --  may fail  
  
        --  ATTEMPT ADDING IMPLICIT ROW FOR NT NAME --   
        if @@error <> 0    --  not found  
        begin  
            EXEC @ret = sys.sp_MSadduser_implicit_ntlogin @membername  
            if (@ret <> 0)  
            begin  
                ROLLBACK TRANSACTION  
                raiserror(15410, -1, -1, @membername)  
                return (1)  
            end  
            --  Member locked by sp_MSadduser_implicit_ntlogin  
        end  
  
        set @stmtR = 'alter role '  
        set @stmtR = @stmtR + quotename(@rolename, ']')  
        set @stmtR = @stmtR + ' add member '  
        set @stmtR = @stmtR + quotename(@membername, ']')  
  
        --  issue DDL to create the role --   
        exec (@stmtR)      
        if @@error <> 0  
        begin  
            ROLLBACK TRANSACTION  
            return (1)  
        end  
  
    --  RETURN SUCCESS --   
    COMMIT TRANSACTION  
    return (0) --  sp_addrolemember

sp_addrole (Transact-SQL MetaData) Definition

Definition:

sys.sp_addrole(nvarchar @rolename
, nvarchar @ownername)

MetaData:

 create procedure sys.sp_addrole  
    @rolename   sysname,        --  name of new role  
    @ownername  sysname = NULL  --  name of owner of new role  
as  
    --  SETUP RUNTIME OPTIONS / DECLARE VARIABLES --   
    set nocount on  
    declare @ret        int,    --  return value of sp call  
            @owner      int,  
            @stmtR        nvarchar(4000),  
            @stmtS        nvarchar(4000)  
  
    --  DISALLOW USER TRANSACTION --   
    set implicit_transactions off  
    if (@@trancount > 0)  
    begin  
        raiserror(15002,-1,-1,'sys.sp_addrole')  
        return (1)  
    end  
  
    exec @ret = sys.sp_validname @rolename  
    if @ret <> 0  
        return(1)  
  
    if @ownername is null  
        select @ownername = user_name()  
  
    set @stmtR = 'create role '  
    set @stmtR = @stmtR + quotename(@rolename, ']')  
    set @stmtR = @stmtR + ' authorization '  
    set @stmtR = @stmtR + quotename(@ownername, ']')  
  
    --  Create Schema if not one exists with same name and owner  
    set @stmtS = 'create schema '  
    set @stmtS = @stmtS + quotename(@rolename, ']')  
    set @stmtS = @stmtS + ' authorization '  
    set @stmtS = @stmtS + quotename(@rolename, ']')  
      
    BEGIN TRANSACTION  
  
    --  issue DDL to create the role --   
    exec (@stmtR)      
    if @@error <> 0  
    begin  
        ROLLBACK TRANSACTION  
        return (1)  
    end  
  
  
    --  create the Schema if not one exists with same name and owner  
    declare @schema_uid int  
    declare @owner_uid  int  
    select @owner_uid  = principal_id from sys.database_principals where name = @rolename  
    select @schema_uid = principal_id from sys.schemas where name = @rolename  
    if (@schema_uid is null or        --  there is no schema since if there is one, it will have an owner  
        @schema_uid <> @owner_uid)  --  for error message  
    begin  
        exec (@stmtS)  
        if @@error <> 0  
        begin  
            ROLLBACK TRANSACTION  
            return (1)  
        end  
    end  
  
    COMMIT TRANSACTION  
  
    --  RETURN SUCCESS --   
    return (0) --  sp_addrole

sp_addremotelogin (Transact-SQL MetaData) Definition

Definition:

sys.sp_addremotelogin(nvarchar @remoteserver
, nvarchar @loginame
, nvarchar @remotename)

MetaData:

 create procedure sys.sp_addremotelogin  
    @remoteserver    sysname,            --  name of remote server  
    @loginame        sysname = NULL,        --  user's local user name  
    @remotename        sysname = NULL        --  user's remote name  
as  
    declare @localid int  
  
    --  DISALLOW USER XACT --   
    set implicit_transactions off  
    if @@trancount > 0  
    begin  
        raiserror(15002,-1,-1,'sys.sp_addremotelogin')  
        return (1)  
    end  
  
    --  CHECK PERMISSIONS  
    if not (has_perms_by_name(null, null, 'alter any login') = 1)  
    begin  
        EXEC %%System().AuditEvent(ID = 1380142162, Success = 0, TargetLoginName = @loginame, TargetUserName = NULL, Role = NULL, Object = @remotename, Provider = NULL, Server = @remoteserver)  
        raiserror(15247,-1,-1)  
        return (1)  
    end  
    else  
        EXEC %%System().AuditEvent(ID = 1380142162, Success = 1, TargetLoginName = @loginame, TargetUserName = NULL, Role = NULL, Object = @remotename, Provider = NULL, Server = @remoteserver)  
  
    --  CHECK FOR INVALID PARAMETER SYNTAX --   
    if @remoteserver is null OR (@loginame is null and @remotename is not null)  
    begin  
        raiserror(15600,-1,-1,'sys.sp_addremotelogin')  
        return (1)  
    end  
  
    BEGIN TRAN  
  
    --  VALIDATE/LOCK SERVER NAME --   
    EXEC %%LinkedServer ( Name = @remoteserver ) . Lock ( Exclusive = 1 )  
    IF @@ERROR <> 0  
    begin  
        ROLLBACK TRAN  
        raiserror(15015,-1,-1,@remoteserver)  
        return (1)  
    end  
  
    --  VALIDATE/LOCK @loginame --   
    select @localid = 0  
    if @loginame is not null  
    begin  
        --  share-lock the local login  
        EXEC %%LocalLogin ( Name = @loginame ) . Lock ( Exclusive = 0 )  
        IF @@ERROR = 0  
            select @localid = principal_id from sys.server_principals  
                where name = @loginame and type = 'S'        --  cannot map to NT login  
        if @localid = 0  
        begin  
            ROLLBACK TRAN  
            raiserror(15007,-1,-1,@loginame)  
            return (1)  
        end  
    end  
  
    --  ADD NEW REMOTE-LOGIN (WILL CHECK FOR DUPLICATE <@remoteserver, @remotename> PAIR)  
    EXEC %%LinkedServer(Name=@remoteserver).NewRemoteLogin( RemoteName = @remotename, LocalID = @localid )  
    IF @@ERROR <> 0  
    begin  
        ROLLBACK TRAN  
        if @remotename is null  
            raiserror(15066,-1,-1,@remoteserver)  
        else  
            raiserror(15068,-1,-1,@remotename,@remoteserver)  
        return (1)  
    end  
  
    --  SUCCESS --   
    COMMIT TRAN  
    return (0)    --  sp_addremotelogin

sp_addpullsubscription_agent (Transact-SQL MetaData) Definition

Definition:

sys.sp_addpullsubscription_agent(nvarchar @job_password
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, int @subscriber_security_mode
, nvarchar @subscriber_login
, nvarchar @subscriber_password
, nvarchar @distributor
, nvarchar @distribution_db
, int @distributor_security_mode
, nvarchar @distributor_login
, nvarchar @distributor_password
, nvarchar @optional_command_line
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date
, bit @encrypted_distributor_password
, nvarchar @enabled_for_syncmgr
, nvarchar @ftp_address
, int @ftp_port
, nvarchar @ftp_login
, nvarchar @ftp_password
, nvarchar @alt_snapshot_folder
, nvarchar @working_directory
, nvarchar @use_ftp
, tinyint @publication_type
, nvarchar @dts_package_name
, nvarchar @dts_package_password
, nvarchar @dts_package_location
, nvarchar @reserved
, nvarchar @offloadagent
, nvarchar @offloadserver
, nvarchar @job_name
, nvarchar @job_login)

MetaData:

   
--   
--  Name: sp_addpullsubscription_agent  
--   
--  Descriptions:   
--   
--  Parameters: as defined in create statement  
--   
--  Returns: 0 - success  
--           1 - Otherwise  
--   
--  Security:   
--  Requires Certificate signature for catalog access  
--   
create procedure sys.sp_addpullsubscription_agent  
(  
    @publisher                        sysname,  
    @publisher_db                   sysname = NULL,  
    @publication                    sysname,  
    @subscriber                        sysname = NULL,  
    @subscriber_db                  sysname = NULL,  
    @subscriber_security_mode       int = NULL,                --  0 standard; 1 integrated  
    @subscriber_login               sysname = NULL,  
    @subscriber_password            sysname = NULL,  
    @distributor                    sysname = @publisher,  
    @distribution_db                sysname = NULL,  
    @distributor_security_mode        int = 1,  
    @distributor_login                sysname = NULL,  
    @distributor_password            sysname = NULL,  
    @optional_command_line            nvarchar(4000) = '',  
    @frequency_type                 int = 2,                --  2 == OnDemand  
    @frequency_interval                int = 1,   
    @frequency_relative_interval    int = 1,   
    @frequency_recurrence_factor    int = 1,   
    @frequency_subday                int = 1,   
    @frequency_subday_interval        int = 1,  
    @active_start_time_of_day        int = 0,   
    @active_end_time_of_day            int = 0,           
    @active_start_date                int = 0,   
    @active_end_date                int = 0,  
    @distribution_jobid                binary(16) = NULL OUTPUT,  
    @encrypted_distributor_password    bit = 0,  
    @enabled_for_syncmgr            nvarchar(5) = 'false',    --  Enabled for SYNCMGR: true or false  
    @ftp_address                    sysname = NULL,  
    @ftp_port                        int = NULL,  
    @ftp_login                        sysname = NULL,  
    @ftp_password                   sysname = NULL,  
    @alt_snapshot_folder            nvarchar(255) = NULL,  
    @working_directory                nvarchar(255) = NULL,  
    @use_ftp                        nvarchar(5) = 'false',  
    @publication_type                tinyint = 0,            --  0 - Transactional, 1 - Snapshot, 2 - Merge  
    @dts_package_name                sysname = NULL,            --  value will be sent and validated at distributor  
    @dts_package_password            sysname = NULL,  
    @dts_package_location            nvarchar(12) = N'subscriber',  
    @reserved                        nvarchar(100) = N'',    --  Not default to null because null problems in conditional expressions.  
    @offloadagent                    nvarchar(5) = 'false',  
    @offloadserver                    sysname = NULL,  
    @job_name                        sysname = NULL,  
    --  used for jobstep level proxy accounts  
    @job_login                         nvarchar(257) = NULL,  
    @job_password                     sysname = NULL  
)  
AS  
BEGIN  
    SET NOCOUNT ON  
  
    --   
    --  Declarations.  
     --   
  
    DECLARE @command                    nvarchar(4000)  
                ,@retcode                    int  
                ,@subscription_type_id       int                    --  1 = pull, 2 = anonymous  
                ,@independent_agent_id       bit  
                ,@distribution_agent         nvarchar(100)   
                ,@category_name              sysname  
                ,@platform_nt                binary  
                ,@subscriber_enc_password    nvarchar(524)  
                ,@distributor_enc_password   nvarchar(524)  
                ,@use_ftp_bit                bit  
                ,@distribution_job_step_uid  uniqueidentifier  
                ,@edition_id int  
  
    select @platform_nt = 0x1  
            ,@distribution_job_step_uid = NULL  
            ,@edition_id = sys.fn_MSrepl_editionid ()  
  
    --   
    --  Security Check  
     --   
  
    EXEC @retcode = sys.sp_MSreplcheck_subscribe  
    IF @@ERROR <> 0 or @retcode <> 0  
        RETURN(1)  
      
    --   
    --  Initializations.  
     --   
  
    --  Set null @optional_command_line to empty string to avoid string concat problem  
    if @optional_command_line is null  
        set @optional_command_line = ''  
    else  
        set @optional_command_line = N' ' + LTRIM( RTRIM(@optional_command_line) ) + N' '  
  
  
    IF @distributor_password = N''  
        select @distributor_password = NULL  
  
    IF @ftp_password = N''  
        select @ftp_password = NULL  
  
    IF @dts_package_password = N''  
        select @dts_package_password = NULL  
  
    --  @dts_package_password cannot be non-null if @dts_package_name is not set  
    if (@dts_package_name is null or rtrim(@dts_package_name) = N'') and  
        @dts_package_password is not null  
    begin  
        raiserror(18780, 16, -1)  
        return (1)  
    end  
      
    IF RTRIM(ISNULL(@dts_package_name, N'')) != N''  
        AND RTRIM(ISNULL(@dts_package_password, N'')) = N''  
    BEGIN  
        --  Use of DTS packages in replication requires a non-NULL/non-empty string password. Specify a valid value for parameter '%s'.  
        RAISERROR(21732,16, -1, '@dts_package_password')  
        RETURN (1)  
    END  
  
    --   
    --  Parameter Check: @publisher  
    --  Check to make sure that the publisher is define  
     --   
    IF @publisher IS NULL  
    BEGIN  
        RAISERROR (14043, 16, -1, '@publisher', 'sp_addpullsubscription_agent')  
        RETURN (1)  
    END  
  
    EXECUTE @retcode = sys.sp_validname @publisher  
  
    IF @@ERROR <> 0 OR @retcode <> 0  
       RETURN (1)  
  
    --  ensure that the distributor is set to a value...   
    --  we will default to publisher since this is sp default  
    SELECT @distributor = ISNULL(@distributor, @publisher)  
      
    --   
    --  Parameter Check: @publisher_db  
     --   
    IF @publisher_db = 'all'  
    BEGIN  
        RAISERROR (14136, 16, -1)  
        RETURN (1)  
    END  
  
    IF @publisher_db IS NOT NULL  
    BEGIN  
        EXECUTE @retcode = sys.sp_validname @publisher_db  
        IF @@ERROR <> 0 OR @retcode <> 0  
            RETURN (1)  
    END  
    ELSE  
    BEGIN  
        --  @publisher_db is NULL for Oracle publishers only  
        SET @publisher_db = @publisher  
    END  
  
    --   
    --  Parameter Check: @publication  
    --    
     --   
    IF @publication IS NULL  
    BEGIN  
        RAISERROR (14043, 16, -1, '@publication', 'sp_addpullsubscription_agent')  
        RETURN (1)  
    END  
  
    EXECUTE @retcode = sys.sp_validname @publication  
  
    IF @@ERROR <> 0 OR @retcode <> 0  
       RETURN (1)  
  
    --   
    --  Parameter Check: @subscriber and @subscriber_db  
     --   
  
    IF @subscriber IS NOT NULL   
        OR @subscriber_db IS NOT NULL  
    BEGIN  
        --  The @subscriber and @subscriber_db parameter(s) have been deprecated and should no longer be used. See the 'sp_addpullsubscription_agent' documentation for more information.  
        RAISERROR(21827, 10, -1, '@subscriber and @subscriber_db', 'sp_addpullsubscription_agent')  
    END  
      
    SELECT @subscriber = @@SERVERNAME,  
            @subscriber_db = DB_NAME()  
      
    EXECUTE @retcode = sys.sp_validname @subscriber  
    IF @@ERROR <> 0 OR @retcode <> 0  
       RETURN (1)  
      
    EXECUTE @retcode = sys.sp_validname @subscriber_db  
    IF @@ERROR <> 0 OR @retcode <> 0  
       RETURN (1)  
      
    --    
    --  Check to see if MSreplictaion_subscriptions table exists.  
    --  If so, copy it into the temp table  
     --   
    IF object_id('MSreplication_subscriptions', 'U') is NULL  
    BEGIN  
        RAISERROR (20017, 16, -1)  
        RETURN (1)  
    END   
      
    --   
    --  Check to make sure that the subscription does exist  
     --   
    IF NOT EXISTS (SELECT * FROM  MSreplication_subscriptions  
                WHERE UPPER(publisher) = UPPER(@publisher) AND  
                      publisher_db  = @publisher_db AND  
                      publication = @publication)  
    BEGIN  
        RAISERROR (20017, 16, -1)  
        RETURN (1)  
    END  
      
    declare @update_mode_id int  
    SELECT  @distribution_agent = NULL  
    SELECT  @independent_agent_id = independent_agent,   
            @subscription_type_id = subscription_type,  
            @distribution_agent = distribution_agent,  
            @update_mode_id = update_mode  
        FROM  MSreplication_subscriptions  
        WHERE UPPER(publisher) = UPPER(@publisher) AND  
              publisher_db  = @publisher_db AND  
              publication = @publication  
    --  Distribution agent for push subscriptions is at distributor side  --   
    IF @subscription_type_id = 0  
    BEGIN  
        RAISERROR (21001, 16, -1)  
        RETURN (1)  
    END  
  
    IF @distribution_agent IS NOT NULL  
    BEGIN  
        RAISERROR (21002, 11, -1, @distribution_agent)  
        RETURN (1)  
    END  
      
    --  Parameter check: @subscriber_security_mode, @subscriber_login, @subscriber_password   
    IF (@subscriber_security_mode IS NOT NULL  
        AND @subscriber_security_mode <> 1)  
        OR @subscriber_login IS NOT NULL  
        OR @subscriber_password IS NOT NULL  
    BEGIN  
        --  The @subscriber_security_mode, @subscriber_login and @subscriber_password parameter(s) have been deprecated and should no longer be used. See the 'sp_addpullsubscription_agent' documentation for more information.  
        RAISERROR(21827, 10, -1, '@subscriber_security_mode, @subscriber_login and @subscriber_password', 'sp_addpullsubscription_agent')  
    END  
  
    --  if we do not require yukon security then check paramters.  
    IF sys.fn_yukonsecuritymodelrequired(NULL) = 0  
    BEGIN  
        IF @job_login IS NULL  
        BEGIN  
            IF @job_password IS NOT NULL  
            BEGIN  
                --  Parameter '@job_login' can be set to 'NULL' only when '@job_password' is set to 'NULL'.  
                RAISERROR(21678, 16, -1, '@job_login', 'NULL', '@job_password', 'NULL')  
                RETURN 1  
            END  
        END  
        ELSE  
        BEGIN  
            IF @job_password IS NULL  
            BEGIN  
                --  Parameter '@job_password' can be set to 'NULL' only when '@job_login' is set to 'NULL'.  
                RAISERROR(21678, 16, -1, '@job_password', 'NULL', '@job_login', 'NULL')  
                RETURN 1  
            END  
  
            IF @edition_id <> 40  and @edition_id <> 22  
                AND sys.fn_replisvalidwindowsloginformat(@job_login) != 1  
            BEGIN  
                --  '@job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addpullsubscription_agent'.  
                RAISERROR(21797, 16, -1, '@job_login', 'sp_addpullsubscription_agent')  
                RETURN 1  
            END  
        END  
    END  
    --  if yukon sec required then a valid windows login/password is required  
    ELSE  
    BEGIN  
        IF @job_login IS NULL  
            OR @job_password IS NULL  
        BEGIN  
            --  Only members of the sysadmin fixed server role can perform this operation without specifying @job_login or @job_password.  
            RAISERROR(21832, 16, -1, '@job_login or @job_password')  
            RETURN 1  
        END  
  
        IF @edition_id <> 40 and @edition_id <> 22   
            AND sys.fn_replisvalidwindowsloginformat(@job_login) != 1  
        BEGIN  
            --  '@job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addpullsubscription_agent'.  
            RAISERROR(21797, 16, -1, '@job_login', 'sp_addpullsubscription_agent')  
            RETURN 1  
        END  
    END  
  
  
    --  Subscriber must be Windows Authentication  
    SELECT @subscriber_security_mode = 1,  
            @subscriber_login = N'',  
            @subscriber_password = N'-- -- -- -- -- ',  
            @subscriber_enc_password = @subscriber_password  
  
    EXEC @retcode = sys.sp_MSreplencrypt @subscriber_enc_password OUTPUT  
    IF @@ERROR <> 0 OR @retcode <> 0  
        RETURN 1  
          
    --  default to Windows Auth.  
    SELECT @distributor_security_mode = ISNULL(@distributor_security_mode, 1)  
      
    --  check security mode  
    IF @distributor_security_mode not in (0, 1)  
    BEGIN  
        --  The specified '@distributor_security_mode' is invalid (valid values are: 0, 1).  
        RAISERROR(14266, 16, -1, '@distributor_security_mode', '0, 1')  
        RETURN 1  
    END  
  
    --  make sure that the login is valid  
    SELECT @distributor_login = RTRIM(LTRIM(ISNULL(@distributor_login, '')))  
  
    --  Security Mode = 1  
    IF @distributor_security_mode = 1  
    BEGIN  
        SELECT @distributor_login = '',  
                @distributor_password = newid()  
    END  
    --  Security Mode = 0  
    ELSE IF @distributor_login = ''  
    BEGIN  
  
        --  '@distributor_login cannot be null or empty when @distributor_security_mode is set to 0 (SQL Server authentication).'  
        RAISERROR(21694, 16, -1, '@distributor_login', '@distributor_security_mode')  
        RETURN 1  
    END  
  
    SELECT @distributor_enc_password = @distributor_password  
          
    EXEC @retcode = sys.sp_MSreplencrypt @distributor_enc_password OUTPUT  
    IF @@ERROR <> 0 OR @retcode <> 0  
        RETURN 1  
      
    IF object_id('MSsubscription_properties','U') is NULL  
    begin  
        exec @retcode = sys.sp_MScreate_sub_tables_internal @property_table = 1  
        if @retcode <> 0 or @@error <> 0  
        return (1)  
    end  
  
    --    
    --  Parameter check: @alt_snapshot_folder   
    --  @alt_snapshot_folder and @use_ftp are mutually exclusive      
     --   
  
    IF @alt_snapshot_folder <> N'' AND @alt_snapshot_folder IS NOT NULL  
       AND LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = N'true'  
    BEGIN  
        RAISERROR(21146, 16, -1)  
        RETURN (1)  
    END  
  
    --    
    --  Parameter check: @use_ftp  
    --  Must be 'true' or 'false'  
     --   
    IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')  
    BEGIN  
        RAISERROR (14148, 16, -1, '@use_ftp')  
        RETURN (1)  
    END  
      
    IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = 'true'  
    BEGIN  
        SELECT @use_ftp_bit = 1  
    END  
    ELSE  
    BEGIN  
        SELECT @use_ftp_bit = 0  
    END  
  
  
    --   
    --  Parameter check: @publication_type  
    --  Must be 0 - Transactional or 1 - Snapshot  
     --   
    IF @publication_type NOT IN (0, 1)  
    BEGIN  
        RAISERROR (20033, 16, -1)  
        RETURN (1)  
    END  
  
    --   
    --  Parameter Check: @dts_package_location  
    --  Valid values:  
    --  distributor  
    --  subscriber  
    --   
     --   
    IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('distributor', 'subscriber')  
    BEGIN  
        RAISERROR(21179, 16, -1)      
        RETURN (1)  
    END  
  
    declare @dts_package_location_id int  
  
    IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = 'distributor'  
        SELECT @dts_package_location_id = 0  
    ELSE   
        SELECT @dts_package_location_id = 1  
  
    --  Have to be a push, non updatable  subscription to set DTS package name  
    if @dts_package_name is not null  
    begin  
        if  @update_mode_id != 0  
        begin  
            RAISERROR(21180, 16, -1)      
            RETURN (1)  
        end  
    end  
      
    --  Copy the passwords to new value before attempting to encrypt  
    --  We no longer supported passing in encrypted passwords  
    IF @encrypted_distributor_password = 1  
    BEGIN  
        --  Parameter '@encrypted_distributor_password' is no longer supported.  
        RAISERROR(21698, 16, -1, '@encrypted_distributor_password')  
        RETURN (1)  
    END  
      
    declare @dts_package_enc_password nvarchar(524)  
    set @dts_package_enc_password = @dts_package_password  
  
    if @dts_package_enc_password is not null  
    begin  
        EXEC @retcode = sys.sp_MSreplencrypt @dts_package_enc_password OUTPUT  
        IF @@error <> 0 OR @retcode <> 0  
            return 1  
    end  
  
    --   
    --  Parameter Check: @offloadserver  
     --   
    IF @offloadagent IS NOT NULL  
        AND RTRIM(LTRIM(LOWER(@offloadagent))) != 'false'  
    BEGIN  
        --  "Parameter '@offloadagent' is no longer supported."  
        RAISERROR(21698, 16, -1, '@offloadagent')  
        RETURN 1  
    END  
  
    IF ISNULL(@offloadserver, N'') != N''  
    BEGIN  
        --  "Parameter '@offloadserver' is no longer supported."  
        RAISERROR(21698, 16, -1, '@offloadserver')  
        RETURN 1  
    END  
  
    --   
    --  Construct unique name  
     --   
    if @subscriber is NULL select @subscriber = ''  
    if @subscriber_db is NULL select @subscriber_db = ''  
      
    declare @job_existing bit  
    if @job_name is null  
    begin  
        select @job_existing = 0  
        SELECT @job_name = CONVERT(nvarchar(18),@publisher ) + '-' +   
                        CASE @publisher_db  
                            WHEN NULL THEN ''  
                            ELSE CONVERT(nvarchar(18),@publisher_db) + '-'  
                        END +   
                        CONVERT(nvarchar(18),@publication) + '-' + CONVERT(nvarchar(18),@subscriber) + '-' +  
                        CONVERT(nvarchar(18),@subscriber_db) + '-' + CONVERT(nvarchar(36),newid())  
    end  
    else  
        select @job_existing = 1          
      
    BEGIN TRAN  
    save tran sp_addpullsubagent  
  
    --   
    --  If the publication is independent agent type or it is the first  
    --  subscription on the non independent agent publications.  
     --   
  
    IF @independent_agent_id = 1 OR   
        NOT EXISTS (SELECT * FROM MSreplication_subscriptions WHERE  
                            UPPER(@publisher) = UPPER(publisher) and  
                            @publisher_db = publisher_db and  
                            agent_id IS NOT NULL and  
                            independent_agent = 0)  
    BEGIN  
        if @job_existing = 0  
        begin  
            --  Construct agent command  --   
            SELECT @command = '-Publisher ' + @publisher + ' '  
  
            IF @publisher_db IS NOT NULL  
                SELECT @command = @command + '-PublisherDB ' + QUOTENAME(@publisher_db) + ' '  
              
            IF @independent_agent_id = 1  
                SELECT @command = @command + '-Publication ' + QUOTENAME(@publication) + ' '  
  
            SELECT @command = @command + '-Distributor ' + QUOTENAME(@distributor)  + ' '  
  
            SELECT @command = @command + '-SubscriptionType ' + convert(nvarchar(10),@subscription_type_id)  + ' '  
  
            SELECT @command = @command + '-Subscriber ' + QUOTENAME(@subscriber)  + ' '  
  
            --  Always use integrated for local security  
            select @command = @command + '-SubscriberSecurityMode 1 '  
  
            SELECT @command = @command + '-SubscriberDB ' + QUOTENAME(@subscriber_db) + ' '  
  
            if @dts_package_name is not null  
              select @command = @command + '-UseDTS '  
  
            --    
            --  make sure the command line is not truncated  
             --   
            --  Use datalength because len doesn't count the last space in @command  --   
            IF (datalength(@command) + datalength(@optional_command_line)) > 8000  
            BEGIN  
                RAISERROR(20018, 16, -1)  
                goto cleanup  
            END  
  
            SELECT @command = @command + @optional_command_line  
  
            --  Get Distribution category name (assumes category_id = 10)  
            select @category_name = name FROM msdb.dbo.syscategories where category_id = 10  
  
            EXEC @retcode = sys.sp_MSadd_repl_job  
                    @name = @job_name,  
                    @subsystem = 'Distribution',  
                    @server = @@SERVERNAME,  
                    @databasename = @subscriber_db,  
                    @enabled = 1,  
                    @freqtype = @frequency_type,  
                    @freqinterval = @frequency_interval,  
                    @freqsubtype = @frequency_subday,  
                    @freqsubinterval = @frequency_subday_interval,  
                    @freqrelativeinterval = @frequency_relative_interval,  
                    @freqrecurrencefactor = @frequency_recurrence_factor,  
                    @activestartdate = @active_start_date,  
                    @activeenddate = @active_end_date,  
                    @activestarttimeofday = @active_start_time_of_day,  
                    @activeendtimeofday = @active_end_time_of_day,  
                    @command = @command,  
                    @category_name = @category_name,  
                    @retryattempts = 10,  
                    @retrydelay = 1,  
                    @job_id = @distribution_jobid OUTPUT,  
                    @job_step_uid = @distribution_job_step_uid OUTPUT,  
                    @job_login = @job_login,  
                    @job_password = @job_password  
  
            IF @@ERROR <> 0 or @retcode <> 0  
                goto cleanup  
        end  
        else  
        begin  
            SELECT @distribution_jobid = sjv.job_id,  
                    @distribution_job_step_uid = sjs.step_uid  
            FROM msdb.dbo.sysjobs_view as sjv   
                JOIN msdb.dbo.sysjobsteps as sjs   
                    ON sjv.job_id = sjs.job_id   
            WHERE sjv.name = @job_name collate database_default   
                AND UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  
                AND sjv.master_server = 0  
                AND sjv.category_id = 10  
                AND sjs.subsystem = N'Distribution'  
                AND sjs.database_name = db_name()  
                      
            if @distribution_jobid IS NULL  
            begin  
                --  Message from msdb.dbo.sp_verify_job_identifiers  
                RAISERROR(14262, -1, -1, 'Job', @job_name)            
                goto cleanup  
            end  
        end  
    END  
  
        --  If we do not have independent agents , i.e. independent_agent=0, but there is  
    already a row for that publisher and that publisher database with a NOT null   
    distribution_agent_id, then set the @distribution_jobid to that id.  Note that if  
    there are no rows returned, the value of the variable does not change, which is what we want.  
    There should never be more than one row ever returned for this query - but will use TOP 1  
    to insist that is the case.  
     --   
      
    IF @independent_agent_id = 0  
    BEGIN  
        SELECT DISTINCT @distribution_jobid=agent_id, @job_name = distribution_agent  
          FROM MSreplication_subscriptions  
         WHERE UPPER(publisher) = UPPER(@publisher)   
           AND publisher_db =  @publisher_db  
           AND agent_id IS NOT NULL AND independent_agent=0  
    END  
      
    UPDATE MSreplication_subscriptions   
       SET distribution_agent = @job_name,  
           agent_id = @distribution_jobid  
     WHERE UPPER(publisher) = UPPER(@publisher)   
       AND publisher_db =  @publisher_db   
       AND publication =  @publication   
       AND (subscription_type = 1 --  pull --  OR subscription_type = 2) -- anonymous --   
  
    IF @@ERROR <> 0   
        goto cleanup  
  
    if (@subscription_type_id = 1) OR (@subscription_type_id = 2)  
    BEGIN  
        --  if the job step uid is null then we will   
        --  attempt to retrieve it based on job info  
        if @distribution_job_step_uid IS NULL  
        begin  
            select @distribution_job_step_uid = sjs.step_uid  
            from msdb.dbo.sysjobs_view as sjv   
                join msdb.dbo.sysjobsteps as sjs   
                    on sjv.job_id = sjs.job_id   
            where sjv.job_id = @distribution_jobid  
                and sjv.master_server = 0  
                and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  
                and sjv.category_id = 10  
                and sjs.subsystem = N'Distribution'   
                and sjs.database_name = db_name()  
        end  
          
        IF NOT EXISTS (select * from MSsubscription_properties   
            where UPPER(publisher) = UPPER(@publisher)  
              and publisher_db =  @publisher_db  
              and publication = @publication)   
        BEGIN  
            --  Publication type:  
            --  0  transactional  
            --  1  snapshot  
            --  2  merge (not allowed)  
  
            INSERT INTO MSsubscription_properties   
            (publisher, publisher_db, publication, publication_type,   
             publisher_login,publisher_password, publisher_security_mode,   
             distributor, distributor_login, distributor_password,   
             distributor_security_mode, ftp_address, ftp_port, ftp_login,   
             ftp_password, alt_snapshot_folder, working_directory, use_ftp,  
             dts_package_name, dts_package_password, dts_package_location,   
             offload_agent, offload_server, dynamic_snapshot_location, job_step_uid)  
            values (@publisher, @publisher_db, @publication, @publication_type, NULL, NULL, -1,   
                @distributor, @distributor_login, @distributor_enc_password,   
                @distributor_security_mode, null, null, null,  
                null, @alt_snapshot_folder, @working_directory, @use_ftp_bit,  
                @dts_package_name, @dts_package_enc_password,   
                @dts_package_location_id, 0, null, null, @distribution_job_step_uid)  
        END  
        ELSE  
        BEGIN  
            update MSsubscription_properties set  
                distributor = @distributor,  
                distributor_login = @distributor_login,  
                distributor_password = @distributor_enc_password,  
                distributor_security_mode = @distributor_security_mode,  
                dts_package_name = @dts_package_name,  
                dts_package_password = @dts_package_enc_password,  
                dts_package_location = @dts_package_location_id,  
                job_step_uid = @distribution_job_step_uid  
                where UPPER(publisher) = UPPER(@publisher)  
                    and publisher_db =  @publisher_db  
                    and publication = @publication  
        END  
        IF @@ERROR <> 0   
            goto cleanup  
  
  
        --  For dependent subscriptions we need to fix up all the  
        --  shared properties    
        IF @independent_agent_id = 0   
        BEGIN  
  
            EXEC @retcode = sys.sp_MSfixupsharedagentproperties  
                   @publisher = @publisher,  
                   @publisher_db = @publisher_db,  
                   @publication = @publication,  
                   @distributor = @distributor,  
                   @distributor_security_mode = @distributor_security_mode,  
                   @distributor_login = @distributor_login,  
                   @distributor_password = @distributor_enc_password,  
                   @ftp_address = @ftp_address,  
                   @ftp_port = @ftp_port,  
                   @ftp_login = @ftp_login,  
                   @ftp_password = @ftp_password,  
                   @alt_snapshot_folder = @alt_snapshot_folder,  
                   @working_directory = @working_directory,  
                   @use_ftp = @use_ftp_bit  
                     
            IF @retcode <> 0 OR @@ERROR <> 0  
                goto cleanup  
        END  
  
        IF @@ERROR <> 0   
            goto cleanup  
    END  
  
    --  Conditional support for MobileSync  --   
    if LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true'  
    BEGIN  
        --  Call sp_MSregistersubscription so that the subscription can be synchronized via MobileSync etc.  --   
        declare @subscription_id uniqueidentifier  
        declare @failover_mode_id int  
        set @subscription_id = convert(uniqueidentifier, @distribution_jobid)  
  
        if @update_mode_id in (3,5)   
            select @failover_mode_id = 1  
        else if @update_mode_id in (2,4)  
            select @failover_mode_id = 2  
        else  
            select @failover_mode_id = 0  
              
        exec @retcode = sys.sp_MSregistersubscription @replication_type = 1,  
                                    @publisher = @publisher,  
                                    @publisher_db = @publisher_db,  
                                    @publication = @publication,  
                                    @subscriber = @subscriber,  
                                    @subscriber_db = @subscriber_db,  
                                    @subscriber_security_mode = @subscriber_security_mode,  
                                    @subscriber_login = @subscriber_login,  
                                    @subscriber_password = @subscriber_password,  
                                    @distributor = @distributor,  
                                    @subscription_id = @subscription_id,  
                                    @independent_agent = @independent_agent_id,  
                                    @subscription_type = @subscription_type_id,  
                                    @failover_mode = @failover_mode_id  
        IF @@ERROR <> 0 or @retcode <> 0  
            goto cleanup  
    END  
  
    --  If we didn't need to create the job but   
    --  we were given the job_login and job_password  
    --  then we will attempt to change them or add  
    --  NOTE:  
    --  We do this at the very end because the actual  
    --  agent must be added prior to setting the login  
    --  and passwords...  
    IF @job_existing = 1  
    BEGIN  
        IF @job_login IS NOT NULL  
        BEGIN  
            EXEC @retcode = sys.sp_change_subscription_properties @publisher = @publisher,  
                                                                    @publisher_db = @publisher_db,  
                                                                    @publication = @publication,  
                                                                    @property = 'distrib_job_login',  
                                                                    @value = @job_login  
            IF @retcode <> 0 OR @@ERROR <> 0  
                GOTO cleanup  
  
            EXEC @retcode = sys.sp_change_subscription_properties @publisher = @publisher,  
                                                                    @publisher_db = @publisher_db,  
                                                                    @publication = @publication,  
                                                                    @property = 'distrib_job_password',  
                                                                    @value = @job_password  
            IF @retcode <> 0 OR @@ERROR <> 0  
                GOTO cleanup  
        END  
    END  
      
    --  success  
    COMMIT TRAN  
    RETURN(0)  
    --  error cleanup  
cleanup:  
    ROLLBACK TRAN sp_addpullsubagent  
    COMMIT TRAN             
    RETURN(1)  
END

C# and I

April 13, 2012

sp_addsubscriber_schedule (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addsubscriber (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addsrvrolemember (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addserver (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addscriptexec (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addrolemember (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addrole (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addremotelogin (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_addpullsubscription_agent (Transact-SQL MetaData) Definition

Definition:

MetaData:

Total Pageviews

Where i go...