CREATE PROCEDURE sys.sp_addqreader_agent
(
@job_login nvarchar(257) = NULL,
@job_password sysname = NULL,
@job_name sysname = NULL,
@frompublisher bit = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @retcode int,
@qreader_exists bit
if (@frompublisher = 0)
begin
-- Security Check: require sysadmin/dbo of dist
IF IS_SRVROLEMEMBER('sysadmin') != 1
BEGIN
RAISERROR(21089,16,-1)
RETURN 1
END
-- database must be distribution db
IF sys.fn_MSrepl_isdistdb(DB_NAME()) <> 1
BEGIN
RAISERROR (21482, 16, -1, 'sp_addqreader_agent', 'distribution')
RETURN (1)
END
end
else
begin
-- Security Check: require sysadmin of publisher
IF IS_SRVROLEMEMBER('sysadmin') != 1
BEGIN
RAISERROR(21089,16,-1)
RETURN 1
END
-- database must be publishing db
IF sys.fn_MSrepl_istranpublished(DB_NAME(), 0) <> 1
BEGIN
RAISERROR (14013, 16, -1)
RETURN (1)
END
end
--
-- if executed from publisher
--
if (@frompublisher = 1)
begin
declare @loc_publisher sysname
,@loc_distribdb sysname
,@rpcsrvname sysname
,@rpc nvarchar(1000)
--
-- get the distributor rpc info
--
select @loc_publisher = CONVERT(sysname, SERVERPROPERTY('ServerName'))
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @loc_publisher,
@rpcsrvname = @rpcsrvname OUTPUT,
@distribdb = @loc_distribdb OUTPUT
IF @@error <> 0 OR @retcode <> 0 or (@rpcsrvname IS NULL) or (@loc_distribdb IS NULL)
BEGIN
RAISERROR (14080, 16, -1, @loc_publisher)
RETURN (1)
END
--
-- execute the RPC
--
select @rpc = quotename(@rpcsrvname) + N'.' + quotename(@loc_distribdb) + N'.dbo.sp_addqreader_agent'
exec @retcode = @rpc @job_login
,@job_password
,@job_name
,0
if (@@error != 0)
select @retcode = 1
--
-- return
--
return @retcode
end
--
-- If we are here - this SP is being executed on distributor
--
-- 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 sys.fn_replisvalidwindowsloginformat(@job_login) != 1
BEGIN
-- '@job_login' must be a valid Windows Login in the form : 'MACHINELogin' or 'DOMAINLogin'. Please see the documentation for 'sp_addqreader_agent'.
RAISERROR(21797, 16, -1, '@job_login', 'sp_addqreader_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 sys.fn_replisvalidwindowsloginformat(@job_login) != 1
BEGIN
-- '@job_login' must be a valid Windows Login in the form : 'MACHINELogin' or 'DOMAINLogin'. Please see the documentation for 'sp_addqreader_agent'.
RAISERROR(21797, 16, -1, '@job_login', 'sp_addqreader_agent')
RETURN 1
END
END
IF @job_name IS NULL
BEGIN
EXECUTE @retcode = sys.sp_MSreplagentjobexists @exists = @qreader_exists output,
@type = 4
IF @@ERROR <> 0 or @retcode <> 0
RETURN 1
IF @qreader_exists = 1
BEGIN
-- "The %s already exists. Use '%' to update any settings/properties."
RAISERROR(21831, 16, -1, 'qreader agent', 'sp_changeqreader_agent')
RETURN 1
END
END
BEGIN TRANSACTION tr_addqreader_agent
SAVE TRANSACTION tr_addqreader_agent
EXECUTE @retcode = sys.sp_MSadd_qreader_agent @job_login = @job_login,
@job_password = @job_password,
@name = @job_name,
@internal = N'YUKON'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
-- 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_name IS NOT NULL
BEGIN
IF @job_login IS NOT NULL
BEGIN
EXECUTE @retcode = sys.sp_changeqreader_agent @job_login = @job_login,
@job_password = @job_password,
@frompublisher = @frompublisher
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
END
COMMIT TRANSACTION tr_addqreader_agent
RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_addqreader_agent
COMMIT TRANSACTION
RETURN 1
END
|
