sp_adddynamicsnapshot_job (Transact-SQL)

Bir aracı oluşturur iş , parametreli satır filtreleriyle yayın için süzülmüş veri anlık görüntü görüntüsünü oluşturur.Bu saklı yordam, yayın veritabanı üzerinde yayımcı adresindeki yürütülür.Bu saklı yordam, bir yönetici tarafından el ile aboneleri için süzülmüş veri anlık görüntü işi oluşturmak için kullanılır.

Not

Sırasıyla oluşturulması süzülmüş veri anlık görüntü işi için bir standart anlık görüntü iş yayın için önceden mevcut olmalıdır.

Daha fazla bilgi için bkz: Anlık görüntüler için birleştirme yayınları ile parametreli süzgeçleri.

Transact-sql sözdizimi kuralları

Sözdizimi

sp_adddynamicsnapshot_job [ @publication = ] 'publication' 
    [ , [ @suser_sname = ] 'suser_sname' ] 
    [ , [ @host_name = ] 'host_name' ] 
    [ , [ @dynamic_snapshot_jobname = ] 'dynamic_snapshot_jobname' OUTPUT ] 
    [ , [ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid' OUTPUT ] 
    [ , [ @frequency_type= ] frequency_type ]
    [ , [ @frequency_interval= ] frequency_interval ]
    [ , [ @frequency_subday= ] frequency_subday ]
    [ , [ @frequency_subday_interval= ] frequency_subday_interval ]
    [ , [ @frequency_relative_interval= ] frequency_relative_interval ]
    [ , [ @frequency_recurrence_factor= ] frequency_recurrence_factor ]
    [ , [ @active_start_date= ] active_start_date ]
    [ , [ @active_end_date= ] active_end_date ]
    [ , [ @active_start_time_of_day= ] active_start_time_of_day ]
    [ , [ @active_end_time_of_day= ] active_end_time_of_day ]

Bağımsız değişkenler

• [ @publication=] 'publication'
  Is the name of the publication to which the filtered data snapshot job is being added.publication is sysname, with no default.

• [ @ suser_sname=] 'suser_sname'
  Is the value used when creating a filtered data snapshot for a subscription that is filtered by the value of the SUSER_SNAME function at the Subscriber.suser_sname is sysname, with no default.suser_sname should be NULL if this function is not used to dynamically filter the publication.

• [ @ host_name=] 'host_name'
  Is the value used when creating a filtered data snapshot for a subscription that is filtered by the value of the HOST_NAME function at the Subscriber.host_name is sysname, with no default.host_name should be NULL if this function is not used to dynamically filter the publication.

• [ @ dynamic_snapshot_jobname=] 'dynamic_snapshot_jobname'
  Is the name of the filtered data snapshot job created.dynamic_snapshot_jobname is sysname, with default of NULL, and is an optional OUTPUT parameter.Belirtilmişse, dynamic_snapshot_jobname at dağıtımcı benzersiz bir iş çözülmesi gerekir.Parametre belirtilmezse, bir iş adı otomatik olarak oluşturulan ve burada adı gibi oluşturulan sonuç küme döndürülen:

  'dyn_' + <name of the standard snapshot job> + <GUID>
  

  Not

  Dinamik anlık görüntü iş adını oluştururken, standart anlık görüntü işinin adını kesmek.

• [ @ dynamic_snapshot_jobid=] 'dynamic_snapshot_jobid'
  Is an identifier for the filtered data snapshot job created.dynamic_snapshot_jobid is uniqueidentifier, with default of NULL, and is an optional OUTPUT parameter.

• [ @frequency_type=] frequency_type
  Is the frequency with which to schedule the filtered data snapshot job.frequency_type is int, and can be one of these values.

  Değer	Açıklama
  1	Birisaat
  2	İsteğe bağlı
  4 (varsayılan)	Günlük
  8	Haftalık
  16	Aylık
  32	Aylık göreli
  64	Otomatik başlatma
  128	Yinelenen

• [ @frequency_interval = ] frequency_interval
  Is the period (measured in days) when the filtered data snapshot job is executed.frequency_interval is int, with a default value of 1, and depends on the value of frequency_type.

  Değer, frequency_type	Üzerinde tümden frequency_interval
  1	frequency_interval kullanılmaz.
  4 (varsayılan)	Her frequency_interval gün, günlük varsayılan değer.
  8	frequency_interval biri veya bir kaçını (birleştirilen bir | (Bit düzey veya) (Transact-SQL) mantıksal işleç): 1 Pazar = | 2 = Monday | 4 = Tuesday | 8 Çarşamba = | 16 = Perşembe | 32 = Friday | 64 Cumartesi =
  16	Üzerinde frequency_interval ayın günü.
  32	frequency_interval aşağıdakilerden biridir: 1 Pazar = | 2 = Monday | 3 = Tuesday | 4 Çarşamba = | 5 = Thursday | 6 = Friday | 7 = Saturday | 8 = Day | 9 = Weekday | 10 = Hafta sonu
  64	frequency_interval kullanılmaz.
  128	frequency_interval kullanılmaz.

• [ @frequency_subday=] frequency_subday
  Specifies the units for frequency_subday_interval.frequency_subday is int, and can be one of these values.

  Değer	Açıklama
  1	Bir kez
  2	İkinci
  4 (varsayılan)	Dakika
  8	Saat

• [ @frequency_subday_interval=] frequency_subday_interval
  Is the number of frequency_subday periods that occur between each execution of the job.frequency_subday_interval is int, with a default of 5.

• [ @frequency_relative_interval=] frequency_relative_interval
  Süzülmüş veri anlık görüntü oluşumunu ise iş her ay.This parameter is used when frequency_type is set to 32 (monthly relative).frequency_relative_interval is int, and can be one of these values.

  Değer	Açıklama
  1 (varsayılan)	First
  2	İkinci
  4	Üçüncü
  8	Dördüncü
  16	Last

• [ @frequency_recurrence_factor=] frequency_recurrence_factor
  Is the recurrence factor used by frequency_type.frequency_recurrence_factor is int, with a default of 0.

• [ @active_start_date=] active_start_date
  Is the date when the filtered data snapshot job is first scheduled, formatted as YYYYMMDD.active_start_date is int, with a default of NULL.

• [ @active_end_date=] active_end_date
  Is the date when the filtered data snapshot job stops being scheduled, formatted as YYYYMMDD.active_end_date is int, with a default of NULL.

• [ @active_start_time_of_day=] active_start_time_of_day
  Is the time of day when the filtered data snapshot job is first scheduled, formatted as HHMMSS.active_start_time_of_day is int, with a default of NULL.

• [ @active_end_time_of_day=] active_end_time_of_day
  Is the time of day when the filtered data snapshot job stops being scheduled, formatted as HHMMSS.active_end_time_of_day is int, with a default of NULL.

Sonuç kümesi

Sütun adı	Veri türü	Açıklama
Kimliği	int	Süzülmüş veri anlık görüntü görüntü işleme tanımlar MSdynamicsnapshotjobs sistem tablo.
dynamic_snapshot_jobname	sysname	Süzülmüş veri anlık görüntü iş adı.
dynamic_snapshot_jobid	uniqueidentifier	Uniquely identifies the Microsoft SQL Server Agent job at the Distributor.

Dönüş Kodu Değerleri

0 (başarılı) veya 1 (hata)

Açıklamalar

sp_adddynamicsnapshot_job birleştirmede kullanılan çoğaltma parametreleştirilmiş bir filtre kullanan yayınlar için.

Örnek

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks2008R2';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks2008R2];

-- Enable AdventureWorks2008R2 for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication.  
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks2008R2.', 
  @allow_subscriber_initiated_snapshot = N'false';

-- Create a new snapshot job for the publication, using the 
-- default schedule. Pass credentials at runtime using 
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(Login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains customer information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the snapshot agent job.
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

PRINT '*** Waiting for the initial snapshot.';
GO

-- Create a temporary table to store the filtered data snapshot 
-- job information.
CREATE TABLE #temp (id int,
    job_name sysname,
    job_id uniqueidentifier,
    dynamic_filter_login sysname NULL,
    dynamic_filter_hostname sysname NULL,
    dynamic_snapshot_location nvarchar(255),
    frequency_type int, 
    frequency_interval int, 
    frequency_subday_type int,
    frequency_subday_interval int, 
    frequency_relative_interval int, 
    frequency_recurrence_factor int, 
    active_start_date int, 
    active_end_date int, 
    active_start_time int, 
    active_end_time int
)

-- Create each snapshot for a partition 
-- The initial snapshot must already be generated.
DECLARE @publication AS sysname;
DECLARE @jobname AS sysname
DECLARE @hostname AS sysname
SET @publication = N'AdvWorksSalesPersonMerge';
SET @hostname = N'adventure-works\Fernando';

WHILE NOT EXISTS(SELECT * FROM sysmergepublications 
    WHERE [name] = @publication 
    AND snapshot_ready = 1)
BEGIN
    WAITFOR DELAY '00:00:05'
END

-- Create a data partition by overriding HOST_NAME().
EXEC sp_addmergepartition 
  @publication = @publication,
  @host_name = @hostname;

-- Create the filtered data snapshot job, and use the returned 
-- information to start the job.
EXEC sp_adddynamicsnapshot_job 
  @publication = @publication,
  @host_name = @hostname;

INSERT INTO #temp (id, job_name, job_id, dynamic_filter_login,
    dynamic_filter_hostname, dynamic_snapshot_location,
    frequency_type, frequency_interval, frequency_subday_type,
    frequency_subday_interval, frequency_relative_interval, 
    frequency_recurrence_factor, active_start_date, active_end_date, 
    active_start_time,active_end_time)
EXEC sp_helpdynamicsnapshot_job;

SELECT @jobname = (SELECT DISTINCT job_name FROM #temp WHERE dynamic_filter_hostname = @hostname);

EXEC msdb..sp_start_job @job_name = @jobname;
DROP TABLE #temp;
GO

İzinler

Yalnızca üyeleri sysadmin sabit sunucu rolü veya db_owner sabit veritabanı rolü olabilir yürütmek sp_adddynamicsnapshot_job.

Ayrıca bkz.

Görevler

Nasıl yapılır: Bir birleştirme yayını ile parametreli süzgeçleri (çoğaltma Transact-sql programlama) için bir Snapshot oluştur

Başvuru

sp_dropdynamicsnapshot_job (Transact-SQL)

sp_helpdynamicsnapshot_job (Transact-SQL)

Kavramlar

Parametreli satır filtreleri