jerry_diskrent_diskrent (teaching_ database).sql

机械工业出版社《SQL Server应用开发实例教程》源代码

-- Adding the transactional pull subscription: JERRY:diskrent:diskrent

/****** Begin: Script to be run at Subscriber: JERRY ******/
use [teaching_ Database]
GO
exec sp_addpullsubscription @publisher = N'JERRY', @publisher_db = N'diskrent', @publication = N'diskrent', @independent_agent = N'true', @subscription_type = N'pull', @description = N'Transactional publication of diskrent database from Publisher JERRY.', @update_mode = N'failover', @immediate_sync = 1
GO

/****** Object:  Job JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF    Script Date: 2001-04-24 11:08:55 ******/
begin transaction 
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
if (select count(*) from msdb.dbo.syscategories where name = N'REPL-Distribution') < 1 
  execute msdb.dbo.sp_add_category N'REPL-Distribution'

select @JobID = job_id from msdb.dbo.sysjobs where (name = N'JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF')
if (@JobID is NULL)
BEGIN
  execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF', @enabled = 1, @start_step_id = 1, @notify_level_eventlog = 2, @notify_level_email = 0, @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0, @description = N'No description available.', @category_name = N'REPL-Distribution', @owner_login_name = N'WWW\Administrator' 
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback


  execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 10, @retry_interval = 1, @os_run_priority = 0, @flags = 0, @step_name = N'Run agent.', @subsystem = N'Distribution', @command = N'-Publisher JERRY -PublisherDB [diskrent] -Publication [diskrent] -Distributor [JERRY] -SubscriptionType 1 -Subscriber [JERRY] -SubscriberSecurityMode 1 -SubscriberDB [teaching_ Database]   ', @server = N'JERRY'
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

  execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

  execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Replication agent schedule.', @enabled = 0, @freq_type = 1, @freq_interval = 0, @freq_subday_type = 0, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20010424, @active_end_date = 99991231, @active_start_time = 0, @active_end_time = 235900
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

  execute @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'jerry'
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback


END

commit transaction 
goto EndSave 
QuitWithRollback: 
  if (@@TRANCOUNT > 0) rollback transaction 
EndSave:

GO

exec sp_addpullsubscription_agent @publisher = N'JERRY', @publisher_db = N'diskrent', @publication = N'diskrent', @distributor = N'JERRY', @subscriber_security_mode = 1, @distributor_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @enabled_for_syncmgr = N'true', @alt_snapshot_folder = N'E:\袁连海\8z', @use_ftp = N'false', @publication_type = 0, @dts_package_name = N'diskrentpubs', @dts_package_location = N'subscriber', @offloadagent = N'false', @job_name = N'JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF'
exec sp_link_publication @publisher = N'JERRY', @publisher_db = N'diskrent', @publication = N'diskrent', @security_mode = 2
GO

/****** End: Script to be run at Subscriber: JERRY ******/

/****** Begin: Script to be run at Publisher: JERRY ******/
use [diskrent]
GO
-- Parameter @sync_type is scripted as 'automatic', please adjust when appropriate.
exec sp_addsubscription @publication = N'diskrent', @subscriber = N'JERRY', @destination_db = N'teaching_ Database', @sync_type = N'automatic', @subscription_type = N'pull', @update_mode = N'failover'
GO

/****** End: Script to be run at Publisher: JERRY ******/

-- Adding the transactional pull subscription: JERRY:diskrent:diskrent

/****** Begin: Script to be run at Subscriber: JERRY ******/
use [teaching_ Database]
GO
exec sp_addpullsubscription @publisher = N'JERRY', @publisher_db = N'diskrent', @publication = N'diskrent', @independent_agent = N'true', @subscription_type = N'pull', @description = N'Transactional publication of diskrent database from Publisher JERRY.', @update_mode = N'failover', @immediate_sync = 1
GO

/****** Object:  Job JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF    Script Date: 2001-04-24 11:09:51 ******/
begin transaction 
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
if (select count(*) from msdb.dbo.syscategories where name = N'REPL-Distribution') < 1 
  execute msdb.dbo.sp_add_category N'REPL-Distribution'

select @JobID = job_id from msdb.dbo.sysjobs where (name = N'JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF')
if (@JobID is NULL)
BEGIN
  execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF', @enabled = 1, @start_step_id = 1, @notify_level_eventlog = 2, @notify_level_email = 0, @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0, @description = N'No description available.', @category_name = N'REPL-Distribution', @owner_login_name = N'WWW\Administrator' 
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback


  execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 10, @retry_interval = 1, @os_run_priority = 0, @flags = 0, @step_name = N'Run agent.', @subsystem = N'Distribution', @command = N'-Publisher JERRY -PublisherDB [diskrent] -Publication [diskrent] -Distributor [JERRY] -SubscriptionType 1 -Subscriber [JERRY] -SubscriberSecurityMode 1 -SubscriberDB [teaching_ Database]   ', @server = N'JERRY'
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

  execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

  execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Replication agent schedule.', @enabled = 0, @freq_type = 1, @freq_interval = 0, @freq_subday_type = 0, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20010424, @active_end_date = 99991231, @active_start_time = 0, @active_end_time = 235900
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

  execute @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'jerry'
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback


END

commit transaction 
goto EndSave 
QuitWithRollback: 
  if (@@TRANCOUNT > 0) rollback transaction 
EndSave:

GO

exec sp_addpullsubscription_agent @publisher = N'JERRY', @publisher_db = N'diskrent', @publication = N'diskrent', @distributor = N'JERRY', @subscriber_security_mode = 1, @distributor_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @enabled_for_syncmgr = N'true', @alt_snapshot_folder = N'E:\袁连海\8z', @use_ftp = N'false', @publication_type = 0, @dts_package_name = N'diskrentpubs', @dts_package_location = N'subscriber', @offloadagent = N'false', @job_name = N'JERRY-diskrent-diskrent-JERRY-teaching_ Database-6C82DB11-350B-4A28-A13B-953E1FEF2ACF'
exec sp_link_publication @publisher = N'JERRY', @publisher_db = N'diskrent', @publication = N'diskrent', @security_mode = 2
GO

/****** End: Script to be run at Subscriber: JERRY ******/

/****** Begin: Script to be run at Publisher: JERRY ******/
use [diskrent]
GO
-- Parameter @sync_type is scripted as 'automatic', please adjust when appropriate.
exec sp_addsubscription @publication = N'diskrent', @subscriber = N'JERRY', @destination_db = N'teaching_ Database', @sync_type = N'automatic', @subscription_type = N'pull', @update_mode = N'failover'
GO

/****** End: Script to be run at Publisher: JERRY ******/