create procedure sys.sp_MSrepl_addpublication_snapshot
(
@publication sysname,
@frequency_type int,
@frequency_interval int,
@frequency_subday int,
@frequency_subday_interval int,
@frequency_relative_interval int,
@frequency_recurrence_factor int,
@active_start_date int,
@active_end_date int,
@active_start_time_of_day int,
@active_end_time_of_day int,
@snapshot_job_name nvarchar(100),
@publisher_security_mode int,
@publisher_login sysname,
@publisher_password sysname,
@job_login nvarchar(257),
@job_password sysname,
@publisher sysname,
@publisher_type sysname
)
AS
SET NOCOUNT ON
/*
** Declarations.
*/
DECLARE @retcode int
DECLARE @newid int
DECLARE @mergepublish_bit smallint
DECLARE @transpublish_bit int
DECLARE @transpub_found bit
DECLARE @mergepub_found bit
DECLARE @newagentid int
DECLARE @dbname sysname
DECLARE @publisher_local sysname
/*
** Initializations
*/
select @mergepublish_bit = 4
select @transpublish_bit = 1
select @transpub_found = 0
select @mergepub_found = 0
select @dbname = db_name()
/*
** Parameter Check
*/
exec @retcode = sys.sp_MSreplcheck_name @publication, '@publication', 'sp_MSrepl_addpublication_snapshot'
if @@ERROR <> 0 or @retcode <> 0
return(1)
/*
** Security Check
*/
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)
-- ensure that we are published and then go to work
IF sys.fn_MSrepl_ispublished(@dbname) != 1
BEGIN
-- The database is not published.
RAISERROR (18757, 16, -1)
RETURN 1
END
-- SQLSERVER
IF @publisher_type = N'MSSQLSERVER'
BEGIN
-- if @pub_sec_mode is NULL default it to Windows Auth.
SELECT @publisher_security_mode = ISNULL(@publisher_security_mode, 1)
-- check security mode
IF @publisher_security_mode NOT IN (0, 1)
BEGIN
-- The specified '@publisher_security_mode' is invalid (valid values are: 0, 1).
RAISERROR(14266, 16, -1, '@publisher_security_mode', '0, 1')
RETURN 1
END
-- Check to ensure a login is provided if security mode is SQL Server authentication.
SELECT @publisher_login = rtrim(ltrim(isnull(@publisher_login, '')))
IF @publisher_security_mode = 0 AND @publisher_login = ''
BEGIN
-- '@publisher_login cannot be null or empty when @publisher_security_mode is set to 0 (SQL Server authentication).'
RAISERROR(21694, 16, -1, '@publisher_login', '@publisher_security_mode')
RETURN 1
END
END
-- HETERO
ELSE
BEGIN
IF @publisher_security_mode IS NOT NULL
OR @publisher_login IS NOT NULL
OR @publisher_password IS NOT NULL
BEGIN
-- For heterogeneous publications, the @publisher_security_mode, @publisher_login and @publisher_password parameter(s) should be specified when calling 'sp_adddistpublisher'.
RAISERROR(22535, 16, -1, '@publisher_security_mode, @publisher_login and @publisher_password', 'sp_adddistpublisher')
RETURN 1
END
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 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_addpublication_snapshot'.
RAISERROR(21797, 16, -1, '@job_login', 'sp_addpublication_snapshot')
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 : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addpublication_snapshot'.
RAISERROR(21797, 16, -1, '@job_login', 'sp_addpublication_snapshot')
RETURN 1
END
END
BEGIN TRANSACTION tr_addsnapshot_agent
SAVE TRANSACTION tr_addsnapshot_agent
/*
** Check if the publication is valid.
** 1. Check transaction-level publications
** 2. Check merge publications
*/
if ((select category & @transpublish_bit from master.dbo.sysdatabases where name = DB_NAME() collate database_default) <> 0) or
(not @publisher_type = N'MSSQLSERVER')
begin
EXEC @retcode = sys.sp_MSaddpub_snapshot
@publication = @publication,
@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,
@newagentid = @newagentid OUTPUT,
@snapshot_job_name = @snapshot_job_name,
@publisher_security_mode = @publisher_security_mode,
@publisher_login = @publisher_login,
@publisher_password = @publisher_password,
@job_login = @job_login,
@job_password = @job_password,
@publisher = @publisher
IF @retcode <> 0 OR @@ERROR <> 0
GOTO UNDO
if @newagentid <> 0
begin
select @transpub_found = 1
goto DONE
end
end
if (select category & @mergepublish_bit from master.dbo.sysdatabases where name = DB_NAME() collate database_default) <> 0
begin
EXEC @retcode = sys.sp_MSaddmergepub_snapshot
@publication = @publication ,
@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,
@newtaskid = @newagentid OUTPUT,
@snapshot_job_name = @snapshot_job_name,
@publisher_security_mode = @publisher_security_mode,
@publisher_login = @publisher_login,
@publisher_password = @publisher_password,
@job_login = @job_login,
@job_password = @job_password
IF @retcode <> 0 OR @@ERROR <> 0
GOTO UNDO
if @newagentid <> 0
begin
select @mergepub_found = 1
goto DONE
end
end
DONE:
-- 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 @snapshot_job_name IS NOT NULL
BEGIN
IF @job_login IS NOT NULL
BEGIN
IF @publisher_type = 'MSSQLSERVER'
BEGIN
SELECT @publisher_local = NULL
END
ELSE
BEGIN
SELECT @publisher_local = @publisher
END
EXEC @retcode = sys.sp_changepublication_snapshot @publication = @publication,
@job_login = @job_login,
@job_password = @job_password,
@publisher = @publisher_local
IF @retcode <> 0 OR @@ERROR <> 0
GOTO UNDO
END
END
COMMIT TRANSACTION tr_addsnapshot_agent
if @transpub_found = 0 and @mergepub_found = 0
begin
RAISERROR (15001, 11, -1, @publication)
RETURN (1)
end
return (0)
UNDO:
ROLLBACK TRANSACTION tr_addsnapshot_agent
COMMIT TRANSACTION
RETURN 1
