- Microsoft SQL Server
- System tables
- Procedure
- Views
- Functions
|
sys.sp_MSupdatesharedagentproperties
Syntax
|
 |
 |
 |
|
-- 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
|
|
|
|
|
|
|
|
Last revision 2008RTM |
|
|
|
|
|
See also
News
|