sp_addmergepartition (Transact-SQL)sp_addmergepartition (Transact-SQL)

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database (Managed Instance uniquement) nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Crée une partition filtrée dynamiquement pour un abonnement qui est filtrée par les valeurs HOST_NAME ou SUSER_SNAME sur l’abonné.Creates a dynamically filtered partition for a subscription that is filtered by the values of HOST_NAME or SUSER_SNAME at the Subscriber. Cette procédure stockée, exécutée sur le serveur de publication dans la base de données en cours de publication, est utilisée pour générer manuellement des partitions.This stored procedure is executed at the Publisher on the database that is being published, and is used to manually generate partitions.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

  
sp_addmergepartition [ @publication = ] 'publication'  
        , [ @suser_sname = ] 'suser_sname'  
        , [ @host_name = ] 'host_name'  

ArgumentsArguments

[ @publication = ] 'publication'Publication de fusion sur laquelle la partition est créée.[ @publication = ] 'publication' Is the merge publication on which the partition is being created. publication est de type sysname, sans valeur par défaut.publication is sysname, with no default. Si SUSER_SNAME est spécifié, la valeur du nom d’hôte doit être null.If suser_sname is specified, the value of hostname must be NULL.

[ @suser_sname = ] 'suser_sname'Valeur utilisée lors de la création de la partition pour un abonnement filtré par la valeur de la fonction SUSER_SNAME sur l’abonné.[ @suser_sname = ] 'suser_sname' Is the value used when creating the partition for a subscription that is filtered by the value of the SUSER_SNAME function at the Subscriber. SUSER_SNAME est de type sysname, sans valeur par défaut.suser_sname is sysname, with no default.

[ @host_name = ] 'host_name'Valeur utilisée lors de la création de la partition pour un abonnement filtré par la valeur de la fonction HOST_NAME sur l’abonné.[ @host_name = ] 'host_name' Is the value used when creating the partition for a subscription that is filtered by the value of the HOST_NAME function at the Subscriber. HOST_NAME est de type sysname, sans valeur par défaut.host_name is sysname, with no default.

Valeurs des codes de retourReturn Code Values

0 (succès) ou 1 (échec)0 (success) or 1 (failure)

NotesRemarks

sp_addmergepartition est utilisé dans la réplication de fusion.sp_addmergepartition is used in merge replication.

ExempleExample

-- 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'AdventureWorks2012';
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 [AdventureWorks2012];

-- Enable AdventureWorks2012 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 AdventureWorks2012.', 
  @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

AutorisationsPermissions

Seuls les membres du rôle serveur fixe sysadmin ou du rôle de base de données fixe db_owner peuvent exécuter sp_addmergepartition.Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addmergepartition.

Voir aussiSee Also

Créer un instantané pour une publication de fusion avec des filtres paramétrés Create a Snapshot for a Merge Publication with Parameterized Filters
Filtres de lignes paramétrésParameterized Row Filters