-- Name: sp_MSupdatesharedagentproperties

-- Descriptions:

-- Parameters: as defined in create statement

-- Returns: 0 - success
--          1 - Otherwise

-- Security:
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSupdatesharedagentproperties (
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,
    @property    sysname,
    @strvalue    nvarchar(256) = NULL,
    @intvalue    int = NULL,
    @subscription_type int
    )
AS
    DECLARE @retcode     int,
            @password     nvarchar(524)

    -- Note publisher/publisher_db/publication uniquely identifies
    -- a subscription in MSreplication_subscriptions
    CREATE TABLE #subscriptions
    (
        publication sysname collate database_default
    )

    -- Compute the list of affected subscriptions
    INSERT INTO #subscriptions SELECT publication FROM
    MSreplication_subscriptions WHERE UPPER(publisher) = UPPER(@publisher)
    AND publisher_db = @publisher_db AND publication <> @publication
    AND independent_agent = 0 AND subscription_type = @subscription_type

    IF (SELECT COUNT(*) FROM #subscriptions) > 0
    BEGIN

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor'
        BEGIN
            UPDATE MSsubscription_properties SET distributor = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_security_mode'
        BEGIN
            -- Security Mode 1
            IF @intvalue = 1
            BEGIN
                SELECT @password = newid()

                -- Encrypt the password before storing
                EXEC @retcode = sys.sp_MSreplencrypt @password OUTPUT
                IF @@ERROR <> 0 OR  @retcode <> 0
                    RETURN 1

                UPDATE MSsubscription_properties
                    SET distributor_security_mode = 1,
                        distributor_login = N'',
                        distributor_password = @password
                    WHERE UPPER(publisher) = UPPER(@publisher)
                        AND publisher_db = @publisher_db
                        AND publication in (SELECT publication FROM #subscriptions)
            END
            -- Security Mode 0
            ELSE
            BEGIN
                UPDATE MSsubscription_properties
                    SET distributor_security_mode = 0
                 WHERE UPPER(publisher) = UPPER(@publisher)
                   AND publisher_db = @publisher_db
                   AND publication in (SELECT publication FROM #subscriptions)
            END


        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_login'
        BEGIN
            UPDATE MSsubscription_properties SET distributor_login = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_password'
        BEGIN
            UPDATE MSsubscription_properties SET distributor_password = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_password'
        BEGIN
            UPDATE MSsubscription_properties SET distributor_password = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_address'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_address = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_port'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_port = @intvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_login'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_login = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_password'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_password = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'alt_snapshot_folder'
        BEGIN
            UPDATE MSsubscription_properties SET alt_snapshot_folder = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'working_directory'
        BEGIN
            UPDATE MSsubscription_properties SET working_directory = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'use_ftp'
        BEGIN
            UPDATE MSsubscription_properties SET use_ftp = @intvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
    END

    DROP TABLE #subscriptions

    IF @@ERROR <> 0
        RETURN 1
    ELSE
        RETURN 0